I can't take it anymore...Percentage range function

I have to have a spreadsheet done by tomorrow for a new job.  I've come close to tossing this computer out the window and heading for EDD to file for unemployment when I get fired.  No, seriously...they know I'm not an Excel guru.  I'm just trying to help them out right now.  I've gone through, oh I don't know, maybe like a trillion posts on the Internet and I still can't figure this out.

I have a commission figure in B11 ($24,000) and an actual sales figure in D11 ($30,000).  E11 contains the percentage to quota =(D11/B11) which works fine.  I want F11 to show the commission percentage.  In this case, if the percentage to quota is less than 100%,  the commission that needs to appear in F11 is 0%.  I got that far =IF(E11<99,0).  But now, I need a nested FALSE funtion in the same cell with F11 (where the 0% is below) that will show a range of commission percentages that correspond to the actual sales figures entered into column D.  Right now, even though the actual sales show 30K (125% of quota), the commission percentage is still at 0% when it needs to automatically change from 0% to 12% or whatever % is appropriate.  The commission percentage changes with the actual sales made (Sales column D below).  So now, did I give everyone else a headache?

A                   B                    C                       D                 E                  F

Mocean

I am slightly struggling to see what you are doing but maybe your issue is that the 125% is actually stored in Excel as 1.25, so you original function in any case would need to be =IF(E11<1,0).

It appears you are saying that if you don't reach your quota then you get nothing, whereas if you are over your quota (100%), you get a sliding scale of commission, although in you scale you have a 5% level for example at 70-79% of quota?

I think you are needing nested IFs or better still to set up you table and use a VLOOKUP, but beware, if you go the IFs route you can only nest 7 levels in versions of Excel before 2007. Sort of like this

=IF(E11<1,0,IF(E11<1.25,.1,IF(E11<1.5,.12,1.5)))

This only covers the commission %s for above 100% as I don't get the other bit, but hopefully this sets you on the way

You can also use a lookup, which is tidier and easier to amend IMO

=LOOKUP(E11,{0,1,1.25,1.5,99},{0,1,0.12,0.15})

I do apologize for the frantic and convoluted posting.  I just got my boss to delay this thing until Monday (thank heaven)  I'm afraid I'm a real newbie with Excel.  I've worked in it for years, even set a few up, but I've never dealt with anything this complicated.

I read your post and I guess I need to digest the formula in order to understand why it is what it is:-) 

Basically, I'm looking to have a constant (quota), next to that the actual sales figure($), next to that the percentage to quota and next to that, what the percentage commission is and lastly, the actual $ commission earned.  I would like to be able to go into the  Actual Sales $ figures, update them to reflect whatever the current actual figures are and have the % to Quota, Commission Rate% and Actual Commission $ update automatically. It looks like this.

Quota            Actual Sales        % to Quota          Commission Rate         Actual Commission $

24,000.00          $XxX.00                 %                           %                                  $XXX.00

36,000.00

48,000.00

The commission rates will change, so I also need to be able to change the Commission Rate% and have it update all the other fields except Actual Sales and Quota.

Reading your response, will the Lookup function do all this?

Thank you and I'm sorry to bludgeon my way in here asking for help, but I've read everything I can read about this over the last 2 days and I still can't do it.  I read on the Internet, this is place to go for Excel gurus.  Should I just pay someone to do this?  This is only part of what I need to set up.

-M

That's what these forums are for, askig questions.

Uness your spreadsheet is much more complex than you have described to date, the there is no reason to buy in help, we'll sort it here, it doesn't look that hard.

LOOKUP should work for you, this formula should do that conversion

=LOOKUP(C2,{0,0.7,0.8,0.9,1,1.25,1.5},{0,0.05,0.06,0.07,0.1,0.12,0.15})

probably best to put the two arrays in ranges, say M1:M8, N 1:N8 and use

=LOOKUP(C2,M1:M8,N1:N8)

it then is very easy to update the thresholds and/or the rates.

Thank you for your kindness.  I do contribute in forums when I have the expertise to do so.  Being on the receiving end is a little unsettling for me.

I just wish I understood the logical progression in the formulas because if I did, I could extrapolate out the additional formulas I need.  I would probably also understand which cell to put them into.  I need to have this done by tomorrow and I absolutely know I will not have this done.  Learning Excel to this extent in a matter of a few days is ridiculous.  I've never felt so out of my element.  For example, in your first forumula, no matter where I paste it, it results in the same percentage, .15.  When I change the actual sales amount, nothing else changes.  I also don't understand what you mean when you say,  "best to put the two arrays in ranges, say M1:M8, N 1:N8"  I honestly have no idea what that means.  2 arrays?

I am lost.  I really need someone to make this happen.  Do you know of anyone who might be able to do this by tomorrow and what do Excel gurus usually charge for something like this?

The spreadsheet will be simple at first, since the division is just getting off the ground.  I've produced all sorts of things already, including one interactive spreadsheet, but this spreadsheet is making me sick to my stomach:-(

I played with it some more and got part of it to work!  I can't believe it!  I'm 1\100th of the way!  I took apart your formula and made the connection to the percentages, so I 'sort' of understand the logic now.  Now, I'm going to try to do the formula for figuring the actual commission dollars in the last column based on the actual sales made and the corresponding commission percentage

The formula is based upon the bottom of the bands, and checks from the value in the first arrayup,  and below the next band, returning the corresponding value in the second array. So with

=LOOKUP(C2,{0,0.7,0.8,0.9,1,1.25,1.5},{0,0.05,0.06,0.07,0.1,0.12,0.15})

if C2 has a value between 0 and less than 0.7, the formula returns 0.

From 0.7 and less than 0.8, it returns 0.05 or 5%, etc.

Just plug the formula in a cell, format it as a perecentage, and change C2 and see what yoy get.

You can send me a workbook if you like and I will see what I can do.

I don't know what timezeone you are on, but I am GMT.

My email addy is excelguru at xldynamic.com - change at to @ and no spaces.

Bob

Bob Phillips:

I don't know what timezeone you are on, but I am GMT.

You still an hour behind in the 'Kingdom of Dorset?'

I always get confused Nick as to what the affect of DST is, whether it is 1 hour ahead, behind or just plain old GMT.

BTW, how to you include the quote, I can't see anything on the thread that allows that?

Bob

When you type a reply, the op's question is up above at the bottom of that is a hyperlink with quote on it. Click that and it will insert the tags and thye original post for you. You can trim it from here

Nick Hodge:

Bob

When you type a reply, the op's question is up above at the bottom of that is a hyperlink with quote on it. Click that and it will insert the tags and thye original post for you. You can trim it from here

OK I see, it is Reply. I always use, used, Quick Reply.

Thanks Nick.

Hello Bob.

I have a situation similar as the on above. I have to calculate a bonus depending on the retention percentage calculated. Here is how the retention bonus is determined:

I need to figure out how I can create a formula to incorporate the range of percentages above. So lets say I have a retention percentage of 77%, that would fit in to the $100 bonus category. i need a formula that will use the chart above to calculate the bonus. Please give me advise if it is not too much trouble. Thank you

Juan

I'm sure someone will have a cleaner way of doing this.  In my solution the formula below would go into cell D7.

=IF(AND(E9>=B2,E9<=C2),D2,IF(AND(E9>=B3,E9<=C3),D3,IF(AND(E9>=B4,E9<=C4),D4,IF(AND(E9>=B5,E9<=C5),D5,IF(AND(E9>=B6,E9<=C6),D6,IF(AND(E9>=B7,E9<=C7),D7,0))))))

Similar formula

=LOOKUP(C2,{0,0.6,0.6499,0.6999,0.76,0.9201},{0,25,50,75,100,150})