You can use CEILING again for that:
=IF(A1<5,0,IF(A1<50,CEILING(A1,5),IF(A150,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.

"Colin Vicary" wrote:


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(A150,CEIL ING(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


--
Colin Vicary
------------------------------------------------------------------------
Colin Vicary's Profile: http://www.excelforum.com/member.php...o&userid=10472
View this thread: http://www.excelforum.com/showthread...hreadid=545079