Formula needed

[Kevin Vidrine]

What Im trying to do i make a spreadsheet to calculate
commission for our salesman. The way it work is they get a
percentage of the revenue they bring in. For instance, if
their revenue is between $1 and $20000, they get 5.5% of
the revenue. Between 20 and 25K is 7% and so on. There is
14 different percentages that could be used depending on
what the revenue is. I want to write a formula that takes
the input which would be revenue, and multiplies it by the
correct percentage. I have tried to do this by using an if
statement but I can't get the if statement to work when
telling it if the revenue is between two numbers, such as
between 20 and 25 thousand. What other formulas will work?

 

[scott]

Maybe this would work:

In A1 type in "Revenue"
In B1(This will be used as a data entry field) type in the
revenue Say $20,000 for example

Then:
Create a table with 2 columns
column A would show bottom of revenue range

Column B would show the percentage of the revenue that
would flow to the producer

Example:
A B
5 Range Start Percent to Agent
6 $1000 .05
7 $2001 .07
8 $3501 .10
Etc.

Once you get the table built you can create a "lookup"
formula.

So for example in A12 you may type in "% of revenue due to
agent" just as a title.

And then in B12 create the lookup formula such as:
=lookup(a2,a6:a8,b6:b8)

This will look up the correct % to assess on the gross
revenue.

Then in A14 you could type in "$ amount due to agent"
Then in B14 you could enter formula such as:
= b1*b12

Hope this helps.

 

[Guest]

Woops, that lookup formula should be
=LOOKUP(B1,A6:A8,B6:B8)

 

[Guest]

To make a formula based on more than one criteria, you
need to use either the "OR" or the "AND" function.

In your example, this formula works: =if(AND
(C2>=20000,C2<=25000),C2*.07,"Action if false") where "C2"
is revenue value.

However, since you're limited to seven nested if
statements, your best bet if you want purely a formula is
to make a lookup table and use VLOOKUP or HLOOKUP to get
the corresponding percentage to use as a multiplier.

HTH,

Allison

 

[steve]

Kevin,

Try a lookup table for the % that you want. (See help on vlookup)

=VLOOKUP(B2,$D$1:$E$5,2,TRUE)
where B2 is the revenue. $D$1:$E$5 is the range containing your table.
2 represents the column to extract. True says to find the closest match.
(I suggest you use the decimal percent in my 3rd column)

The table can be on a different sheet. If so change $D$1:$E$5 to
Sheet2!$D$1:$E$5 (change Sheet2 to the sheet name)

Set up your table for the minimum in each range
Revenue Percentage
1 5.5 (0.055)
20001 7.0 (0.007)
etc

= B2*VLOOKUP(B2,$D$1:$E$5,2,TRUE)

 

[merjet]

Due to the number of brackets, the clearest solution
might be a user-defined function like this.

Function Commission(X As Double) As Double
Select Case X
Case Is <= 20000
Commission = 0.055 * X
Case Is <= 25000
Commission = 0.07 * X
Case Is <= 30000
Commission = 0.075 * X
Case Else
Commission = 0.08 * X
End Function

It assumes one rate on all revenue of a given amount,
NOT e.g. a rate of 5.5% on the 1st $20000 and 7%
on $2500 for revenue of $22500.

HTH,
Merjet