complex excel formula

[jonsonbero]

Hello everyone
I have the following formula in the column K to deduct the value in the column C so that the discount rate does not exceed 25% of the net salary in the column Q
The discount will start from the next month Until the amount to be deducted is over.
How can I change the formula to UDF so as to easily use it ?
Please have a look at the formula ... For the test please change the date to next month
I would be very thankful to all of you for your help... Thank you for your cooperation in advance

[Paul_Hossler]

In words or pseudo-code, what does the formula do?

[jonsonbero]

This formula for deduction the value of absence from work ... Each instalment should not exceed 25% of the net salary
The discount will start from the next month Until the amount to be deducted is over

[jonsonbero]

Is there any chance for helping me in this topic please?

[Paul_Hossler]

This formula for deduction the value of absence from work ... Each instalment should not exceed 25% of the net salary
The discount will start from the next month Until the amount to be deducted is over

That's not pseudo-code. That's a repeat of your #1

Pseudo-code is something like this

If the CURRENT MONTH(A) is >= DISCOUNT START(B) then

______if TOTAL DEDUCATIONS (C) is >= ....



______else
____________.....

______Endif


Else

_______........




End if

(had to use underscores to get the indents to show)

[jonsonbero]

Thank you Mr. Paul
I will try to explain what I am attempting to achieve.
If the total discounts in column B is less than or equal to the total in column H minus the two ranges (I:K & M:P) multiplied by 25%
give the value in column B then deduct this value starting from the next month ... If it's not .... Divide the value of column B on installments Then deduct these installments starting from the next month so that the discount rate does not exceed 25% Until the amount to be deducted is over.
Here's a sample without the formula
Sorry I didn't know a better way to describe this problem. but I hope to get help from you all ....Thank you so much.

[arnelgp]

is this what you meant.
https://www.dropbox.com/scl/fi/stwxr...t349ru3i30qhhy

[Paul_Hossler]

is this what you meant.

No

That's just the formula

That's also the third version of your formula.

It's VERY difficult and wastes time and effort if you keep changing what you'd like

Option Explicit


'=IF((ROW()-1)*$S$2*0.25>$B$2,IF($B$2-((ROW()-2)*$S$2*0.25)<0,0,$B$2-((ROW()-2)*$S$2*0.25)),((ROW()-1)*$S$2*0.25)/(ROW()-1))

Function Allowed(NetSalary As Double, TotalDeductions As Double) As Double
    Dim rowCaller As Long, rowCaller1 As Long, rowCaller2 As Long
    Dim SalaryQtr As Double
    
    rowCaller = Application.Caller.Row
    rowCaller1 = rowCaller - 1
    rowCaller2 = rowCaller - 2
    
    SalaryQtr = 0.25 * NetSalary
    
    'IF((ROW()-1)*$S$2*0.25>$B$2
    If rowCaller1 * SalaryQtr > TotalDeductions Then
    
        'IF($B$2-((ROW()-2)*$S$2*0.25)<0
        If TotalDeductions - rowCaller2 * SalaryQtr < 0# Then
            
            ',0
            Allowed = 0#
        
        Else
        
            '$B$2-((ROW()-2)*$S$2*0.25))
            Allowed = TotalDeductions - rowCaller2 * SalaryQtr
        End If
        
    Else
    
        '((ROW()-1)*$S$2*0.25)/(ROW()-1)
        Allowed = rowCaller1 * SalaryQtr / rowCaller1
        
    End If
    
End Function




'===================================================================================


'Pseudo code


'Input NetSalery (Col S) and TotalDeductions (Col b)


'Find the row the function is in = rowCaller


'If (rowCaller - 1) x NetSalery x .25 is > TotalDeductions then


'   If TotalDeductions - (rowCaller - 2) x NetSalary x .25 is < 0 Then


'       Return 0


'   Else


'       Return TotalDeductions - (rowCaller - 2) x NetSalary x .25


'   Endif


'Else


'   return ((rowCaller - 1 x NetSalary x .25) / (rowCaller -1)


'Endif

[arnelgp]

i could have made a function, but it is very simple calculation so
i opt for the formula.
yes, it might be 3rd version. better than Nothing to offer
than to peep on someones work.

[Paul_Hossler]

@arnelgp

My mistake -- I thought I was responding to the OP

[jonsonbero]

Thank you very much Mr. Paul for your time and effort ... I am really appreciating that.
May be the last point ... Attached sample may clarify more ... Thank you so much for all your assistance!

[Paul_Hossler]

The UDF only calculates Col L

The rest are regular WS formulas


Capture.JPG

Option Explicit


Function AllowedDeduction(TotalDeduction As Range, TotalDues As Range, TotalDiscounts1 As Range, TotalDiscounts2 As Range) As Double
    Dim dTotalDiscounts As Double, dNetSalary As Double, dFullDeduction As Double
    
    dTotalDiscounts = Application.WorksheetFunction.Sum(TotalDiscounts1) + Application.WorksheetFunction.Sum(TotalDiscounts2)
    dNetSalary = TotalDues.Value - dTotalDiscounts
    dFullDeduction = Round(0.25 * dNetSalary, 2)
    
    If dFullDeduction <= TotalDeduction.Value Then
        AllowedDeduction = dFullDeduction
    Else
        AllowedDeduction = TotalDeduction.Value
    End If
End Function

[jonsonbero]

Thank you Mr. Paul for your patience with me
In fact your solution is very very excellent and big step towards the ultimate aim
final clarification ... We are now in the month of August 2021
The current month's date is automatically changed using the formula in column A
How can the required values be deducted Starting from the next month automatically? .... This is the goal
Please take the test On row number 2 only ... Thanking you in advance for your insight.

[Paul_Hossler]

The current month's date is automatically changed using the formula in column A
How can the required values be deducted Starting from the next month automatically?

Not sure I understand

Use the workbook and make manual changes or provide a detailed example

[jonsonbero]

Many thanks Mr. Paul for your time again.
I don't think you don't understand me.... the example is the same
It makes sense that the rows of column A contain the date of the current month, so

1- No values should be deducted in the current month.
2- Amounts should be deducted automatically With change your computer’s date as follows:-
The first installment 1/9/2021 = 2032.02
The second installment 1/10/2021 = 2032.02
The third installment 1/11/2021 = 2032.02
The end of the last installment 1/12/2021= 452.65 ( The rest of the amount )
With the beginning of 1/1/2022 the cell output will become blank.
I think you will need to add some conditions to deal with The date in column A as a start and end of the amount to be deducted automatically
Hope I've been clear enough ... Thank you very much for your patience

[Paul_Hossler]

The UDF just calculates the Allowed Deduction for that month using Total Deductions and 7 Discount fields.

Using the UDF is part of the worksheet

Just clearing the UDF from L2 should suffice, so I don't see any need to add a date condition

But, here it is anyway


Capture.JPG

Option Explicit


Function AllowedDeduction(DataMonth As Date, TotalDeduction As Range, TotalDues As Range, TotalDiscounts1 As Range, TotalDiscounts2 As Range) As Double
    Dim dTotalDiscounts As Double, dNetSalary As Double, dFullDeduction As Double
    
    If Year(Now) = Year(DataMonth) And Month(Now) = Month(DataMonth) Then
        AllowedDeduction = 0#
        Exit Function
    End If
    
    dTotalDiscounts = Application.WorksheetFunction.Sum(TotalDiscounts1) + Application.WorksheetFunction.Sum(TotalDiscounts2)
    dNetSalary = TotalDues.Value - dTotalDiscounts
    dFullDeduction = Round(0.25 * dNetSalary, 2)
    
    If dFullDeduction <= TotalDeduction.Value Then
        AllowedDeduction = dFullDeduction
    Else
        AllowedDeduction = TotalDeduction.Value
    End If
End Function

Edit -- you may want to use this date logic instead

    If DataMonth <= DateSerial(Year(Now), Month(Now), 1) Then
        AllowedDeduction = 0#
        Exit Function
    End If

[jonsonbero]

Thanks a lot Mr. Paul for helping me in solving this issue
Best regards