Rounding Issuewith .5

[Pauline]

I already have a formula in a cell that is formatted to a percentage with no
decimal places. The problem I have is that excel decides to either round up
or down the result if it ends with .5 How can I ensure that the final result
is rounded up to the next full number if if currently ends with .5

For example 7.1 + 8.6 = 16 if no decimal points.
But 7.1 + 8.4 can either = 15 or 16 (no decimal places). How can I ensure
that this always shows 16 as 15.5 should be rounded up to 16 not down.

 

[chalky]

Use =ROUNDUP() when you add the figures together but if it comes to .5
it will always round up as a matter of course.

 

[Pauline]

I think you have misunderstood my issue. Here is my curretn formula:

=(K9/O9)*12

The end result in the cell currently shows 24.5.....

How do I ensure that anything ending .5 is rounded up. Excel sometimes
rounds up and sometimes rounds down depending on the other decimal numbers.

Pauline

 

[Bob Phillips]

I think you problem is that the values are not really 7.1 and/or 8.4, they
just appear that way. For instance, if the first value is 7.09 but
displaying to one decimal place, it appears like 7.1 , but when added the
total is 15.49, which will round down. One solution is (and don't laugh)

=ROUND(ROUND(K1,1)+ROUND(K2,1),0)

which forces it to use the numbers o the same precision as displayed.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[David Biddulph]

I think you problem is that the values are not really 7.1 and/or 8.4, they
just appear that way. For instance, if the first value is 7.09 but
displaying to one decimal place, it appears like 7.1 , but when added the
total is 15.49, which will round down. One solution is (and don't laugh)

=ROUND(ROUND(K1,1)+ROUND(K2,1),0)

which forces it to use the numbers o the same precision as displayed.

But even if you do have numbers that include exactly 0.5, remember that
there are many different types of rounding (see
http://support.microsoft.com/kb/q196652/).

 

[Pauline]

Thank you for your formula ut I cannot see where this fits with my orginal
formula (which must remain). Please could you let me know how I added
rounding formula to this formaula: =(K9/O9)*12

(K9/09)*12 must remain in the final formula otherwise my end result is
incorrect.

REgards

Pauline

 

[Bob Phillips]

You didn't mention that formula in your original post, so I responded on
what you wrote not what you actually had.

Again you don't say what is in K9 or O9, or what you woant to see, so it is
hard to be precise, but maybe

=ROUND(((ROUND(K9,1)/ROUND(O9,1))*12),0)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)