Create User Defined Function from excel sheet calculation

Sceppi

New Member
Joined
Aug 4, 2017
Messages
2
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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
 
Upvote 0
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

Code:
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
 
Upvote 0
Another option, perhaps shorter code would be

Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,333
Members
452,636
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top