Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Column total is wrong
I have a column of figures which is the result of multiplied column A x
Column B. Column B is a multiplier of .001665. The result is shown to two decimal places. When I total the results in Column B, using formula =SUM(B1:B2), it gives the result as 11.16 instead of 11.15. Can I fix this somehow so it gives the correct total? Example A Formula in B Result in B 3250.00 =A1x0.001665 5.41 3450.00 =A2x0.001665 5.74 Total 11.16 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Column total is wrong
Use rounding on the multiplier formula:
=ROUND(A1*0.001665,2) -- Biff Microsoft Excel MVP "Carolan" wrote in message ... I have a column of figures which is the result of multiplied column A x Column B. Column B is a multiplier of .001665. The result is shown to two decimal places. When I total the results in Column B, using formula =SUM(B1:B2), it gives the result as 11.16 instead of 11.15. Can I fix this somehow so it gives the correct total? Example A Formula in B Result in B 3250.00 =A1x0.001665 5.41 3450.00 =A2x0.001665 5.74 Total 11.16 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Column total is wrong
Hi,
what happens is that you have more decimals like 5.41 really is 5.41125 and 5.74 is 5.74425 this is why you get 11.16. try using this formula =FLOOR(SUM(B6:B7),0.01) "Carolan" wrote: I have a column of figures which is the result of multiplied column A x Column B. Column B is a multiplier of .001665. The result is shown to two decimal places. When I total the results in Column B, using formula =SUM(B1:B2), it gives the result as 11.16 instead of 11.15. Can I fix this somehow so it gives the correct total? Example A Formula in B Result in B 3250.00 =A1x0.001665 5.41 3450.00 =A2x0.001665 5.74 Total 11.16 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Column total is wrong
Your problem is that cell is rounding the data up. The result is not 5.41.
Its really 5.41125. Result 2 is really 5.74425. Together your true total is 11.1555. Or rounded up to 11.16. You could use another decimal point to give you a more exact answer. "Carolan" wrote: I have a column of figures which is the result of multiplied column A x Column B. Column B is a multiplier of .001665. The result is shown to two decimal places. When I total the results in Column B, using formula =SUM(B1:B2), it gives the result as 11.16 instead of 11.15. Can I fix this somehow so it gives the correct total? Example A Formula in B Result in B 3250.00 =A1x0.001665 5.41 3450.00 =A2x0.001665 5.74 Total 11.16 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Column total is wrong
Thank you. This one worked the best.
"T. Valko" wrote: Use rounding on the multiplier formula: =ROUND(A1*0.001665,2) -- Biff Microsoft Excel MVP "Carolan" wrote in message ... I have a column of figures which is the result of multiplied column A x Column B. Column B is a multiplier of .001665. The result is shown to two decimal places. When I total the results in Column B, using formula =SUM(B1:B2), it gives the result as 11.16 instead of 11.15. Can I fix this somehow so it gives the correct total? Example A Formula in B Result in B 3250.00 =A1x0.001665 5.41 3450.00 =A2x0.001665 5.74 Total 11.16 . |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Column total is wrong
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Carolan" wrote in message ... Thank you. This one worked the best. "T. Valko" wrote: Use rounding on the multiplier formula: =ROUND(A1*0.001665,2) -- Biff Microsoft Excel MVP "Carolan" wrote in message ... I have a column of figures which is the result of multiplied column A x Column B. Column B is a multiplier of .001665. The result is shown to two decimal places. When I total the results in Column B, using formula =SUM(B1:B2), it gives the result as 11.16 instead of 11.15. Can I fix this somehow so it gives the correct total? Example A Formula in B Result in B 3250.00 =A1x0.001665 5.41 3450.00 =A2x0.001665 5.74 Total 11.16 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
My formula gives a wrong total...help! | Excel Discussion (Misc queries) | |||
addition total is wrong | Excel Worksheet Functions | |||
Sum Wrong total but add each cell gives the correct total | Excel Worksheet Functions | |||
Formula total wrong | Excel Worksheet Functions | |||
Sum Function with wrong total shown | Excel Worksheet Functions |