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

How to stop Excel from rounding a number?
 

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

How to stop Excel from rounding a number?
 

* 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











.

How to stop Excel from rounding a number?
 

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











.