0

I have a spreadsheet that displays ledgers over a period of time. As values carry forward each month, the debit & credit columns either reduce or increase the balance value. This seems pretty straightforward.

After applying "Accounting" formatting to the cell it displayed "0.00" rather than the "-" you'd typically see with this format. So I increased the decimal places to debug and was surprised to see that the balance had decimal points yet the debit / credit and balance carried forward values were rounded to two decimal places.

The "seed" values were all rounded to two decimal places so, I guess the question is, does the rounding not apply to future calculations on the rounded value? Additionally, is there any rational explanation for why the value of F35 is 0.000000000000909494701772928000 and not 0.00? I expect this with code when using float and decimal types but am not sure if this is the case here or not.

Error formatted as "Accounting", without currency symbol and G35 having a 0 value for control enter image description here

Same error, formatted as "Number" with maximum decimal points (30) enter image description here

Same error, formatted as "Scientific" enter image description here

Improve this question
9
  • Not sure where you've quoted that from but to confirm what you're saying, using the "Round" function doesn't actually round the value, instead it formats the value? Is that correct @SaaranshGarg?
    – Daniel
    Mar 2, 2022 at 10:42
  • 1
    @Mobus, But I think he's saying he used the function here
    – Saaransh Garg
    Mar 2, 2022 at 10:48
  • 1
    This is a floating point error. They do exist in Excel. See docs.microsoft.com/en-us/office/troubleshoot/excel/… for more information
    – DMM
    Mar 2, 2022 at 20:04
  • 1
    According to the article Excel 97 and newer includes an optimisation that corrects for this near zero type error. While I know of these limitations I have never come across them in any finance sheet. You normally have to do a lot of repetitive math for the error to grow to significance. Would be intersting to trace back your numbers Daniel and see where and why this error is creeping in.
    – ExcelEverything
    Mar 3, 2022 at 6:13
  • 1
    @Mobus it seems like this is exactly what's happening: there are a few values that are initially calculated as interest on a principal debt, all individually rounded. They are summed and VAT / Sales Tax is applied. This value is rounded, too, and used in ledgers that span a number of months and, the error only presents at the final month in the term. It seems to coincide with the scenario you're describing.
    – Daniel
    Mar 3, 2022 at 10:10

1 Answer 1

Reset to default
2

Please note the difference between using cell number formatting (which only influences how a number is displayed) vs. using the ROUND function inside a cell formula (which actually changes or truncates if your will the cell value).

enter image description here

TL;DR When using percentages/multiplication/division in finance sheets, always be mindful of the decimals and where the actual rounding should take place, as

=ROUND(A1*C1,2) + ROUND(B1*C1,2)

isn't always the same as

=ROUND((A1+B1)*C1,2)
Improve this answer
1
  • Thanks, I will bear this in mind going forward. I've pasted a screenshot of the same table with the values formatted as "Scientific" for your interest. Sadly, making the change above doesn't resolve the issue nor can I reproduce it on a single sheet to share. I'm inclined to believe it's a floating point error, as mentioned by DMM in a comment on my question.
    – Daniel
    Mar 3, 2022 at 9:59

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .