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 ?

 

[Joe4]

If I use the ROUNDDOWN formula, I get 56,100, which appears to be what you want.
Where do the values in B26 and 'FA Afford'!G32 come from?
Are they formulas too? If so, you may need to round those values too.

 

[gaz_chops]

Using those values, it appears to work ok

 

[Fluff]

What do you get if you don't round down & just use
= B26/'FA Afford'!G32*12

 

[cmerrick]

If I use the ROUNDDOWN formula, I get 56,100, which appears to be what you want.
Where do the values in B26 and 'FA Afford'!G32 come from?
Are they formulas too? If so, you may need to round those values too.

They are coming from other formulas that DON'T have rounding which may be the issue but.... I'm dubious to change the other formulas as so far this is the only instance of this not working correctly.

It's a fairly complex set of formulas and I'm not sure of the implications throughout the document considering it's worked for everything else

 

[cmerrick]

What do you get if you don't round down & just use
= B26/'FA Afford'!G32*12

=(B26/'FA Afford'!G32*12) = £56,200.01

 

[Fluff]

That means your values are not 227.14 and 4.85%, as already suggested.

 

[Joe4]

I'm dubious to change the other formulas as so far this is the only instance of this not working correctly.

You don't have to change the other formulas, just this one to round each value you are using, i.e.
=ROUNDDOWN((ROUND(B26,2)/ROUND('FA Afford'!G32,4)*12),-2)

 

[Fluff]

If you had 227.14166 or 4.84995 you would get a value of ~56200

 

[cmerrick]

my exact values are as follows;

B26 = 227.1417262

Which is (B24 - B25)

B24 = 892.8571429
B25 = 665.7154167

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

If I round down B26 then I get the correct value.

My issue with doing this is I've tested this on way over 1000 different scenarios (it's a very advanced loan calculator) and this is the only time I've had a problem with the value displayed. Every other scenario has worked out bang on