How to stop Excel from rounding a number?

Hi. Using Microsoft Office Excel 2007. Any help will be greatly
appreciated.

We are setting up a spreadsheet so my husband can enter:

-the price of an item
-Calculate the sales tax of .08375 and have sales tax added to price each.
-multiply the sum of the item price & sales tax by the quantity of items
entered

This is what happens:

Price ea Sales Tax
before tax Added QTY Total
$0.307 $0.33 3 $1.00

Ordinarily, the total should equal $.99

I've tried changing the formats from currency to number, to accounting to
scientific notation. Still have the problem.

When I increase the decimal points on the total column, the number is:
0.99813375

I've used the ROUNDDOWN and ROUNDDUP formulas, but some entry totals require
ROUNDDOWN while others require ROUNDUP in order to maintain accuracy. I
prefer not to figure out which of these formulas is required for each entry.

Is there a way to keep the number .99 as it is when reducing the decimal
numbers back to 2 - just chop off the rest of the numbers that follow?

Thanks!
Sue

Try rounding the price + tax added amount.

A1 = price = 0.307
B1 = price + tax (0.08375)

=ROUND(A1*(1.08375),2)

C1 = QTY = 3
D1 = Total

=C1*B1

Result = 0.99

--
Biff
Microsoft Excel MVP


"lightdancing" > wrote in message
...
> Hi. Using Microsoft Office Excel 2007. Any help will be greatly
> appreciated.
>
> We are setting up a spreadsheet so my husband can enter:
>
> -the price of an item
> -Calculate the sales tax of .08375 and have sales tax added to price each.
> -multiply the sum of the item price & sales tax by the quantity of items
> entered
>
> This is what happens:
>
> Price ea Sales Tax
> before tax Added QTY Total
> $0.307 $0.33 3 $1.00
>
> Ordinarily, the total should equal $.99
>
> I've tried changing the formats from currency to number, to accounting to
> scientific notation. Still have the problem.
>
> When I increase the decimal points on the total column, the number is:
> 0.99813375
>
> I've used the ROUNDDOWN and ROUNDDUP formulas, but some entry totals
> require
> ROUNDDOWN while others require ROUNDUP in order to maintain accuracy. I
> prefer not to figure out which of these formulas is required for each
> entry.
>
> Is there a way to keep the number .99 as it is when reducing the decimal
> numbers back to 2 - just chop off the rest of the numbers that follow?
>
> Thanks!
> Sue
>
>
>
>
>
>
>
>
>

* BIG SMILES * Thanks T. Valko. That did the trick. I looked at the
ROUND formula but didn't understand it. Still not sure how it works, but
I'm mighty glad it does!

"T. Valko" wrote:

> Try rounding the price + tax added amount.
>
> A1 = price = 0.307
> B1 = price + tax (0.08375)
>
> =ROUND(A1*(1.08375),2)
>
> C1 = QTY = 3
> D1 = Total
>
> =C1*B1
>
> Result = 0.99
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "lightdancing" > wrote in message
> ...
> > Hi. Using Microsoft Office Excel 2007. Any help will be greatly
> > appreciated.
> >
> > We are setting up a spreadsheet so my husband can enter:
> >
> > -the price of an item
> > -Calculate the sales tax of .08375 and have sales tax added to price each.
> > -multiply the sum of the item price & sales tax by the quantity of items
> > entered
> >
> > This is what happens:
> >
> > Price ea Sales Tax
> > before tax Added QTY Total
> > $0.307 $0.33 3 $1.00
> >
> > Ordinarily, the total should equal $.99
> >
> > I've tried changing the formats from currency to number, to accounting to
> > scientific notation. Still have the problem.
> >
> > When I increase the decimal points on the total column, the number is:
> > 0.99813375
> >
> > I've used the ROUNDDOWN and ROUNDDUP formulas, but some entry totals
> > require
> > ROUNDDOWN while others require ROUNDUP in order to maintain accuracy. I
> > prefer not to figure out which of these formulas is required for each
> > entry.
> >
> > Is there a way to keep the number .99 as it is when reducing the decimal
> > numbers back to 2 - just chop off the rest of the numbers that follow?
> >
> > Thanks!
> > Sue
> >
> >
> >
> >
> >
> >
> >
> >
> >
>
>
> .
>

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"lightdancing" > wrote in message
...
>* BIG SMILES * Thanks T. Valko. That did the trick. I looked at the
> ROUND formula but didn't understand it. Still not sure how it works,
> but
> I'm mighty glad it does!
>
> "T. Valko" wrote:
>
>> Try rounding the price + tax added amount.
>>
>> A1 = price = 0.307
>> B1 = price + tax (0.08375)
>>
>> =ROUND(A1*(1.08375),2)
>>
>> C1 = QTY = 3
>> D1 = Total
>>
>> =C1*B1
>>
>> Result = 0.99
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "lightdancing" > wrote in message
>> ...
>> > Hi. Using Microsoft Office Excel 2007. Any help will be greatly
>> > appreciated.
>> >
>> > We are setting up a spreadsheet so my husband can enter:
>> >
>> > -the price of an item
>> > -Calculate the sales tax of .08375 and have sales tax added to price
>> > each.
>> > -multiply the sum of the item price & sales tax by the quantity of
>> > items
>> > entered
>> >
>> > This is what happens:
>> >
>> > Price ea Sales Tax
>> > before tax Added QTY Total
>> > $0.307 $0.33 3 $1.00
>> >
>> > Ordinarily, the total should equal $.99
>> >
>> > I've tried changing the formats from currency to number, to accounting
>> > to
>> > scientific notation. Still have the problem.
>> >
>> > When I increase the decimal points on the total column, the number is:
>> > 0.99813375
>> >
>> > I've used the ROUNDDOWN and ROUNDDUP formulas, but some entry totals
>> > require
>> > ROUNDDOWN while others require ROUNDUP in order to maintain accuracy.
>> > I
>> > prefer not to figure out which of these formulas is required for each
>> > entry.
>> >
>> > Is there a way to keep the number .99 as it is when reducing the
>> > decimal
>> > numbers back to 2 - just chop off the rest of the numbers that follow?
>> >
>> > Thanks!
>> > Sue
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>>
>>
>> .
>>