| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Round up numbers to either a half or a whole depending on which side of 0.5 it is
Hi
Is there any way in Excel 2002 to do the above. E.g. My vertical sum comes to 15.2 so I want that changed to 15.5 or My vertical sum comes to 15.6 so I want that changed to 16 It doesn't matter if the rounding takes place in a different cell to the sum. TIA John |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Round up numbers to either a half or a whole depending on which side of 0.5 it is
Hi
Activate Analysis Toolpack Add-In (when you can't, you have to install it from Office Setup CD). Now you can use ROUNDUP/ROUNDDOWN functions =ROUNDUP(A1/5,1)*5 -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "John Williams" wrote in message ... Hi Is there any way in Excel 2002 to do the above. E.g. My vertical sum comes to 15.2 so I want that changed to 15.5 or My vertical sum comes to 15.6 so I want that changed to 16 It doesn't matter if the rounding takes place in a different cell to the sum. TIA John |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Round up numbers to either a half or a whole depending on which si
or try
=IF(SUM(F16:F17)-INT(SUM(F16:F17))<0.5,ROUND(SUM(F16:F17),0.5)+0.5, ROUND(SUM(F16:F17),0.5)) regards Peter "John Williams" wrote: Hi Is there any way in Excel 2002 to do the above. E.g. My vertical sum comes to 15.2 so I want that changed to 15.5 or My vertical sum comes to 15.6 so I want that changed to 16 It doesn't matter if the rounding takes place in a different cell to the sum. TIA John |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Round up numbers to either a half or a whole depending on which side of 0.5 it is
Hi
Thanks for the two replies both of you. They both work, but if the sum does equal say 15.5 then they both round that up to 16 and not leave it at 15.5 Perhaps I should have said that in the original post Regards John "John Williams" wrote in message ... Hi Is there any way in Excel 2002 to do the above. E.g. My vertical sum comes to 15.2 so I want that changed to 15.5 or My vertical sum comes to 15.6 so I want that changed to 16 It doesn't matter if the rounding takes place in a different cell to the sum. TIA John |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Round up numbers to either a half or a whole depending on which si
Try this:
With A1: (a numeric value) This formula rounds up in increments of 0.5 B1: =CEILING(A1,0.5) Does that help? *********** Regards, Ron XL2002, WinXP "John Williams" wrote: Hi Is there any way in Excel 2002 to do the above. E.g. My vertical sum comes to 15.2 so I want that changed to 15.5 or My vertical sum comes to 15.6 so I want that changed to 16 It doesn't matter if the rounding takes place in a different cell to the sum. TIA John |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Round up numbers to either a half or a whole depending on which side of 0.5 it is
Hi
"John Williams" wrote in message ... Hi Thanks for the two replies both of you. They both work, but if the sum does equal say 15.5 then they both round that up to 16 and not leave it at 15.5 =ROUNDUP((A1-0.1)/5,1)*5 -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Round up numbers to either a half or a whole depending on whic
or this
=IF(SUM(F16:F17)-INT(SUM(F16:F17))<0.5,ROUND(SUM(F16:F17),0.5)+0.5, IF(SUM(F16:F17)-INT(SUM(F16:F17))=0.5,SUM(F16:F17),ROUND(SUM(F16:F 17),0.5))) Peter "Ron Coderre" wrote: Try this: With A1: (a numeric value) This formula rounds up in increments of 0.5 B1: =CEILING(A1,0.5) Does that help? *********** Regards, Ron XL2002, WinXP "John Williams" wrote: Hi Is there any way in Excel 2002 to do the above. E.g. My vertical sum comes to 15.2 so I want that changed to 15.5 or My vertical sum comes to 15.6 so I want that changed to 16 It doesn't matter if the rounding takes place in a different cell to the sum. TIA John |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Round up numbers to either a half or a whole depending on whic
Regarding:
=IF(SUM(F16:F17)-INT(SUM(F16:F17))<0.5,ROUND(SUM(F16:F17),0.5)+0.5, IF(SUM(F16:F17)-INT(SUM(F16:F17))=0.5,SUM(F16:F17),ROUND(SUM(F16:F 17),0.5))) .....that would be better than this? =CEILING(SUM(F16:F17),0.5) Also, the long formula you posted returns 0.5 if F16:F17 are both blanks or zeroes. *********** Regards, Ron XL2002, WinXP "Billy Liddel" wrote: or this =IF(SUM(F16:F17)-INT(SUM(F16:F17))<0.5,ROUND(SUM(F16:F17),0.5)+0.5, IF(SUM(F16:F17)-INT(SUM(F16:F17))=0.5,SUM(F16:F17),ROUND(SUM(F16:F 17),0.5))) Peter "Ron Coderre" wrote: Try this: With A1: (a numeric value) This formula rounds up in increments of 0.5 B1: =CEILING(A1,0.5) Does that help? *********** Regards, Ron XL2002, WinXP "John Williams" wrote: Hi Is there any way in Excel 2002 to do the above. E.g. My vertical sum comes to 15.2 so I want that changed to 15.5 or My vertical sum comes to 15.6 so I want that changed to 16 It doesn't matter if the rounding takes place in a different cell to the sum. TIA John |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Round up numbers to either a half or a whole depending on which side of 0.5 it is
On Fri, 24 Nov 2006 11:13:07 GMT, "John Williams"
wrote: Hi Is there any way in Excel 2002 to do the above. E.g. My vertical sum comes to 15.2 so I want that changed to 15.5 or My vertical sum comes to 15.6 so I want that changed to 16 It doesn't matter if the rounding takes place in a different cell to the sum. TIA John =CEILING(A1,0.5) and 15.5 will remain 15.5 --ron |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Round up numbers to either a half or a whole depending on whic
If A1 contains exactly 15.5 then Arvi's suggestion will leave it at that. If
you get a result of 16 then I suggest that A1 actually contains a value slightly greater than 15.5 - try formatting with multiple decimal places... Note ROUNDUP is a native excel function, not part of Analysis ToolPak. CEILING suggestion would be my choice....unless you have negative values "John Williams" wrote: Hi Thanks for the two replies both of you. They both work, but if the sum does equal say 15.5 then they both round that up to 16 and not leave it at 15.5 Perhaps I should have said that in the original post Regards John "John Williams" wrote in message ... Hi Is there any way in Excel 2002 to do the above. E.g. My vertical sum comes to 15.2 so I want that changed to 15.5 or My vertical sum comes to 15.6 so I want that changed to 16 It doesn't matter if the rounding takes place in a different cell to the sum. TIA John |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Round up numbers to either a half or a whole depending on which side of 0.5 it is
Hi
Thanks for the help. This is the one that I used and it seems to do just what I want. John "Arvi Laanemets" wrote in message ... Hi "John Williams" wrote in message ... Hi Thanks for the two replies both of you. They both work, but if the sum does equal say 15.5 then they both round that up to 16 and not leave it at 15.5 =ROUNDUP((A1-0.1)/5,1)*5 -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I round numbers to the nearest multiple of 10 or 5? | Excel Worksheet Functions | |||
| How to round negative and positive numbers | Excel Worksheet Functions | |||
| Round up to next half number | New Users to Excel | |||
| round to one of two numbers | Excel Worksheet Functions | |||
| numbers dont round up | Excel Discussion (Misc queries) | |||
Linear Mode
