Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
stop number rounding | New Users to Excel | |||
How to stop excel from rounding ? | Excel Discussion (Misc queries) | |||
stop Excel from rounding 22/32 to 11/16 | Excel Discussion (Misc queries) | |||
How can I stop rounding in Excel? | Excel Discussion (Misc queries) | |||
Can I stop rounding in excel? | Excel Discussion (Misc queries) |