Make custom formula with VBA's

[kiisaaa]

Hi,
I dont really know VBAs that well. I only know how to copy the codes I find online and do mild adjustments on them.

One of my colleagues wants me to make him a custom code in Excel where he's able to insert his own inputs to get a sum of costs. We make many special deals with our partners and pay them commission based on number of attendants.

One partner might have this following deal:
6-9 customers - 6 500
10-19 customers - 10 000
20-30 customers - 15 000

Another one might have
5-8 customers - 3 000
9-13 customers - 10 000
etc.

These costs are negotiated and really depends on all sort of factors (so it won't work with a lookup table -> match index formula)

Is there possible to make a formula where you use input:
range of first step, cost first step, range of second step, cost of second step, range of third step, cost of thrid step, range of fourth step, cost of fourth step. ?

I have a feeling that it should be a very simple formula to make, but I really don't know how to code it myself..

Thanks in advance!

[Paul_Hossler]

The easiest way would be to have a series of column pairs, one for each distribution, use the correct one in a VLookup

Capture.JPG

[kiisaaa]

Thanks for the reply,
the only problem is that every contract is customized with the time intervals and the cost structures (and we have tons of contracts!) - so it would be much better to have it in a VBA formula(?)

[snb]

that every contract is customized with the time intervals and the cost structures

That seems to be new information.
What does it mean ?

[Paul_Hossler]

the only problem is that every contract is customized with the time intervals and the cost structures (and we have tons of contracts!) - so it would be much better to have it in a VBA formula(?)

Well, you didn't mention the time phasing.

But the data table concept is the same, but more complicated, unless you really wanted it in VBA

A VBA User Defined Formula (UDF) would be the most user friendly probably

Completely define your requirements first, and maybe make up a demo workbook to post here

[kiisaaa]

Well, you didn't mention the time phasing.

But the data table concept is the same, but more complicated, unless you really wanted it in VBA

A VBA User Defined Formula (UDF) would be the most user friendly probably

Completely define your requirements first, and maybe make up a demo workbook to post here

Ok, sorry bad phrasing.

Everytime we make a contract, we make a custom commission deal, but always in a "stair - system", like the ones written in the first post. My colleague just wants a simple UDF where he can just write the inputs and it calculates the costs based on the number of attendants (which are reported after the project is done).

In one year we more than 250 different contracts, so I do believe that a lookup table won't work since all of the commissions are different based on the contract.

The wish here is to get a formula where we can write =Commission(3;5;5000;9;9000;19;12000;35;15000;cell)
If we get less than 3 attendants, the contract is canceled.

3-5 attendants - 5000 in commission
6-9 attendants - 9000 in commission
10-19 attendants - 12000 in commission
20-35 attendants - 15000 in commission.

=Commission(4;6;6000;10;10000;18;15000;35;20000;cell)
4-6 attendants - 6000 in commission
7-10 attendants - 10000 in commission
11-18 attendants - 15000 in commission
19-35 attendants - 20000 in commission

Then link this to "cell" reporting the number of attendants on this course, returning the cost corresponding to the number of attendants. We make these contracts several months before we know the number of attendants, thats why it's easier to just fill in an UDF whenever the contract is signed, and just fill in the cell with number of attendants so we know the size of the commission in the project.

I hope this was more clear? :-)

[Paul_Hossler]

The first one is what you asked for, but I'd use the second version since it's less finicky about the input

Both are in the attachment

Option Explicit
Function Commission(NumAttendants As Long, Steps As String) As Double
    Dim v As Variant
    Dim i As Long
    
    v = Split(Steps, ";")
    For i = UBound(v) - 1 To LBound(v) Step -2
        If NumAttendants >= v(i) Then
            Commission = v(i + 1)
            Exit Function
        End If
    Next i
  
End Function
 
Function Commission1(NumAttendants As Long, ParamArray Steps()) As Double
    Dim i As Long
    
    For i = UBound(Steps) - 1 To LBound(Steps) Step -2
        If NumAttendants >= Steps(i) Then
            Commission1 = Steps(i + 1)
            Exit Function
        End If
    Next i
  
End Function

[kiisaaa]

Thank you so much!! This is EXACTLY what I was looking for!!
You are a lifesaver

[kiisaaa]

Thank you so much!! This is EXACTLY what I was looking for!!
You are a lifesaver

[snb]

A 'Normal' formula suffices:

=CHOOSE(MATCH(A5;{3;5;9;19;35};1);0;5000;9000;12000;15000)

[kiisaaa]

The first one is what you asked for, but I'd use the second version since it's less finicky about the input

Both are in the attachment

Option Explicit
Function Commission(NumAttendants As Long, Steps As String) As Double
    Dim v As Variant
    Dim i As Long
    
    v = Split(Steps, ";")
    For i = UBound(v) - 1 To LBound(v) Step -2
        If NumAttendants >= v(i) Then
            Commission = v(i + 1)
            Exit Function
        End If
    Next i
  
End Function
 
Function Commission1(NumAttendants As Long, ParamArray Steps()) As Double
    Dim i As Long
    
    For i = UBound(Steps) - 1 To LBound(Steps) Step -2
        If NumAttendants >= Steps(i) Then
            Commission1 = Steps(i + 1)
            Exit Function
        End If
    Next i
  
End Function

Hi!
The VBA just suddenly got a critical error in this line when I am opening the file, and I have no clue on why that is.
Function Commission1(NumAttendants As Long, ParamArray Steps()) As Double
Unavngivet.jpg


I could remove it and change all the formulas to comission instead of comission1. But since Comission needs " " before and after the inputs, I can't use the regular 'find and replace'..

It worked fine until yesterday

Any tips/solutions to why this keeps happening :-) ?

[Paul_Hossler]

Close Excel
Restart Computer
Open Excel
Try Again


If it still occurs, post your workbook

Automation Errors are usually something environment related, as opposed to VBA errors