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!

Ryan,

Build a table, say in AA1:AB10 (or whatever the last row might be, like so

AA1: 0% AB1: 0
AA2: 90% AB2: 100
AA3: 100% AB3: 200
AA4: 110% AB4: 300
etc.

Then in C12, add the formula

=VLOOKUP(A12,$AA$1:$AB$10,2)

that's it

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Ryan" wrote in message
om...
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!

Hi Bob,

Thanks so much. I believe I pulled in what you suggested and have it
right, but I am getting an #NA as the result. In my example, I used
generic cell names. Maybe it would help if I posted the actual situation
as I am clearly doing something wrong.

Here is the table that already exists in P8 through Q13:

P8: 125.0% Q8: $700
P9: 115.0% Q9: $530
P10: 110.0% Q10: $450
P11: 105.0% Q11: $370
P12: 102.5% Q12: $330
P13: 100.0% Q13: $300

Now, in cell J13 I have the calculated percentage. Now, as an example,
that percentage could be 103% so I would want to have Excel realize the
answer is $330 since it is over 102.5% but less than 105%.

In the cell I want the result to display, I have keyed the following
formula thinking I have followed your directions:

=VLOOKUP(J13,$P$8:Q$13,2)

I am telling it to look at the percentage in J13 and then lookup in the
table in P8 - Q13 the right dollar amount.

I believe I changed the cell values in your example to match my cell
numbers, but I get #NA so I have done something wrong.

Any ideas? I truly appreciate your help.




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Ryan,

You van post the workbook to me if you wish (note my email address note in
my signature), but looking at your post, I think you might just need to put
the data in the other way around, in ascending order, that is

P8: 100% Q8: $300
P9: 102.5% Q9: $330
etc.

I also strongly urge you to have a 0% amount, $)?), otherwise anything less
than 100% will return #N/A. This should go in row 8.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Ryan Healey" wrote in message
...


Hi Bob,

Thanks so much. I believe I pulled in what you suggested and have it
right, but I am getting an #NA as the result. In my example, I used
generic cell names. Maybe it would help if I posted the actual situation
as I am clearly doing something wrong.

Here is the table that already exists in P8 through Q13:

P8: 125.0% Q8: $700
P9: 115.0% Q9: $530
P10: 110.0% Q10: $450
P11: 105.0% Q11: $370
P12: 102.5% Q12: $330
P13: 100.0% Q13: $300

Now, in cell J13 I have the calculated percentage. Now, as an example,
that percentage could be 103% so I would want to have Excel realize the
answer is $330 since it is over 102.5% but less than 105%.

In the cell I want the result to display, I have keyed the following
formula thinking I have followed your directions:

=VLOOKUP(J13,$P$8:Q$13,2)

I am telling it to look at the percentage in J13 and then lookup in the
table in P8 - Q13 the right dollar amount.

I believe I changed the cell values in your example to match my cell
numbers, but I get #NA so I have done something wrong.

Any ideas? I truly appreciate your help.




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hi Bob,

(Once again)

I think I understand. It is very kind of you to offer to look at the
workbook. I would be happy to e-mail it to you, but when I look at your
signature on my screen, I don't see any e-mail address, just the
following:

"Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)"

If you'd like to send me an e-mail to my address ) I
would be thrilled to respond and attached the workbook in question.

You have been such a great help. I appreciate it!
Ryan



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

My email address is

Bob . Phillips @ tiscali . co . uk

remove the embedded spaces.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Ryan Healey" wrote in message
...



Hi Bob,

(Once again)

I think I understand. It is very kind of you to offer to look at the
workbook. I would be happy to e-mail it to you, but when I look at your
signature on my screen, I don't see any e-mail address, just the
following:

"Bob Phillips
.. looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)"

If you'd like to send me an e-mail to my address ) I
would be thrilled to respond and attached the workbook in question.

You have been such a great help. I appreciate it!
Ryan



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!