Rounding

rboss

New Member
Joined
Apr 23, 2002
Messages
1
Is there a way to round in Excel using the odd/even rule? For example,

I want 114,500 to round to 114,000 but Excel rounds to 115,000.

115,500 rounds to 116,000 which is ok.

If anyone can help, it would be greatly appreciated.

rboss@leitztooling.com
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Have a look at these 2 functions :

CEILING - rounds up to the nearest no. of signifcance
FLOOR - like CEILING but rounds down
 
Upvote 0
Try:

=IF(ISEVEN(INT(A1/1000)),INT(A1/1000),ROUNDUP(A1/1000,0))*1000

or

=IF(ISNUMBER(A1),IF(ISEVEN(INT(A1/1000)),INT(A1/1000),ROUNDUP(A1/1000,0))*1000,"")

Aladin
 
Upvote 0
Can you state a rule for what you want to happen?
your example is contradictory. do you want anything ending in 000 to 500 to round down to nearest thousand and anything ending in 501 to 999 to round up?
 
Upvote 0
Rounding to the nearest even number (or in this case thousands)

Try:

=EVEN(B6/1000)*1000

Adjust the formula to how many places you want to round to:

If you want to round to hundreds, divide by 100 and then multiply by 100
 
Upvote 0
According to rboss
"I want 114,500 to round to 114,000 but Excel rounds to 115,000.
115,500 rounds to 116,000 which is ok. "

which isn't rounding to the nearest even no. or rounding to the nearest ooo.
I'm trying to understand why it's ok to round 115.5 to 116, but not ok to round 114.5 to 115

we need to know this to give proper answer, don't we?
 
Upvote 0
On 2002-04-24 05:23, rboss wrote:
Is there a way to round in Excel using the odd/even rule? For example,

I want 114,500 to round to 114,000 but Excel rounds to 115,000.

115,500 rounds to 116,000 which is ok.

=IF(MOD(B5,B6)< B6/2,FLOOR(B5,B6),CEILING(B5,B6))

This is a generic formula to do what you want.The number to be rounded is in B5 and the number to be rounded to the nearest multiple is in B6, in your case 2000. It rounds to the nearest multiple of 2000 which will give you the nearest even thousands. Of course, if you won't be changing what you are rounding to, just replace B6 with 2000 in the formula.

Or, the easiest way is =MROUND(B5,2000) which requires the Analysis ToolPak.
This message was edited by Steve Hartman on 2002-04-24 12:15
 
Upvote 0
Another flavor??

=ISODD(INT(B8/1000))*MROUND(B8,1000)+ISEVEN(INT(B8/1000))*FLOOR(B8,1000)

You would need the analysis toolpack add-in to use the mround feature.
 
Upvote 0
Use Microsoft Access! They use banker's rounding by default, which makes me mad. Hartman, I remember you from the Access-Programmers-UK board.
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

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