Excel rounding issue.

[cmerrick]

I have a formula -

=ROUNDDOWN((B26/'FA Afford'!G32*12),-2)

which equates to

(£227.14 / 4.85%) * 12

On a standard calculator this gives me roughly '56,199.58762886598' but excel seems to want to round this up automatically to £56,200 in spite of my rounding down.

How can I get this to display as ''56,199.58762886598' and then round down to £56,100.00 ?

 

[Fluff]

The fact that other values calculate "correctly", may be more down to luck, after-all you are talking a difference of only 0.42
Why not use the formula Joe suggested in post#8

 

[Joe4]

the 'FA Afford'!G32' (4.85%) has no further decimals

Note that the number of decimals you elect to show in formatting is NOT the number that Excel will use. If there are more digits, that are not being displayed using the cell format, Excel will use them ALL (formatting just changes what is displayed, but will not change od truncate the actual value in the cell).

So, if I use the number you have for B26, and use something like 4.8451% (which rounds to 4.85% which using the format of 4 decimals), I get the 56,200 value you are seeing.
Try the formula I proposed in post 8, and it should take care of it and do what you want.

 

[cmerrick]

Note that the number of decimals you elect to show in formatting is NOT the number that Excel will use. If there are more digits, that are not being displayed using the cell format, Excel will use them ALL (formatting just changes what is displayed, but will not change od truncate the actual value in the cell).

So, if I use the number you have for B26, and use something like 4.8451% (which rounds to 4.85% which using the format of 4 decimals), I get the 56,200 value you are seeing.
Try the formula I proposed in post 8, and it should take care of it and do what you want.

I know what you're saying regarding the 'exact values' but I've traced it back to the original source and the 4.85% comes from a 'keyed in' value.

I've tried your formula and it does work. I guess I'll just have to go back over a number of our old scenario and ensure the values still work out as they have done previously

 

[Joe4]

I've tried your formula and it does work. I guess I'll just have to go back over a number of our old scenario and ensure the values still work out as they have done previously

You can just change your formulas to use this versions, and that should ensure that they all work like you want.

I know what you're saying regarding the 'exact values' but I've traced it back to the original source and the 4.85% comes from a 'keyed in' value.

Out of curiousity, what do you see if the go to cell 'FA Afford'!G32 and change the cell format to show 8 decimal places?

 

[cmerrick]

You can just change your formulas to use this versions, and that should ensure that they all work like you want.


Out of curiousity, what do you see if the go to cell 'FA Afford'!G32 and change the cell format to show 8 decimal places?

G32 = 4.85000000% etc

 

[cmerrick]

You can just change your formulas to use this versions, and that should ensure that they all work like you want.


Out of curiousity, what do you see if the go to cell 'FA Afford'!G32 and change the cell format to show 8 decimal places?

so I have two versions of the same calculator now, one with your suggested formula and one without.

The base value of 227.1417262 (which is B26) is still being fed into both.

The one with the new formula '=ROUNDDOWN((ROUND(B26,2)/'FA Afford'!G32*12),-2)' has the correct amount of £56,100.00 and the other one '=ROUNDDOWN((B26/'FA Afford'!G32*12),-2)' still shows £56,200.00

Am I right in saying then that as far excel is concerned, the new formula is just doing 227.14/4.85*12 whereas the old one is actually doing 227.1417262/4.85%*12 and this is the issue?

I'ts just for my own understanding

 

[Joe4]

Am I right in saying then that as far excel is concerned, the new formula is just doing 227.14/4.85*12 whereas the old one is actually doing 227.1417262/4.85%*12 and this is the issue?

Yes, that is exactly what the new formula is doing.

When doing calculations, Excel uses the full value of the cells, not the formatted (visible) values.
By using the ROUND function and setting it to use exactly the same number of decimals as your cell formatting is showing, it will ensure that it uses just the numbers you see.

 

[cmerrick]

Yes, that is exactly what the new formula is doing.

When doing calculations, Excel uses the full value of the cells, not the formatted (visible) values.
By using the ROUND function and setting it to use exactly the same number of decimals as your cell formatting is showing, it will ensure that it uses just the numbers you see.

Thanks a lot for your (and everyone else's') input.

Hopefully this hasn't changed my other results too much !

 

[Joe4]

You are welcome!
Glad we could help.