0 does not = 0 ; Classic Rounding, Formula or Formatting Mistake

I have a a column of data that starts with an inputted value with number format, being 100 at the start. After that, for each row in the column I want the value of each descending cell to decrease by the same increment, being a calculated number, with the pattern repeating once the value of 0 is reached, (i.e. start over at 100). The decreasing increment is derived by a formula with number format.

In the attached sample, the repeating decreasing increment is 4.

Upon performing formula evaluation, 0 ends up having a value of -2.66453525910038E-15. But all formula evaluations on all other formulas give correct values and as best as I can tell are formatted correctly.

I have tried:

-ROUND
-ROUNDUP
-ROUNDDOWN
-Changing Set Precision to Display to OFF and also ON with new data in new workbook

I am making a very fundamental mistake.

In summary, what is the mistake such that 0 does not equal 0, and how may it be fixed?

Regards,

James

Originally Posted by JAMES456

I have a a column of data that starts with an inputted value with number format, being 100 at the start. After that, for each row in the column I want the value of each descending cell to decrease by the same increment, being a calculated number, with the pattern repeating once the value of 0 is reached, (i.e. start over at 100).
[....]
In the attached sample, the repeating decreasing increment is 4. Upon performing formula evaluation, 0 ends up having a value of -2.66453525910038E-15. [....] In summary, what is the mistake such that 0 does not equal 0, and how may it be fixed?

At a minimum, enter the following formula into A14 and copy down:

=IF($A13=0,100,ROUND($A13-$A$9,2))
or
=ROUND(IF($A13=0,100,$A13-$A$9),2)

Use the second formula if you intend to replace 100 with a cell reference (e.g. $A$1).

You might also want to make the following change, depending on your intent.

A9: =ROUND($A$7/$A$8,2)

Without rounding A9, what appears to be 4.00 is actually about 4 + 8.88E-16. We cannot see that even when formatted as Number with 14 decimal places, because Excel formats only up to the first 15 significant digits. But note that =SUM(A9,-4) formatted as Scientific displays about 8.88E-16.

or just,,,
A9=ROUNDUP(($A$7/$A$8),0)

If A9 is =ROUND($A$7/$A$8,0) works too...

Originally Posted by joeu2004

At a minimum, enter the following formula into A14 and copy down:
=IF($A13=0,100,ROUND($A13-$A$9,2))
or
=ROUND(IF($A13=0,100,$A13-$A$9),2)
[....]
You might also want to make the following change, depending on your intent.
A9: =ROUND($A$7/$A$8,2)

James, even though simply rounding explicitly A9 would seem to remedy the problem in your example, that is only a coincidence because rounded A9 is an integer.

There is no indication that you intend for A9 to always be an integer. On the contrary, your comment says "number, 2 decimals". And in general, we can easily expect =A7/A8 to result in a non-integer, in contrast to your example.

So I would advise that you also explicitly round in A14 et al as I demonstrated, since the simple arithmetic with non-integers can result in infinitesimal differences from mathematical expectations. For example, =IF(10.01 - 10 = 0.01, TRUE) results in FALSE(!). Such anomalies arise because of limitations of the internal representation of numbers (64-bit binary floating-point).

FYI, the correction is: =IF(ROUND(10.01 - 10, 2) = 0.01, TRUE)

I suggested explicitly rounding A9 only as an after-thought. It is probably the right thing to do. But it might not be(!), depending on your intended use of A9 throughout the Excel file.

In fact, you might also consider explicitly rounding A4, A7 and A8 according to your comments in column C.

If you do explicitly round A4, you would use =ROUND($A$2/$A$3,6), not 4, since 0.3333% is the decimal number 0.003333, for example.

But I thought that suggestion is premature, without further guidance from you. Usually, I do not round percentages and other decimal fractions that are used as multiplicative factors or divisors in deriving other amounts. Instead, I prefer to use the "exact" decimal fraction (within the limitations of their internal representation).

However, I might consider explicitly rounding such factors if they are visible to the end-user (e.g. in an invoice), and you want the Excel calculations to match verification by hand.

In other words, what to round and to what degree are very subjective choices that only you can make, based on your intentions.

Thank you very much for these excellent replies.

Both A9: =ROUND($A$7/$A$8,2) and A9 is =ROUND($A$7/$A$8,0) work for the data set I provided.

With A9 and the other cells at the top being user inputs with a myriad of permutations, I'll also apply and test the integer solution. Thank you for that follow up post.

I'll work away and hopefully soon mark this as Solved with 5 stars, finger crossed. Many thanks in the interim.

The follow-up issue about a cell being an potential integer has been taken into account. A9 is now ROUND(number,2) and A4 is =ROUND($A$2/$A$3,6). All other data inputs and calculated cells have also been changed to ROUND(number,2). The spreadsheets works as desired now. Many thanks for your input - much appreciated.

Thank you - James