Rounding and Ceilings

C

Colin Vicary

I think I'm going mad!

I have a list of numbers to process.

If the number is less than 5 I want to make it zero
If the number is above 5 but below 50 I want to round it to the nearest
5
If the number is above 50 I want to round up to the nearest 10

So I use:-
=IF(A1<5,0,IF(AND(A1>=5,A1<50),?????,IF(A1>50,CEILING(A1,10))))

I just can't work out what to put where the ????? are. Round only seems
to work to the nearest 10 eg ROUND(A1,-1)

Anyone know how I make it round to the nearest 5?

Thanks

Colin
 
M

Miguel Zapico

You can use CEILING again for that:
=IF(A1<5,0,IF(A1<50,CEILING(A1,5),IF(A1>50,CEILING(A1,10))))
Also, you don't need the AND function there, if the number is not greater
than 5 it will fall in the first IF.

Hope this helps,
Miguel.
 
G

Guest

Sorry - missed the adjustment! Try this one:
=IF(A1<5,0,IF(A1>50,CEILING(A1,10),CEILING(A1-2,5)))

Andy.
 
C

Colin Vicary

Thanks for the reply guys, we're almost there!

If the number is between 5 and 50 I need it to round up OR down to the
nearest 5.

EG if the number is 12 it needs to become 10, if it's 13 it needs to
become 15

Thanks

Colin
 
M

Miguel Zapico

How about this one:
=IF(A1<5,0,IF(A1<50,ROUND(A1/5,0)*5,CEILING(A1,10)))

Miguel.
 
D

David Biddulph

"Colin Vicary" <[email protected]>
wrote in message
I think I'm going mad!

I have a list of numbers to process.

If the number is less than 5 I want to make it zero
If the number is above 5 but below 50 I want to round it to the nearest
5
If the number is above 50 I want to round up to the nearest 10

So I use:-
=IF(A1<5,0,IF(AND(A1>=5,A1<50),?????,IF(A1>50,CEILING(A1,10))))

I just can't work out what to put where the ????? are. Round only seems
to work to the nearest 10 eg ROUND(A1,-1)

Anyone know how I make it round to the nearest 5?
Click to expand...

Replace your ????? by MROUND(A1,5)

It uses the Analysis ToolPak.
 
C

Colin Vicary

Thanks for all the suggestions!

Thanks particularly to Andy, that's the one I'll use.

Colin
 
Top