Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel formula is calculating wrong total number on some of cells
Hi!
I having 1 problem that Excel formula is calculating wrong total number on some of cells. For example: 1)M36=2.83 2)O36=ROUND(M36*1.075,2) 3)Q36=O36*110% 4)L36=1434 5)R36=L36*Q36 The R36 total is 4795.30 (wrong) When I used calculator to calcalate it, the correct total is 4789.56 (Formula is 3.34*1434=4789.56). May I know how to solve this problem? Please give advise. Thank you! Regards, Alan |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel formula is calculating wrong total number on some of cells
You need to recheck what you did with your calculator, as you got it wrong.
Q36 is 3.344, not 3.344 Please check your own working before you suggest that Excel has got things wrong. -- David Biddulph "Alan" wrote in message ... Hi! I having 1 problem that Excel formula is calculating wrong total number on some of cells. For example: 1)M36=2.83 2)O36=ROUND(M36*1.075,2) 3)Q36=O36*110% 4)L36=1434 5)R36=L36*Q36 The R36 total is 4795.30 (wrong) When I used calculator to calcalate it, the correct total is 4789.56 (Formula is 3.34*1434=4789.56). May I know how to solve this problem? Please give advise. Thank you! Regards, Alan |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel formula is calculating wrong total number on some of cells
On Tue, 10 Mar 2009 08:31:30 -0000, "David Biddulph" <groups [at]
biddulph.org.uk wrote: Q36 is 3.344, not 3.344 ???? --ron |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel formula is calculating wrong total number on some of cells
On Tue, 10 Mar 2009 01:04:01 -0700, Alan
wrote: Hi! I having 1 problem that Excel formula is calculating wrong total number on some of cells. For example: 1)M36=2.83 2)O36=ROUND(M36*1.075,2) 3)Q36=O36*110% 4)L36=1434 5)R36=L36*Q36 The R36 total is 4795.30 (wrong) When I used calculator to calcalate it, the correct total is 4789.56 (Formula is 3.34*1434=4789.56). May I know how to solve this problem? Please give advise. Thank you! Regards, Alan Your calculator is rounding the step where you compute O36 * 110%. The correct answer to that is 3.344, but your calculator is dropping the last .004. If you want the "correct" answer, you will have to figure out why your calculator does not use more than two decimal digits. If you want Excel to agree with your calculator, you will have to either truncate or round your Q36 formula in the same way as your calculator is altering the results. --ron |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel formula is calculating wrong total number on some of cells
On Mar 10, 12:04*am, Alan wrote:
1)M36=2.83 2)O36=ROUND(M36*1.075,2) 3)Q36=O36*110% 4)L36=1434 5)R36=L36*Q36 The R36 total is 4795.30 (wrong) When I used calculator to calcalate it, the correct total is 4789.56 (Formula is 3.34*1434=4789.56). The Excel result in R36 is correct (approximately) for the procedure that you describe. I suspect you are fooling yourself because some cells (notably Q36 and R36) are formatted with 2 decimal places. As you describe it, Q36 is actually about 3.344. That might __display__ as 3.34 due to cell formatting. But the __actual__ underlying value is still about 3.344. Consequently, the formula in R36 is effectively 3.344*1434, not 3.34*1434. The extra 0.004*1434 is sufficient to explain the difference that you see. Moreover, it should be noted that R36 is really about 4795.296, not 4795.30 -- again, because of the difference between the displayed value due to cell format and actual underlying value. This might propagate "error" (misunderstanding) throughout your worksheet. At a minimum, apparently you want the formula in Q36 to be =ROUND (O36*110%,2). It would be prudent to also change the formula in R36 to =ROUND(L36*Q36,2). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating total number of days | Excel Worksheet Functions | |||
Formula total wrong | Excel Worksheet Functions | |||
Calculating Total Number of Minutes in a Clock Setting | Excel Discussion (Misc queries) | |||
total and subtotal positions wrong in excel | Excel Discussion (Misc queries) | |||
CALCULATING CELLS AS A TOTAL | Excel Discussion (Misc queries) |