Round up numbers to either a half or a whole depending on which side of 0.5 it is

J

John Williams

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
 
A

Arvi Laanemets

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
 
B

Billy Liddel

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
 
J

John Williams

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
 
R

Ron Coderre

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
 
A

Arvi Laanemets

Hi


John Williams said:
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
Click to expand...


=ROUNDUP((A1-0.1)/5,1)*5
 
B

Billy Liddel

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:F17),0.5)))

Peter
 
R

Ron Coderre

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:F17),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
 
R

Ron Rosenfeld

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
Click to expand...


=CEILING(A1,0.5)

and 15.5 will remain 15.5


--ron
 
D

daddylonglegs

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
 
J

John Williams

Hi

Thanks for the help. This is the one that I used and it seems to do just
what I want.

John
 
Top