Creating A Excel Formula Question

[Ryan]

Hello,

I hope someone can assist me. I am somewhat new to Excel and hate to
ask stupid questions, but believe what I want to do is possible with a
formula.

I'll try to explain that what and why so it makes since. I have a
spreadsheet that is used in the calculation of sales commissions.
The salesperson has a sales goal, among other things, the spreadsheet
calculates their monthly performance to goal when we key in their
actual sales for the past month. It displays their performance to
goal as a percentage.

Then, there is a table. If a salesperson is at 90% of their goal, they
get one flat dollar amount. If they at a higher percentage, they get
the next higher dollar amount. There is a table on the side of the
spreadsheet with these dollar amounts.

Currently, we look at the calculated percentage and manually look up
the proper flat dollar amount for that percentage and key it into a
cell. I am certain this can be calculated by Excel but am unsure how
to proceed. Here is the "thought process" I am trying to turn into a
formula. These are examples.

1. If the calculated percentage in cell A12 is between 90% and 100%,
then I want cell C12 to display a sum of $100.
2. If the percentage in cell A12 is between 100% and 110%, then I want
the cell in C12 to display a sum of $200.


Now, I can tell it what sum to display or I can put in the cell number
where the table amount is and tell it to drop in what is in the proper
table cell.

Does that make ANY sense. Right now, I look at the percentage in C12,
look at the table on the side and key in the dollar amount. Can't
excel do that itself?

Again, sorry if I am unclear here. I am trying to explain what I want
to do as clearly as possible.

Thanks so much!

 

[Paul]

Hello,

I hope someone can assist me. I am somewhat new to Excel and hate to
ask stupid questions, but believe what I want to do is possible with a
formula.

I'll try to explain that what and why so it makes since. I have a
spreadsheet that is used in the calculation of sales commissions.
The salesperson has a sales goal, among other things, the spreadsheet
calculates their monthly performance to goal when we key in their
actual sales for the past month. It displays their performance to
goal as a percentage.

Then, there is a table. If a salesperson is at 90% of their goal, they
get one flat dollar amount. If they at a higher percentage, they get
the next higher dollar amount. There is a table on the side of the
spreadsheet with these dollar amounts.

Currently, we look at the calculated percentage and manually look up
the proper flat dollar amount for that percentage and key it into a
cell. I am certain this can be calculated by Excel but am unsure how
to proceed. Here is the "thought process" I am trying to turn into a
formula. These are examples.

1. If the calculated percentage in cell A12 is between 90% and 100%,
then I want cell C12 to display a sum of $100.
2. If the percentage in cell A12 is between 100% and 110%, then I want
the cell in C12 to display a sum of $200.


Now, I can tell it what sum to display or I can put in the cell number
where the table amount is and tell it to drop in what is in the proper
table cell.

Does that make ANY sense. Right now, I look at the percentage in C12,
look at the table on the side and key in the dollar amount. Can't
excel do that itself?

Again, sorry if I am unclear here. I am trying to explain what I want
to do as clearly as possible.

Thanks so much!

You can write a formula in C12 such as this:
=IF(A12>=100%,200,IF(A12>=90%,100,0))

Your post isn't totally clear, though.
What if A12 is greater than 110%? (I have assumed $200.)
What if A12 is less than 90%? (I have assumed $0.)
Also, what if A12 is exactly equal to 100%? (I have assumed $200.)
The formula may require modification if my assumptions are not correct.

 

[shades]

Howdy. If I understand correctly, Excel can do this through using a
lookup table (or depending on the size of the reference table, using IF
statements)

Simple case, put this formula in C12

=IF(AND(A12>0.9)*(A12<1),100,200)

If you have more conditions, then you can add nested IF statements (up
to 7), or better set up a reference table.

Does this give you at least a start?

 

[Charlie]

I like the idea of using a lookup function. It may require
a little work on the commission table but it works great.
Lets say the commission table is in J5.K31. And the
Percentage for each saleperson starts at C5.
Use the following formular in cell D5 and copy down to the
last salesperson. =LOOKUP(C5,$J$5:$K$31.
In the commission table list all the percents that could
be used by the salesperson in column J (90,91,92,93 etc)
in column K list the dollar amount corresponding with the
percentage, (100,100,100,200,200 etc.)