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

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

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

[$ 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