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?

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.


-----Original Message-----
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?
.

Woops, that lookup formula should be
=LOOKUP(B1,A6:A8,B6:B8)
-----Original Message-----
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?
.

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

-----Original Message-----
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?
.

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)
--
sb
"Kevin Vidrine" wrote in message
...
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?

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