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
|