need sumproduct
I have two columns (e.g. a1:a100 and b1:b100) filled numeric values and i need the sumproduct of the two columns of data, considering that it will only read or sum the each result up to 2 decimal places only ....
can anyone help me out with this with a single formula. e.g. [col A] [col B] [rounded product A*B] [$ 1.233 ] [ 33.222 ] [ $ 40.96 ] [$ 0.245 ] [ 11.111 ] [ $ 2.72 ] [$ 0.122 ] [ -3.215 ] [ $ -0.39 ] from the above sample the sum of the product (2 decimals) = $43.29 can anyone help me out with this using a single formula for my quick presentation. any of your suggestion is requested. thanks romelsb 4pinoy :) on banter |
need sumproduct
It depends on what you actually want. Try these based on your example and
note the difference: =ROUND(SUMPRODUCT(A1:A3,B1:B3),2) =SUMPRODUCT(ROUND(A1:A3,2),ROUND(B1:B3,2)) =ROUND(SUMPRODUCT(ROUND(A1:A3,2),ROUND(B1:B3,2)),2 ) The first formula returns the result you posted. Biff "driller2" wrote in message ... I have two columns (e.g. a1:a100 and b1:b100) filled numeric values and i need the sumproduct of the two columns of data, considering that it will only read or sum the each result up to 2 decimal places only .... can anyone help me out with this with a single formula. e.g. [col A] [col B] [rounded product A*B] [$ 1.233 ] [ 33.222 ] [ $ 40.96 ] [$ 0.245 ] [ 11.111 ] [ $ 2.72 ] [$ 0.122 ] [ -3.215 ] [ $ -0.39 ] from the above sample the sum of the product (2 decimals) = $43.29 can anyone help me out with this using a single formula for my quick presentation. any of your suggestion is requested. thanks romelsb 4pinoy :) on banter -- driller2 |
Still unknown, thanks for your suggestion T.Valko,
From below new sample (e.g) i had tried the three(3) suggested formula. [col A] [col B] [rounded product A*B] [$ 1.233 ] [ 33.22 ] [ $ 40.96 ] [$ 0.245 ] [ 11.21 ] [ $ 2.75 ] [$ 0.122 ] [ -3.215 ] [ $ -0.39 ] from the above sample the sum of the product (2 decimals) = $43.32 (in thousands...) =ROUND(SUMPRODUCT(A1:A3,B1:B3),2) result is [$43.31 < $43.32] =SUMPRODUCT(ROUND(A1:A3,2),ROUND(B1:B3,2)) result is [$43.2767 < $43.32] =ROUND(SUMPRODUCT(ROUND(A1:A3,2),ROUND(B1:B3,2)),2 ) result is [$43.28 < $43.32] ___ since last week, this is still a mystery for me where to find a single formula to do this job, perfectly. I do tried Roundup and Roundown, even, but the perfect result varies as the data changes ___ Hope anyone else can give a single sumproduct formula or other evolved formula. happy holidays hohoho...:) romelsb 4pinoy :) on banter [quote=T. Valko]It depends on what you actually want. Try these based on your example and note the difference: =ROUND(SUMPRODUCT(A1:A3,B1:B3),2) =SUMPRODUCT(ROUND(A1:A3,2),ROUND(B1:B3,2)) =ROUND(SUMPRODUCT(ROUND(A1:A3,2),ROUND(B1:B3,2)),2 ) The first formula returns the result you posted. Biff |
need sumproduct
See this:
http://cpearson.com/excel/rounding.htm Biff "driller2" wrote in message ... Still unknown, thanks for your suggestion T.Valko, From below new sample (e.g) i had tried the three(3) suggested formula. [col A] [col B] [rounded product A*B] [$ 1.233 ] [ 33.22 ] [ $ 40.96 ] [$ 0.245 ] [ 11.21 ] [ $ 2.75 ] [$ 0.122 ] [ -3.215 ] [ $ -0.39 ] from the above sample the sum of the product (2 decimals) = $43.32 (in thousands...) =ROUND(SUMPRODUCT(A1:A3,B1:B3),2) result is [$43.31 < $43.32] =SUMPRODUCT(ROUND(A1:A3,2),ROUND(B1:B3,2)) result is [$43.2767 < $43.32] =ROUND(SUMPRODUCT(ROUND(A1:A3,2),ROUND(B1:B3,2)),2 ) result is [$43.28 < $43.32] ___ since last week, this is still a mystery for me where to find a single formula to do this job, perfectly. I do tried Roundup and Roundown, even, but the perfect result varies as the data changes ___ Hope anyone else can give a single sumproduct formula or other evolved formula. happy holidays hohoho...:) romelsb 4pinoy :) on banter T. Valko Wrote: It depends on what you actually want. Try these based on your example and note the difference: =ROUND(SUMPRODUCT(A1:A3,B1:B3),2) =SUMPRODUCT(ROUND(A1:A3,2),ROUND(B1:B3,2)) =ROUND(SUMPRODUCT(ROUND(A1:A3,2),ROUND(B1:B3,2)),2 ) The first formula returns the result you posted. Biff -- driller2 |
need sumproduct
[$ 1.233 ] [ 33.22 ]
[$ 0.245 ] [ 11.21 ] [$ 0.122 ] [ -3.215 ] Format column C as NUMBER set to 15 decimal places: =A1*B1 =A2*B2 =A3*B3 =SUM(C1:C3) Result = 43.314480000000000 =ROUND(SUMPRODUCT(A1:A3,B1:B3),2) Result = 43.31 Biff "T. Valko" wrote in message ... See this: http://cpearson.com/excel/rounding.htm Biff "driller2" wrote in message ... Still unknown, thanks for your suggestion T.Valko, From below new sample (e.g) i had tried the three(3) suggested formula. [col A] [col B] [rounded product A*B] [$ 1.233 ] [ 33.22 ] [ $ 40.96 ] [$ 0.245 ] [ 11.21 ] [ $ 2.75 ] [$ 0.122 ] [ -3.215 ] [ $ -0.39 ] from the above sample the sum of the product (2 decimals) = $43.32 (in thousands...) =ROUND(SUMPRODUCT(A1:A3,B1:B3),2) result is [$43.31 < $43.32] =SUMPRODUCT(ROUND(A1:A3,2),ROUND(B1:B3,2)) result is [$43.2767 < $43.32] =ROUND(SUMPRODUCT(ROUND(A1:A3,2),ROUND(B1:B3,2)),2 ) result is [$43.28 < $43.32] ___ since last week, this is still a mystery for me where to find a single formula to do this job, perfectly. I do tried Roundup and Roundown, even, but the perfect result varies as the data changes ___ Hope anyone else can give a single sumproduct formula or other evolved formula. happy holidays hohoho...:) romelsb 4pinoy :) on banter T. Valko Wrote: It depends on what you actually want. Try these based on your example and note the difference: =ROUND(SUMPRODUCT(A1:A3,B1:B3),2) =SUMPRODUCT(ROUND(A1:A3,2),ROUND(B1:B3,2)) =ROUND(SUMPRODUCT(ROUND(A1:A3,2),ROUND(B1:B3,2)),2 ) The first formula returns the result you posted. Biff -- driller2 |
I read the link you gave, yet I cant see its relation to my request for a perfect single formula.
(e.g) when you see some invoice or pay receipts, the product from two factors are rounded off to 2 decimals. Such rounded product are summed below for the total. Such total is considered correct and is not disputed by visual check of accountants. Now, the single formula I need is to do the work of totalling the rounded product, [ not to round the sum of the virgin products ] I hope I make it clear that this question is not to lead any dispute on round formula may-be errors on few decimals of factors. thanks for the reply happy holidays hohoho driller romelsb 4pinoy:) on banter Quote:
|
All times are GMT +1. The time now is 02:25 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com