Collect different percentages

luis gaspper
StarLounger
Posts: 60
Joined: 03 Aug 2020, 05:23

Collect different percentages

Post by luis gaspper »

Hi Everyone
I am having a simple problem In this part Of the following UDF

Code: Select all

    ElseIf bCond2OK Then
        Select Case LCase(Condition2)
            Case "acceptable"                ' ******************
                Full = 0.4667 * TheValue + TheValue2 + Bonus2
            ' I want to add on the desired result multiply value of column C by 475% Plus multiply the value of column D by 105.73%
            Case Else
                Exit Function
        End Select
I want to add on the desired result for the case ( acceptable ) multiply value of column C by 475% Plus multiply the value of column D by 105.73%.
This is what I've come up with so far, Please see the attachment file ...Thanks in advance
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 69883
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Collect different percentages

Post by HansV »

Use

Code: Select all

Function myUDF3(Condition1 As String, Condition2 As String, TheValue As Double, TheValue2 As Double, TheValue3 As Double) As Variant
    Dim FactorPercent As Double
    Dim Full As Double, Temp As Double
    Dim bCond1OK As Boolean, bCond2OK As Boolean

    myUDF3 = ""

    bCond1OK = False
    Condition1 = LCase(Trim(Condition1))
    If Len(Condition1) = 0 Then
        bCond1OK = True
    Else
        Select Case Condition1
            Case "very good"
                bCond1OK = True
        End Select
    End If

    bCond2OK = False
    Condition2 = LCase(Trim(Condition2))
    If Len(Condition2) = 0 Then
        bCond2OK = True
    Else
        Select Case Condition2
            Case "acceptable"           ' ******************
                bCond2OK = True
        End Select
    End If

    If bCond1OK Then
        Select Case LCase(Condition1)
            Case "very good"
                Full = 3.805 * TheValue + Bonus
            Case Else
                Exit Function
        End Select
    ElseIf bCond2OK Then
        Select Case LCase(Condition2)
            Case "acceptable"                ' ******************
                Full = 0.4667 * TheValue + TheValue3 + Bonus2
            ' I want to add on the desired result multiply value of column C by 475% Plus multiply the value of column D by 105.73%
                Full = Full + (TheValue * 4.75 + TheValue2) * 1.0573
            Case Else
                Exit Function
        End Select
    Else
        Exit Function
    End If
    FactorPercent = 1#
    Temp = FactorPercent * Full
    myUDF3 = Application.WorksheetFunction.Round(Temp, 2)
End Function
and change the formula in F2 to

=myudf3(A2,B2,C2,D2,E2)

then fill down.
Regards,
Hans

luis gaspper
StarLounger
Posts: 60
Joined: 03 Aug 2020, 05:23

Re: Collect different percentages

Post by luis gaspper »

Thank you Hans, it works perfectly