| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Round up to next half number
I am trying to round up a number to what I recall from school days to be
"correct to the nearest half". In other words, if the cell value is between 3.01 and 3.49 I want to round it to 3.5. If the cell value is 3.5 to 3.99 I want it rounded to 4.0 I have managed to round up and down to the nearest whole number but can't figure out how to get it to the nearest half! Can anyone help, please? RMB |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Round up to next half number
How's this =ROUNDUP(A1*2,0)/2
best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "rmb4253" wrote in message ... I am trying to round up a number to what I recall from school days to be "correct to the nearest half". In other words, if the cell value is between 3.01 and 3.49 I want to round it to 3.5. If the cell value is 3.5 to 3.99 I want it rounded to 4.0 I have managed to round up and down to the nearest whole number but can't figure out how to get it to the nearest half! Can anyone help, please? RMB |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Round up to next half number
You don't really want 3.5 to become 4 do you? perhaps, if you only have positive numbers =CEILING(A1,0.5) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=525473 |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Round up to next half number
Bernard, Thanks very much - that is marvellous!
RMB "Bernard Liengme" wrote: How's this =ROUNDUP(A1*2,0)/2 best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "rmb4253" wrote in message ... I am trying to round up a number to what I recall from school days to be "correct to the nearest half". In other words, if the cell value is between 3.01 and 3.49 I want to round it to 3.5. If the cell value is 3.5 to 3.99 I want it rounded to 4.0 I have managed to round up and down to the nearest whole number but can't figure out how to get it to the nearest half! Can anyone help, please? RMB |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Round up to next half number
Happy to help
-- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "rmb4253" wrote in message ... Bernard, Thanks very much - that is marvellous! RMB "Bernard Liengme" wrote: How's this =ROUNDUP(A1*2,0)/2 best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "rmb4253" wrote in message ... I am trying to round up a number to what I recall from school days to be "correct to the nearest half". In other words, if the cell value is between 3.01 and 3.49 I want to round it to 3.5. If the cell value is 3.5 to 3.99 I want it rounded to 4.0 I have managed to round up and down to the nearest whole number but can't figure out how to get it to the nearest half! Can anyone help, please? RMB |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Round up to next half number
daddylonglegs,
Yes I did want 3.5 to become 4 - but thanks for the formula anyway - I've never used "Ceiling" before! I'll have to read up on it! RMB "daddylonglegs" wrote: You don't really want 3.5 to become 4 do you? perhaps, if you only have positive numbers =CEILING(A1,0.5) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=525473 |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Round up to next half number
For positive numbers =ROUNDUP(A1*2,0)/2 does the same as =CEILING(A1,0.5) neither of them will return 4 when A1 is equal to 3.5, what result do you want when A1 is 3? -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=525473 |
|
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Round up to next half number
Daddylonglegs,
Thanks for that! Actually you were right when you said I wouldn't want 3.5 rounded up so what I need is if the result is 3, then I want it to stay as 3. Result of 3.01 to 3.5 to be 3.5 and 3.51 to 4 to be 4. Your suggestion and that of Bernard's both do that so thanks again! RMB "daddylonglegs" wrote: For positive numbers =ROUNDUP(A1*2,0)/2 does the same as =CEILING(A1,0.5) neither of them will return 4 when A1 is equal to 3.5, what result do you want when A1 is 3? -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=525473 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Preceding a number by zeros, that is still a number | Excel Worksheet Functions | |||
| Number of labels on X-axis one more than number of values on Y-axi | Charts and Charting in Excel | |||
| Count Number of Characters in a cell? | Excel Discussion (Misc queries) | |||
| Need number of Saturdays and number of Sundays between 2 dates | Excel Worksheet Functions | |||
| How to format a number in Indian style in Excel? | Excel Discussion (Misc queries) | |||
Linear Mode
