You must have. All of my testing has been on 3 ranges. The ParamArray allows
a variable amount, so it will take 4, 5, 6, etc.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"dribler2" wrote in message
...
sorry for the change of tack,

i run the function and it works for "+","-", ""------------
yet it only accepts 2 range of factors.
=mysp("+",2,L1:L3,M1:M3) only
i could have missed something..

TFH
dribler2

"Bob Phillips" wrote:

You didn't say that!

Change of tack.

Function mySP(test, dec As Long, ParamArray rng())
Dim sConditions As String
Dim sRanges As String
Dim i As Long

sConditions = "--(" & rng(0).Address(, , , True)
sRanges = "ROUND(" & rng(0).Address(, , , True)
For i = LBound(rng) + 1 To UBound(rng)
sConditions = sConditions & "*" & rng(i).Address(, , , True)
sRanges = sRanges & "*" & rng(i).Address(, , , True)
Next i
If test = "+" Then
mySP = Evaluate("=SumProduct(" & sConditions & "0)," & _
sRanges & "," & dec & "))")
ElseIf test = "-" Then
mySP = Evaluate("=SumProduct(" & sConditions & "<0)," & _
sRanges & "," & dec & "))")
Else
mySP = Evaluate("=SumProduct(" & sRanges & "," & dec & "))")
End If
End Function

Call like

=mysp("-",2,L1:L3,M1:M3,N1:N3)

for negatives

=mysp("+",2,L1:L3,M1:M3,N1:N3)

for positives

=mysp("",2,L1:L3,M1:M3,N1:N3)

for both

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"dribler2" wrote in message
...
Bob,

what shall be the formula syntax if i want the sum as one for both
positive
and negative rounded products ?

call like

=mysp("?",2,L1:L3,M1:M3,N1:N3) <<<<<<<<<<<<<<
for negatives + positive

=mysp(FALSE,2,L1:L3,M1:M3,N1:N3)
for negatives

=mysp(TRUE,2,L1:L3,M1:M3,N1:N3)
for positives

TFH
dribler2

"Bob Phillips" wrote:

Function mySP(positive As Boolean, dec As Long, ParamArray rng())
Dim sConditions As String
Dim sRanges As String
Dim i As Long

sConditions = "--(" & rng(0).Address(, , , True)
sRanges = "),ROUND(" & rng(0).Address(, , , True)
For i = LBound(rng) + 1 To UBound(rng)
sConditions = sConditions & "*" & rng(i).Address(, , , True)
sRanges = sRanges & "*" & rng(i).Address(, , , True)
Next i
mySP = Evaluate("=SumProduct(" & sConditions & sRanges & _
IIf(positive, "0", "<0") & "," & dec & "))")
End Function

call like

=mysp(FALSE,2,L1:L3,M1:M3,N1:N3)

for negatives

=mysp(TRUE,2,L1:L3,M1:M3,N1:N3)

for positives

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"dribler2" wrote in message
...
i am amazed with the huge help of the UDF below,
yet i am still lacking some very minor touch of possible calculating
conditions.
like,
I need to do something like this
=mySP(+,2, A1:A100,B1:B100,C1:K100) only to provide the sum of
positive
rounded products
or
=mySP(-,2, A1:A100,B1:B100,C1:K100) only to provide the sum of
negative
rounded products

this may be magic but i hope excel VBA is possible.

Below is the checked result from my previous post
'Use like =mySP(2, A1:A100,B1:B100,C1:K100)

Function mySPR(dec As Long, ParamArray rng())
Dim sRanges As String
Dim i As Long

For i = LBound(rng) To UBound(rng)
sRanges = sRanges & rng(i).Address(, , , True) & "*"
Next i
mySPR = Evaluate("=SumProduct(Round(" & _
Left(sRanges, Len(sRanges) - 1) & "," & dec & "))")
End Function

TFH "thanks for help" happy holidays
dribler2