need sumproduct

[driller2]

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

 

[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

 

[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

 

[T. Valko]

See this:

http://cpearson.com/excel/rounding.htm

Biff

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

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

 

[T. Valko]

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

See this:

http://cpearson.com/excel/rounding.htm

Biff

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

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

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

See this:

http://cpearson.com/excel/rounding.htm

Biff

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-

-