Column total is wrong
in [Functions]
|
From: Carolan on 3 Feb 2010 13:13 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
From: T. Valko on 3 Feb 2010 13:25 Use rounding on the multiplier formula: =ROUND(A1*0.001665,2) -- Biff Microsoft Excel MVP "Carolan" <Carolan(a)discussions.microsoft.com> wrote in message news:80F8A026-2725-4A94-9E15-4B89368BB6C3(a)microsoft.com... >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 >
From: Eduardo on 3 Feb 2010 13:46 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 >
From: John on 3 Feb 2010 13:48 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 >
From: Carolan on 3 Feb 2010 14:23
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" <Carolan(a)discussions.microsoft.com> wrote in message > news:80F8A026-2725-4A94-9E15-4B89368BB6C3(a)microsoft.com... > >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 > > > > > . > |