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

"chalky" wrote:

>
> Use =ROUNDUP() when you add the figures together but if it comes to .5
> it will always round up as a matter of course.
>
>
> --
> chalky
> ------------------------------------------------------------------------
> chalky's Profile: http://www.excelforum.com/member.php...o&userid=23758
> View this thread: http://www.excelforum.com/showthread...hreadid=534566
>
>

[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)

"Pauline" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>

[David Biddulph]

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...

> "Pauline" <[email protected]> wrote in message
> news:[email protected]...
>> 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.

>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/).
--
David Biddulph

[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" wrote:

> 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)
>
> "Pauline" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
> >
>
>
>

[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)

"Pauline" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
> > 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)
> >
> > "Pauline" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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.
> > >
> > >
> >
> >
> >