PDA

View Full Version : Make custom formula with VBA's



kiisaaa
10-14-2016, 01:55 AM
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
10-14-2016, 05:49 AM
The easiest way would be to have a series of column pairs, one for each distribution, use the correct one in a VLookup

17318

kiisaaa
10-14-2016, 05:56 AM
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
10-14-2016, 07:34 AM
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
10-14-2016, 07:42 AM
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
10-17-2016, 12:13 AM
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
10-17-2016, 07:39 AM
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
10-18-2016, 12:48 AM
Thank you so much!! This is EXACTLY what I was looking for!!
You are a lifesaver :)

kiisaaa
10-18-2016, 01:07 AM
Thank you so much!! This is EXACTLY what I was looking for!!
You are a lifesaver :)

snb
10-18-2016, 02:52 AM
A 'Normal' formula suffices:


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

kiisaaa
10-25-2016, 01:27 AM
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
17400


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
10-25-2016, 07:08 AM
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