Create User Defined Function from excel sheet calculation

[Sceppi]

Hi,


I would like to create a User Defined Function (UDF) from a complex calculation in an excel sheet.
Basically I have 5 cells which are calculated like this: “=IF(IF(($E$2-B5)<(B6-B5);($E$2-B5)*C6;(B6-B5)*C6)<0;0;IF(($E$2-B5)<(B6-B5);($E$2-B5)*C6;(B6-B5)*C6))”
Whereby $E$2=Input value
The content of the formula is not important, just to illustrate that it is a complex function

And then the output result is the sum of these 5 cells with IF functions


Now I would like to have something like this: customfunction(Input value)=output value
- I could translate this complex formula entirely in VBA and create a UDF from there directly... but this is rather cumbersome. Furthermore, I'm not a VBA expert (I just now the basics).
- Better would be if I could create a UDF directly from this excel sheet. So I just tell excel to put the (input) in cell $E$2 and then return me the output value of the calculation


Is there a way to achieve this?


Thx

 

[mse330]

First welcome to the forums. Second, if you create a UDF function in VBA you can use it in your excel file later on like any other default functions. If you can give us a little more explanation of what you are trying to achieve, we can help you with the necessary function

 

[mse330]

Came across your other thread in the below link which I think is redundant & you could've just post the explanation in the same thread. Anyway, you can use the below code & directly type =Tariff(50000) in your spreadsheet & it will calculate the amount for you ... Give it a shot & let me know if this is what you are looking for

Link to your other thread : Create User Defined Function from excel sheet calculation

Function Tariff(Amount As Double) As Double

If Amount <= 10860 Then
    Tariff = Amount * 0.25
    Exit Function

ElseIf Amount <= 12470 Then
    Tariff = 10860 * 0.25
    Tariff = Tariff + (Amount - 10860) * 0.3
    Exit Function

ElseIf Amount <= 20780 Then
    Tariff = 10860 * 0.25
    Tariff = Tariff + (12470 - 10860) * 0.3
    Tariff = Tariff + (Amount - 12470) * 0.4
    Exit Function

ElseIf Amount <= 38080 Then
    Tariff = 10860 * 0.25
    Tariff = Tariff + (12470 - 10860) * 0.3
    Tariff = Tariff + (20780 - 12470) * 0.4
    Tariff = Tariff + (Amount - 20780) * 0.45
    Exit Function

ElseIf Amount <= 999999999 Then
    Tariff = 10860 * 0.25
    Tariff = Tariff + (12470 - 10860) * 0.3
    Tariff = Tariff + (20780 - 12470) * 0.4
    Tariff = Tariff + (38080 - 20780) * 0.45
    Tariff = Tariff + (Amount - 38080) * 0.5
    Exit Function
End If

End Function

 

[mse330]

Another option, perhaps shorter code would be

Function Tariff2(Amount As Double) As Double

Select Case Amount
    Case Is <= 10860
        Tariff2 = Amount * 0.25
    Case Is <= 12470
        Tariff2 = 10860 * 0.25
        Tariff2 = Tariff2 + (Amount - 10860) * 0.3
    Case Is <= 20780
        Tariff2 = 10860 * 0.25
        Tariff2 = Tariff2 + (12470 - 10860) * 0.3
        Tariff2 = Tariff2 + (Amount - 12470) * 0.4
    Case Is <= 38080
        Tariff2 = 10860 * 0.25
        Tariff2 = Tariff2 + (12470 - 10860) * 0.3
        Tariff2 = Tariff2 + (20780 - 12470) * 0.4
        Tariff2 = Tariff2 + (Amount - 20780) * 0.45
    Case Else
        Tariff2 = 10860 * 0.25
        Tariff2 = Tariff2 + (12470 - 10860) * 0.3
        Tariff2 = Tariff2 + (20780 - 12470) * 0.4
        Tariff2 = Tariff2 + (38080 - 20780) * 0.45
        Tariff2 = Tariff2 + (Amount - 38080) * 0.5
End Select

End Function

Although both should achieve the same results