Rounding and Ceilings
 

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

Rounding and Ceilings
 

Hi

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

Andy.

"Colin Vicary"
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(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

Rounding and Ceilings
 

Colin,

Try,

=IF(A1<5,0,IF(AND(A15,A1<50),CEILING(A1,5),CEILIN G(A1,10)))

HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=545079

Rounding and Ceilings
 

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

Rounding and Ceilings
 

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

Andy.

<Andy wrote in message ...
Hi

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

Andy.

"Colin Vicary"
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(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

Rounding and Ceilings
 

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


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

Rounding and Ceilings
 

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

"Colin Vicary"
wrote in message
news:Colin.Vicary.28bg01_1148481009.4271@excelforu m-nospam.com...

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


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

Rounding and Ceilings
 

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

Miguel.

"Colin Vicary" wrote:


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


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

Rounding and Ceilings
 

"Colin Vicary"
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(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?

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

It uses the Analysis ToolPak.
--
David Biddulph

Rounding and Ceilings
 

Thanks for all the suggestions!

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

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

Rounding and Ceilings
 

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

Andy.

"Colin Vicary"
wrote in message
news:Colin.Vicary.28crga_1148542501.3704@excelforu m-nospam.com...

Thanks for all the suggestions!

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

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