round up/down anomaly?

I thought I might help someone with a problem they were having:
"Modified Rounding
I need a formula that will round a value based on whether it is less than or equal to .25. So, for instance, 3.24 would round down to 3 and 3.25 (or greater) would round up to 4."
So I wrote the formula =IF(MOD(B2,1)>0.24,ROUNDUP(B2,0),ROUNDDOWN(B2,0)) and tested it and it worked fine until it had to deal with numbers between 32.24 and 44.24 where it rounds up instead of down! I was wondering if anyone could tell me if I've missed something (likely) or is it a problem with Excel (less likely)!
Thanks very much.
Paul.

Originally Posted by thesurfer

I wrote the formula =IF(MOD(B2,1)>0.24,ROUNDUP(B2,0),ROUNDDOWN(B2,0)) and tested it and it worked fine until it had to deal with numbers between 32.24 and 44.24 where it rounds up instead of down! I was wondering if anyone could tell me if I've missed something (likely) or is it a problem with Excel (less likely)!

Likely solution:

=IF(ROUND(MOD(B2,1),2)>0.24,ROUNDUP(B2,0),ROUNDDOWN(B2,0))

The problem is probably due to anomalies of 64-bit binary floating-point arithmetic, the native computer representation and arithmetic that Excel uses. Explicitly rounding usually works around such anomalies, which are common.

Thanks very much for your swift replies - both solutions work...all I have to do now is get my head around the reasons!

Originally Posted by thesurfer

I thought I might help someone with a problem they were having: "Modified Rounding
I need a formula that will round a value based on whether it is less than or equal to .25. So, for instance, 3.24 would round down to 3 and 3.25 (or greater) would round up to 4."

Another issue that you might want to ask the person you are helping: what does 4.25 round to?

If 3.25 rounds to 4, you might think that 4.25 rounds to 5. But it is not uncommon to "round to even", aka "banker's rounding". For normal rounding, that applies to exactly 3.5 and 4.5, for example; both round to 4. But there is no reason why the same principle cannot apply when the rounding midpoint is x.25 instead of x.5.

Originally Posted by thesurfer

both solutions work

Not if the value to be rounded might be calculated, instead of entered manually or input from a text file. See my example in response #5.

Originally Posted by thesurfer

all I have to do now is get my head around the reasons!

For overwhelming and sometimes misleading and incorrect details, see KB 78113 (click here) [1].

[1] http://support.microsoft.com/kb/78113

Basically, in standard binary floating-point, numbers are approximated by the sum of 53 consecutive powers of 2 (called bits), some of which might be zero. Consequently, most non-integers cannot be represented exactly.

That is also true of most integers greater than 9,007,199,254,740,992, which is 2^53.

To demonstrate: Using pencil and paper, try to represent 1/10 exactly using only 1/16 + 1/32 + 0/64 + 0/128 + 1/256 etc -- that is, the sum of fractional powers of 2. It cannot be done!

For your examples:

Please Login or Register  to view this content.

I use comma to demarcate the first 15 significant digits, which is all that Excel formats.

Note that the approximation of x.24 is different for some examples. This is because some of the 53 bits must be used to represent the integer part. So there are fewer bits to represent the decimal fraction, which might result in the sum of fewer powers of 2.

By coincidence, the approximation of x.24 is the same in 0.24 and 1.24. But it is greater than 0.24 in 3.24 and 32.24.

That is why MOD(B2,1)>0.24 returns TRUE when B2 is 32.24.

So why doesn't MOD(B2,1)>0.24 also return TRUE when B2 is 3.24?

Well, it is! If we write MOD(B2,1)-0.24>0, that returns TRUE even when B2 is 3.24.

MOD(B2,1)>0.24 returns FALSE when B2 is 3.24 because Excel tries to be clever sometimes. Sometimes, when two operands are "close enough", Excel treats them as equal. But the operative word is "sometimes". As demonstrated, the heuristic is applied inconsistently.

Thanks for your very detailed reply, this has inspired me to learn more about basic maths and indeed Excel. This is the first time I have fallen foul of the floating point anomaly and it's certainly given me much to think about when interpreting displayed results. Maybe I should have changed the spreadsheet's setting to 'set precision as displayed'. Out of curiosity, when calculating your example:
0.24 0.239999999999999,9911182158029987476766109466552734375
1.24 1.23999999999999,99911182158029987476766109466552734375
3.24 3.24000000000000,02131628207280300557613372802734375
32.24 32.2400000000000,0198951966012828052043914794921875
how did you manage to calculate such a long string of numbers after the decimal point? I'm assuming Excel won't do this.

Originally Posted by thesurfer

Maybe I should have changed the spreadsheet's setting to 'set precision as displayed'.

Although I agree that seems to be the simplest way to ensure everything is rounded "properly", I usually advise against it for a lot of reasons.

If you choose to experiment with setting PAD, be sure to make a copy of your Excel file first.

The biggest danger with PAD is that it might change constants permanently.

For example, it is not uncommon to enter interest rates with 4 or more percentage decimal places (e.g. 4.1234%), but to display them with less precision (e.g. formatted as Percentage with 2 decimal places). If we now set PAD, 4.1234% will be changed to 4.12%, and that will change all of the dependent calculations, presumably unintentionally. If we recognize our mistake and either unselect PAD or change the format to Percentage with 4 decimal places, we're too late: the value will still be 4.12%.

Another big danger with PAD is that it is non-selective.

At first, you might think that is exactly why you want to use it. But if you have many worksheets, it will take you some time to review every calculation to be sure setting PAD did not have unintended and undesirable consequences.

For formulas, we might correct those consequences by changing the cell formats.

Finally, another issue with PAD is: it applies only to the final value of a cell.

Consequenty, setting PAD does not fix surprises like IF(10.1 - 10 = 0.1, TRUE) returns FALSE(!). (For the same reason that MOD(...,1)>=0.25 does not always work as intended.) So you still must review every formula to see where you might need to use ROUND explicitly, notwithstanding setting PAD.

I believe I have documented other issues with PAD in the past. But those are the first ones that come to mind.

Originally Posted by thesurfer

Out of curiosity, when calculating your example:
0.24 0.239999999999999,9911182158029987476766109466552734375
[....] how did you manage to calculate such a long string of numbers after the decimal point? I'm assuming Excel won't do this.

I process the binary representation and sum the powers of 2 exactly. And you assume correctly: Excel (and VBA) will not do that.

Thanks again....I've learnt a lot today. I'll have a look at some of your earlier posts when I get time.