Rounding

rboss · Apr 24th, 2002, 06:23 AM

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

Iain Lewis · Apr 24th, 2002, 06:28 AM

Have a look at these 2 functions :

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

Aladin Akyurek · Apr 24th, 2002, 06:59 AM

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

Big Blue · Apr 24th, 2002, 07:30 AM

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?

klb · Apr 24th, 2002, 09:37 AM

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

Big Blue · Apr 24th, 2002, 11:02 AM

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?

Steve Hartman · Apr 24th, 2002, 11:12 AM

He/she wants to round to the nearest even thousand

Steve Hartman · Apr 24th, 2002, 11:33 AM

Quote:

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 ]

Drew · Apr 24th, 2002, 12:02 PM

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.

dhoffman · Apr 24th, 2002, 12:50 PM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 24th, 2002, 06:23 AM	#1
rboss New Member Join Date: Apr 2002 Location: Excel Posts: 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

Apr 24th, 2002, 06:28 AM	#2
Iain Lewis Board Regular Join Date: Apr 2002 Location: Midlands, UK Posts: 217	Have a look at these 2 functions : CEILING - rounds up to the nearest no. of signifcance FLOOR - like CEILING but rounds down

Apr 24th, 2002, 06:59 AM	#3
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,317	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

Apr 24th, 2002, 07:30 AM	#4
Big Blue Board Regular Join Date: Mar 2002 Location: Ontario Posts: 52	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?

Apr 24th, 2002, 09:37 AM	#5
klb Board Regular Join Date: Apr 2002 Location: Minnesota Posts: 821	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

Apr 24th, 2002, 11:02 AM	#6
Big Blue Board Regular Join Date: Mar 2002 Location: Ontario Posts: 52	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?

Apr 24th, 2002, 11:12 AM	#7
Steve Hartman Board Regular Join Date: Feb 2002 Location: Houston,Texas Posts: 418	He/she wants to round to the nearest even thousand

Apr 24th, 2002, 12:02 PM	#9
Drew Board Regular Join Date: Feb 2002 Location: Boston, Mass Posts: 169	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.

Apr 24th, 2002, 12:50 PM	#10
dhoffman New Member Join Date: Apr 2002 Location: Lawrence, KS Posts: 29	Use Microsoft Access! They use banker's rounding by default, which makes me mad. Hartman, I remember you from the Access-Programmers-UK board.