Collect specific values In addition to a specified percentage

[luis gaspper]

Hi everyone,
I need some help with the following problem:
I have UDF Function to multiply the value of column C by the specified percentage for each case.
but I'm having a problem in add a specific amount for each case In addition to the percentage.
I have highlighted the specific amounts for each case with the expected result ... Please have a look ... Thanks in advance.

[HansV]

Your workbook doesn't explain how to calculate the amounts to add.

[luis gaspper]

I am so sorry Mr. Hans for being late in reply.
What I am trying to do is add a specific amount for each case In addition to the percentage allocated to each case
As for how to calculate the amounts, It is the same way.. I have highlighted the specific amounts for each case From within the codes editor

Code: Select all

            End If
            Select Case Condition2
                Case "one"
                    Factor3 = 3.75     ' + 1000
                Case "two"
                    Factor3 = 4.75    ' + 2000
                Case "three"
                    Factor3 = 5.75     ' + 3000
            End Select
            Percentages = Percentages * Factor3
        Case Else
            Percentages = ""
    End Select
End Function

I can provide additional clarification if needed ...Thank you very much in advance.

[HansV]

The following version produces the desired results for the green cells, but not the others. I don't understand how you want to calculate those.

Code: Select all

Function Percentages(ByVal Condition1, ByVal Condition2, ByVal TheVal, ByVal EvacDate) As Variant
    Dim StartDate As Date
    Dim AfterDate As Date
    Dim EndDate As Date
    Dim Factor1 As Double
    Dim Factor2 As Double
    Dim Factor3 As Double
    Dim Add3 As Double
    StartDate = DateSerial(Year(Date), Month(Date), 1)
    EndDate = DateSerial(Year(Date), Month(Date) + 1, 0)
    Condition2 = LCase(Condition2)
    Select Case Condition2
        Case "one", "two", "three"    ' .................
            Condition1 = LCase(Condition1)
            If Condition1 = "cond1" Then
                Percentages = TheVal
            Else
                If Condition1 = "cond2" Then
                    Factor1 = EvacDate - StartDate
                ElseIf Condition1 = "cond3" Then
                    Factor1 = EvacDate - StartDate + 1
                ElseIf Condition1 Like "case*" Then
                    Factor1 = Right(Condition1, 2) / 100
                    If EvacDate <> "" Then
                        Factor1 = Factor1 * (EvacDate - StartDate + 1)
                    End If
                Else
                    AfterDate = EvacDate + 1
                    Factor1 = Right(Condition1, 2) / 100
                    Factor1 = EndDate - AfterDate + 1 + (EvacDate - StartDate + 1) * Factor1
                End If
                If EvacDate = "" Then
                    Factor2 = 1
                Else
                    Factor2 = EndDate - StartDate + 1
                End If
                Percentages = TheVal * Factor1 / Factor2
            End If
            Select Case Condition2
                Case "one"
                    Factor3 = 3.75
                    Add3 = 1000
                Case "two"
                    Factor3 = 4.75
                    Add3 = 2000
                Case "three"
                    Factor3 = 5.75
                    Add3 = 3000
            End Select
            Percentages = Percentages * Factor3 + Add3
        Case Else
            Percentages = ""
    End Select
End Function

[luis gaspper]

Dear Hans,
I'm sorry for taking you so much time... And, of course, thank you!
I will explain to you more with an example for each case

The first condition Is Full pay work ( Full salary ) Without registering any date in column D
584.94 * 375% + 1000 = 3193.53

The second condition is Termination of service at retirement age ( Actual duration minus one day )
584.94 * 375% / 31 * 14 + 1000 / 31 * 14 = 1442.24

The third condition is Termination of service due to death ( Actual duration )
584.94 * 375% / 31 * 15 + 1000 / 31 * 15 = 1545.26

As for case40 It means a percentage of part pay ( Partial salary ) and It has three cases as follows:-
The first case Without registering any date in column D and This means getting 40% of the output of column C multiplied by the percentage.
584.94 * 375% * 40% + 1000 * 40% = 1277.41

The second case is Termination of service at retirement age ( Actual duration minus one day )
584.94 * 375% * 40% / 31 * 14 + 1000 * 40% / 31 * 14 = 576.90

The third case is Termination of service due to death ( Actual duration )
584.94 * 375% * 40% / 31 * 15 + 1000 * 40% / 31 * 15 = 618.10

As for cut40 It means returning to work from part time to full time and In this case, there will be two periods

The first period from the beginning of the month until the date of cutting (according to its percentage)
and The second period from the next day Until the end of the month ( with full pay )
( 584.94 * 375% * 40% + 1000 * 40% / 31 * 15 ) + ( 584.94 * 375% + 1000 / 31 * 16 ) = 2266.37

How can modify this function to include all the above cases ? ... Thanks again for your support

[HansV]

There was absolutely no way I could have derived this from your original description. In the future, please provide sufficient information at the beginning instead of afterwards.

For case40, how can I distinguish between the second and third case?

Your sample workbook does not have cut40. Is it the same as Hall40?

[luis gaspper]

Dear Hans,
You have the right to it, .. So please accept my apologies.
The distinction between all cases is The date of the evacuation Or the cutting date
Note:- the number 40 or any number that follows the word ( cut or part ) is a percentage
Hereby I attached an Excel file that almost matches to my actual file... For more clarification ... please refer my attach file
Your help will be really appreciated. Thanks in advance.

[HansV]

For part40, there are three situations.

S0039.png

You still haven't explained how we can distinguish between the second and third situation - they look exactly the same.

[luis gaspper]

Dear Hans,
You can distinguish between them by adding Fourth case with changing the case name to ( partrefe40 As a special case) As shown in the picture.
Appreciate your help.

[HansV]

It has been very frustrating that you provided incomplete information at every step. Please try to do better in the future. Here is a new version of the function:

Code: Select all

Function Percentages(ByVal Condition1, ByVal Condition2, ByVal TheVal, ByVal EvacDate) As Variant
    Dim StartDate As Date
    Dim EndDate As Date
    Dim Period1 As Long
    Dim Period2 As Long
    Dim MonthDays As Long
    Dim Factor As Double
    Dim Add As Double
    Dim Perc As Double

    If Condition2 = "" Then
        Percentages = ""
        Exit Function
    End If

    StartDate = DateSerial(Year(Date), Month(Date), 1)
    EndDate = DateSerial(Year(Date), Month(Date) + 1, 0)
    MonthDays = EndDate - StartDate + 1

    Condition2 = LCase(Condition2)
    Select Case Condition2
        Case "one"
            Factor = 3.75
            Add = 1000
        Case "two"
            Factor = 4.75
            Add = 2000
        Case "three"
            Factor = 5.75
            Add = 3000
    End Select

    Condition1 = LCase(Condition1)
    Select Case True
        Case Condition1 = "on the job"
            Perc = 1
            Period1 = MonthDays
        Case Condition1 = "ending service"
            Perc = 1
            Period1 = EvacDate - StartDate
        Case Condition1 = "death"
            Perc = 1
            Period1 = EvacDate - StartDate + 1
        Case Condition1 Like "part*"
            Perc = Right(Condition1, 2) / 100
            If EvacDate = "" Then
                Period1 = MonthDays
            ElseIf Condition1 Like "partrefe*" Then
                Period1 = EvacDate - StartDate
            Else
                Period1 = EvacDate - StartDate + 1
            End If
        Case Condition1 Like "cut*"
            Perc = Right(Condition1, 2) / 100
            Period1 = EvacDate - StartDate + 1
            Period2 = EndDate - EvacDate
    End Select

    Percentages = (TheVal * Factor + Add) * Perc * Period1 / MonthDays + _
        (TheVal * Factor + Add) * Period2 / MonthDays
End Function

[luis gaspper]

Now it is perfect and awesome .. Thank you very much Mr. Hans
Many thanks and kind regards

[luis gaspper]

Sorry for disturbing you again Mr. Hans
Column 2 contains some names like one, two, three
How can make the output blank instead of zeros If the same column contains other names?
Thanks again.

[HansV]

Change

Code: Select all

    Select Case Condition2
        Case "one"
            Factor = 3.75
            Add = 1000
        Case "two"
            Factor = 4.75
            Add = 2000
        Case "three"
            Factor = 5.75
            Add = 3000
    End Select

to

Code: Select all

    Condition2 = LCase(Condition2)
    Select Case Condition2
        Case "one"
            Factor = 3.75
            Add = 1000
        Case "two"
            Factor = 4.75
            Add = 2000
        Case "three"
            Factor = 5.75
            Add = 3000
        Case Else
            Percentages = ""
            Exit Function
    End Select

[luis gaspper]

Thanks a lot Mr. Hans for your great support all the time
please I want adjust rounding value Using the function int
Again, thanks so much