ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   any more criteria condition for mySP UDF, if possible (https://www.excelbanter.com/excel-programming/380186-re-any-more-criteria-condition-mysp-udf-if-possible.html)

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

Bob Phillips
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

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

Bob Phillips
any more criteria condition for mySP UDF, if possible
 
And I just debugged it, all three ranges are being evaluated!

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


All times are GMT +1. The time now is 09:24 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com