| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
