| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Condition Formats for duplicates with additional criteria | Excel Worksheet Functions | |||
| Count Unique with criteria condition | Excel Worksheet Functions | |||
| Using font elements as condition criteria | Excel Worksheet Functions | |||
| if statements with AND or OR condition criteria | Excel Programming | |||
| Assign ID based on condition(criteria) | Excel Programming | |||
Linear Mode
