Round to the nearest 10,000

[eoinymc]

Hi,

can anyone help me round down to the nearest 10,000

So, if it's 84,000, it'll round to 80,000
If It's 86,000, it'll round to 80,000
etc...

I found this online, but it doesn't work properly past 100,000..it just rounds down to the nearest 100,000 when it gets past 100,000

=ROUNDDOWN(AM41,-INT(LOG(AM41)))

Any ideas how I can modify this to work?

Cheers,

Eoin

 

[shg]

Wow, we have a disconnect someplace ...

[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr]
[tr][td]

1​

[/td][td]

(18,000)​

[/td][td]

#NUM!​

[/td][td]B1: =FLOOR($A$1, 10000)[/td][/tr]

[tr][td]

2​

[/td][td][/td][td]

(10,000)​

[/td][td]B2: =FLOOR($A$1, SIGN($A$1)*10000)[/td][/tr]
[/table]

 

[pgc01]

Hi Eoin

Not clear to me the negative value case.

-18000

Do you still want to round down (-20000) or up (-10000) ?

 

[Jonmo1]

Wow, we have a disconnect someplace ...

Hmm...

I'm using XL2010 on Win7 Enterprise.
A1 is formatted as General
Using English United States regional settings.

 

[shg]

Wow, that is REMARKABLE, Jonmo. I duplicate your results with Excel 2010, and get the results I posted earlier in both Excel 2003 and Excel 2007.

Holy cow.

 

[Jonmo1]

Interesting, there must be a change to the FLOOR function from 2007 to 2010...
Presumeably the same for Ceiling.

 

[mirabeau]

Hmm...

I'm using XL2010 on Win7 Enterprise.
A1 is formatted as General
Using English United States regional settings.

from my Excel 2007 Help on the FLOOR function

• If number and significance have different signs, FLOOR returns the #NUM! error value.

 

[shg]

Eliminating the error for signs that disagree is harmless. Changing the rounding from toward 0 to toward -infinity is horrendous.

 

[Jonmo1]

from my Excel 2007 Help on the FLOOR function

• If number and significance have different signs, FLOOR returns the #NUM! error value.

Yep, shg and I just discovered there is a difference in the Floor function between XL2007 and XL2010.

Now the question remains..
Which result do you want when your number is negative..

-18000
Should that be -20000 or -10000
??

 

[Jonmo1]

Eliminating the error for signs that disagree is harmless. Changing the rounding from toward 0 to toward -infinity is horrendous.

Why?

To my logic,
-20000 is indeed DOWN from -18000

Down taking on a psudo meaning of Less Than.

This results in TRUE
=-20000<-18000

 

[shg]

Why?? Because the results of the function change from one version to the next, which would be pretty terrible if you had calculations that depended on that behavior. Previously, the only function that rounded to -infinity was INT() -- ROUNDDOWN(), TRUNC(), FLOOR(), and VBA's Fix() all round toward 0.