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

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

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

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

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

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 )

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

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

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

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

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 )