How to round up or down a number in multiples of 50

[advil]

I would like to round numbers in multiples of 50 depending on the situation.

I need to round up a number if the last two intergers are greater than 25, and I need to round down if the last two intergers are less than 25.

(Ex: 838 is to be rounded up to 850 and 815 is to be rounded down to 800)

 

[PaddyD]

welcome to the board!

simple solution:

=IF(RIGHT(A2,2)+0>=25,CEILING(A2,50),FLOOR(A2,50))

As in:

Book7
	A	B	C	D
1	Num	Rounded
2	308	300
3	383	400
4	363	400
5	449	450
6	397	400
7	120	100
8	110	100
9	184	200
10	391	400
11	315	300
12
Sheet1

Paddy

 

[anno]

paddyd
nice one. i was looking at something similar but couldn't get it to work because i didn't have the +0. why is there a need for coercion in this case?
thanks
anno

 

[PaddyD]

because right() is a text function, so the resulting 2 character text string needs to gets coerced back into a number (in this case by adding 0)

Paddy

 

[anno]

paddyd
oh (slaps forehead dramatically), of course. duh.
thanks
anno

 

[PaddyD]

 

[advil]

Thanks very much for your assistance on the formula.

You guys are great!