rounding

[josorens]

hi there,

problem:

I need to roundup numbers to nearest 5 or 9.

example:
142,95 to 145,00
92,54 to 95,00
97,02 to 99,00

I tried using the round functions, but didnt get any good results.

Do you have a suggestion?

Thanks and cheers

 

[Guitarde]

I did it in 3 columns:

Book3
	B	C	D	E
9	Value	Next9	Next5	Lowest
10
11	142.95	149	145	145
12	92.54	99	95	95
13	97.02	99	100	99
14	99.1	109	100	100
Sheet1

 

[Guitarde]

If you wanted it to be in one cell then the formula is:
=MIN(ROUNDDOWN(ROUNDUP(B12;0)/10;0)*10+9;CEILING(B12;5))

 

[josorens]

Thanks for your help! however I ran into a problem with your formula

Initially I wanted the formula to roundup to nearest 5 or 9.

If I use your formula, the number 39,99 rounds up to 40 instead of the nearest roundup to 45.

Can you fix this please?

Cheers

 

[rrdonutz]

One possibility:

=FLOOR(A1,10)+IF(MOD(A1,10)<=5,5,IF(MOD(A1,10)<=9,9,15))

[EDIT] But probably better as:

=FLOOR(A1,10)+IF(A1=0,0,IF(MOD(A1,10)<=5,5,IF(MOD(A1,10)<=9,9,15)))