Function mySP(rng1 As Range, rng2 As Range, Optional dec As Long = 2)
mySP = Application.Caller.Parent.Evaluate("SUMPRODUCT(ROU ND(A1:A3*B1:B3," &
dec & "))")
End Function


=mySP(A1:A3,B1:B3,1)

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"dribler2" 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 UDformula.

e.g.

[col A] [col B] [rounded product A*B in 2 decimals]

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

i hope that the UDF, if available, can be instructed for any user desired
number of decimals.

can anyone help me out with this.

any of your suggestion is requested.

Have you tried it?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"dribler2" wrote in message
...
hello,

from my previous unsolved post "need sumproduct", please try to evaluate
this basic scenario

Still unknown,

From below new sample (e.g) i had tried the three(3) suggested formula.

[col A] [col B] [(An*Bn) = rounded product in 2decimals]

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

(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 your helping reply

You might try placing your cursor just to the right of the & on the first
line. Then carefully touch the backspace key.
--
Don Guillett
SalesAid Software

"dribler2" wrote in message
...
Bob,

in VB, this line goes in red...i'm not good at correcting this..

mySP = Application.Caller.Parent.Evaluate("SUMPRODUCT(ROU ND(A1:A3*B1:B3,"
&
dec & "))")

thanks


"Bob Phillips" wrote:

Have you tried it?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"dribler2" wrote in message
...
hello,

from my previous unsolved post "need sumproduct", please try to
evaluate
this basic scenario

Still unknown,

From below new sample (e.g) i had tried the three(3) suggested formula.

[col A] [col B] [(An*Bn) = rounded product in 2decimals]

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

(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 your helping reply

The newsgroup reader has subverted it onto 2 lines, it should just be one.

Try this variation of that line

mySP = Application.Caller.Parent.Evaluate("SUMPRODUCT(" & _
"ROUND(A1:A3*B1:B3," &dec & "))")

this REALLY is 2 lines.


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"dribler2" wrote in message
...
Bob,

in VB, this line goes in red...i'm not good at correcting this..

mySP = Application.Caller.Parent.Evaluate("SUMPRODUCT(ROU ND(A1:A3*B1:B3,"
&
dec & "))")

thanks


"Bob Phillips" wrote:

Have you tried it?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"dribler2" wrote in message
...
hello,

from my previous unsolved post "need sumproduct", please try to
evaluate
this basic scenario

Still unknown,

From below new sample (e.g) i had tried the three(3) suggested formula.

[col A] [col B] [(An*Bn) = rounded product in 2decimals]

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

(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 your helping reply

Thanks,
i tried it and the color red was gone, yet when i try to place it in a large
sheet, where the column data varies, the sumproduct was stock to the first
three data factors only...Maybe you can make it on a dynamic variable
range - for magic's sake.

waiting for your instruction.
dribler2

"Bob Phillips" wrote:

The newsgroup reader has subverted it onto 2 lines, it should just be one.

Try this variation of that line

mySP = Application.Caller.Parent.Evaluate("SUMPRODUCT(" & _
"ROUND(A1:A3*B1:B3," &dec & "))")

this REALLY is 2 lines.


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"dribler2" wrote in message
...
Bob,

in VB, this line goes in red...i'm not good at correcting this..

mySP = Application.Caller.Parent.Evaluate("SUMPRODUCT(ROU ND(A1:A3*B1:B3,"
&
dec & "))")

thanks


"Bob Phillips" wrote:

Have you tried it?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"dribler2" wrote in message
...
hello,

from my previous unsolved post "need sumproduct", please try to
evaluate
this basic scenario

Still unknown,

From below new sample (e.g) i had tried the three(3) suggested formula.

[col A] [col B] [(An*Bn) = rounded product in 2decimals]

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

(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 your helping reply

It already is dynamic, it uses two ranges, which you van define to any
cells, as long as they are the same size.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"dribler2" wrote in message
...
Thanks,
i tried it and the color red was gone, yet when i try to place it in a
large
sheet, where the column data varies, the sumproduct was stock to the first
three data factors only...Maybe you can make it on a dynamic variable
range - for magic's sake.

waiting for your instruction.
dribler2

"Bob Phillips" wrote:

The newsgroup reader has subverted it onto 2 lines, it should just be
one.

Try this variation of that line

mySP = Application.Caller.Parent.Evaluate("SUMPRODUCT(" & _
"ROUND(A1:A3*B1:B3," &dec & "))")

this REALLY is 2 lines.


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"dribler2" wrote in message
...
Bob,

in VB, this line goes in red...i'm not good at correcting this..

mySP =
Application.Caller.Parent.Evaluate("SUMPRODUCT(ROU ND(A1:A3*B1:B3,"
&
dec & "))")

thanks


"Bob Phillips" wrote:

Have you tried it?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"dribler2" wrote in message
...
hello,

from my previous unsolved post "need sumproduct", please try to
evaluate
this basic scenario

Still unknown,

From below new sample (e.g) i had tried the three(3) suggested
formula.

[col A] [col B] [(An*Bn) = rounded product in 2decimals]

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

(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 your helping reply

I am not understanding what you are asking.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"dribler2" wrote in message
...
Bob,
We are almost there,
Yet, is there no way to write the UDfunction to allow a varying range of 2
factor that can be from 10 to 65000

eg. =mySP(A1:A15000*A16000:A31000, -3 to 3 )

please advice..

thanks
dribler2

"Bob Phillips" wrote:

It already is dynamic, it uses two ranges, which you van define to any
cells, as long as they are the same size.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"dribler2" wrote in message
...
Thanks,
i tried it and the color red was gone, yet when i try to place it in a
large
sheet, where the column data varies, the sumproduct was stock to the
first
three data factors only...Maybe you can make it on a dynamic variable
range - for magic's sake.

waiting for your instruction.
dribler2

"Bob Phillips" wrote:

The newsgroup reader has subverted it onto 2 lines, it should just be
one.

Try this variation of that line

mySP = Application.Caller.Parent.Evaluate("SUMPRODUCT(" & _
"ROUND(A1:A3*B1:B3," &dec & "))")

this REALLY is 2 lines.


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"dribler2" wrote in message
...
Bob,

in VB, this line goes in red...i'm not good at correcting this..

mySP =
Application.Caller.Parent.Evaluate("SUMPRODUCT(ROU ND(A1:A3*B1:B3,"
&
dec & "))")

thanks


"Bob Phillips" wrote:

Have you tried it?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"dribler2" wrote in message
...
hello,

from my previous unsolved post "need sumproduct", please try to
evaluate
this basic scenario

Still unknown,

From below new sample (e.g) i had tried the three(3) suggested
formula.

[col A] [col B] [(An*Bn) = rounded product in 2decimals]

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

(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 your helping reply

Hello,

Try
Function mySP(rng1 As Range, rng2 As Range, _
Optional dec As Long = 2)
mySP = Application.Caller.Parent.Evaluate("SUMPRODUCT(ROU ND(" _
& rng1.Address & "*" & rng2.Address & "," & dec & "))")
End Function

HTH,
Bernd

Sorry, my bad. I declared the function as dynamic, but didn't change the
actual code. Also error checking wouldn't go amiss.

I should have written

Function mySP(rng1 As Range, rng2 As Range, Optional dec As Long = 2)
If rng1.Cells.Count < rng2.Cells.Count Then
mySP = CVErr(xlErrRef)
Exit Function
End If
mySP = Application.Caller.Parent.Evaluate( _
"SUMPRODUCT(ROUND(" & rng1.Address & "*" & _
rng2.Address & "," & dec & "))")
End Function


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"dribler2" wrote in message
...
Bob P.,

I have tried the UDF "mySP" which works after test when I place the
factors
on A1:B3, yet i need to use your UDF for all my workbooks and for a
varying
number of factors and decimals - like a generic/productive formula.
Your UDF
'-------------
Function mySP(rng1 As Range, rng2 As Range, Optional dec As Long = 2)
mySP = Application.Caller.Parent.Evaluate("SUMPRODUCT(ROU ND(A1:A3*B1:B3,"
&
dec & "))")
End Function
'-------------

'i try to guess from below a generic UDF, surely its not correct because
when I use it in the sheet, #VALUE! error appears..

Function myXP(rng1 As Range, rng2 As Range, Optional dec As Long = 2)
mySP = Application.Caller.Parent.Evaluate("SumProduct(Rou nd(" & rng1 *
rng2,
dec & "))")
End Function

'-------------

i hope you were understanding mine problem.

thanks for anymore suggestion...happy holidays
dribler2

"Bob Phillips" wrote:

I am not understanding what you are asking.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"dribler2" wrote in message
...
Bob,
We are almost there,
Yet, is there no way to write the UDfunction to allow a varying range
of 2
factor that can be from 10 to 65000

eg. =mySP(A1:A15000*A16000:A31000, -3 to 3 )

please advice..

thanks
dribler2

"Bob Phillips" wrote:

It already is dynamic, it uses two ranges, which you van define to any
cells, as long as they are the same size.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"dribler2" wrote in message
...
Thanks,
i tried it and the color red was gone, yet when i try to place it in
a
large
sheet, where the column data varies, the sumproduct was stock to the
first
three data factors only...Maybe you can make it on a dynamic
variable
range - for magic's sake.

waiting for your instruction.
dribler2

"Bob Phillips" wrote:

The newsgroup reader has subverted it onto 2 lines, it should just
be
one.

Try this variation of that line

mySP = Application.Caller.Parent.Evaluate("SUMPRODUCT(" & _
"ROUND(A1:A3*B1:B3," &dec & "))")

this REALLY is 2 lines.


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"dribler2" wrote in message
...
Bob,

in VB, this line goes in red...i'm not good at correcting this..

mySP =
Application.Caller.Parent.Evaluate("SUMPRODUCT(ROU ND(A1:A3*B1:B3,"
&
dec & "))")

thanks


"Bob Phillips" wrote:

Have you tried it?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"dribler2" wrote in message
...
hello,

from my previous unsolved post "need sumproduct", please try
to
evaluate
this basic scenario

Still unknown,

From below new sample (e.g) i had tried the three(3) suggested
formula.

[col A] [col B] [(An*Bn) = rounded product in 2decimals]

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

(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 your helping reply