Thanks:  0
Likes:  0

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

2. Have a look at these 2 functions :

CEILING - rounds up to the nearest no. of signifcance
FLOOR - like CEILING but rounds down

3. 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,"")

4. 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?

5. 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

6. 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?

7. He/she wants to round to the nearest even thousand

8. 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 ]

9. 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.

10. Use Microsoft Access! They use banker's rounding by default, which makes me mad. Hartman, I remember you from the Access-Programmers-UK board.

User Tag List

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•