any more criteria condition for mySP UDF, if possible
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
|