Round to the nearest 10,000

eoinymc

Board Regular
Joined
Jan 29, 2009
Messages
203
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
 
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]
 
Upvote 0
Hi Eoin

Not clear to me the negative value case.

-18000

Do you still want to round down (-20000) or up (-10000) ?
 
Upvote 0
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.
 
Upvote 0
Interesting, there must be a change to the FLOOR function from 2007 to 2010...
Presumeably the same for Ceiling.
 
Upvote 0
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.
 
Upvote 0
Eliminating the error for signs that disagree is harmless. Changing the rounding from toward 0 to toward -infinity is horrendous.
 
Upvote 0
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
??
 
Upvote 0
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
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,226,867
Messages
6,193,428
Members
453,799
Latest member
shanley ducker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top