Rounding and Ceilings

[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

 

[Guest]

Hi

Try this:
=IF(A1<5,0,IF(A1>50,CEILING(A1,10),CEILING(A1,5)))

Andy.

"Colin Vicary" <[email protected]>
wrote in message
news:[email protected]...

 

[SteveG]

Colin,

Try,

=IF(A1<5,0,IF(AND(A1>5,A1<50),CEILING(A1,5),CEILING(A1,10)))

HTH

Steve

 

[Guest]

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.

 

[Guest]

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

Andy.

 

[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

 

[Guest]

Hi
Check my second post. I reckon that's OK.
Andy.

"Colin Vicary" <[email protected]>
wrote in message
news:[email protected]...

 

[Guest]

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

Miguel.

 

[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?

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

It uses the Analysis ToolPak.

 

[Colin Vicary]

Thanks for all the suggestions!

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

Colin

 

[Guest]

Thanks for the feedback and I'm glad your sorted!

Andy.

"Colin Vicary" <[email protected]>
wrote in message
news:[email protected]...