Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Rounded numbers do not add properly.
I saw a post that asked this question and the guy who answered made him look
stupid but I agree with the man. I use Excel to do accounting work. What happens is the numbers add as the original number and not the rounded number. That can lead to some serious miscalculations. Is there a formula that can be used when multiplying or dividing that will round the number to the nearest hundredth and have the column properly add the rounded numbers? The way it is right now, it adds the whole number not the rounded. I think the IRS might have something to say if the pennies start adding up to $100s of dollars in missed tax monies. |
#3
|
|||
|
|||
Take a look he
http://www.mcgimpsey.com/excel/pennyoff.html In article , Stephanie DeCou <Stephanie wrote: I saw a post that asked this question and the guy who answered made him look stupid but I agree with the man. I use Excel to do accounting work. What happens is the numbers add as the original number and not the rounded number. That can lead to some serious miscalculations. Is there a formula that can be used when multiplying or dividing that will round the number to the nearest hundredth and have the column properly add the rounded numbers? The way it is right now, it adds the whole number not the rounded. I think the IRS might have something to say if the pennies start adding up to $100s of dollars in missed tax monies. |
#4
|
|||
|
|||
That won't necessarily solve the problem. If Precision as displayed is
set, and all cells are set to 2 decimal places: A1: 1.00 A2: =A1/3 == 0.33 A3: =A2 * 3 == 0.99 A4: =A1/3 * 3 == 1.00 A5: = A3 = A4 == FALSE In article , "Bernie Deitrick" <deitbe @ consumer dot org wrote: Use Tools | Options.... Calculation tab, check "Precision as displayed" |
#5
|
|||
|
|||
Here's another possible option.
For those times when you don't want to put round() formulas in all your cells: 1)Set the cell formats to display an appropriate number of decimal places. 2)Sum those cells using a variation of this formula: =SUMPRODUCT(ROUND(A1:A10,2)) That function rounds each number to 2 decimal places before it is summed. In my example, I put =1.004 in cells A1:A10 and set the format to 2 decimal places Each cell displays 1.00, but actuall contains 1.004. Using A11: =Sum(A1:A10), I get 10.04 I want my total to be 10.00 (1.00x10) Using A11: =SUMPRODUCT(ROUND(A1:A10,2)) I get 10.00. Does that option help? -- Regards, Ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match Last Occurrence of two numbers and Return Date | Excel Worksheet Functions | |||
Count and Sum Total occurrances of two specific numbers | Excel Worksheet Functions | |||
incorrect totals using rounded numbers | Excel Discussion (Misc queries) | |||
Converting Numbers to Text properly | Excel Discussion (Misc queries) | |||
How can I stop Excel from displaying rounded numbers? Credit car. | Excel Discussion (Misc queries) |