Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
thanks again and again
thanks to excel forum...
As myMilestone for myParticipation in this forum, i have derived [without knowing VB] a certain UDF for those who like to have a direct function on getting the rounded products of multiple factors, maybe accountants or auditors may need it by using excel program.... sum of factored [virgin or rounded] product :[positive or negative or both] this is dedicated to Bob Phillips, Jerry Lewis and Gary''s Student. who helped me sort it out. -------- Function mySPX(test, dec As Long, ParamArray rng()) Dim sConditions As String Dim sRanges As String Dim i As Long sConditions = "--(" & rng(0).Address(False, False, , False) sRanges = "ROUND(" & rng(0).Address(False, False, , False) For i = LBound(rng) + 1 To UBound(rng) sConditions = sConditions & "*" & rng(i).Address(False, False, , False) sRanges = sRanges & "*" & rng(i).Address(False, False, , False) Next i If test = "POS" Then mySPX = Evaluate("Sum(" & sConditions & "0)*" & sRanges & "," & dec & "))") ElseIf test = "NEG" Then mySPX = Evaluate("Sum(" & sConditions & "<0)*" & sRanges & "," & dec & "))") ElseIf test = "ALL" Then mySPX = Evaluate("Sum(" & sRanges & "," & dec & "))") End If End Function ------------------ '=mySPX("POS",2,L1:L3,M1:M3,N1:N3,,,,) 'for sum of negative products '=mySPX("NEG",2,L1:L3,M1:M3,N1:N3,,,,) 'for sum of positive products '=mySPX("ALL",2,L1:L3,M1:M3,N1:N3,,,,) 'for sum of both ------------------------- hope to collect/achieve more, driller |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|