Help with a range formula

Hello,

In my spreadsheet I have a percent listed in C20 (206%), this is the 
salespersons percent to quota.  Below I have a chart that breaks down the 
salespersons payout based on that percent

        Sales Bonus Schedule:	
        Percent to Quota	    Bonus
N1    10-19%	            N2     $125 
O1    20-39%	            O2     $500 
P1    40-99%	            P2     $1,500 
Q1    100-199%	            Q2     $2,000 
R1     200-400%	            R2     $5,000 
S1     401% and above           S2     $10,000 

In D20 I would like to return the amount of the bonus based on the percent 
to quota.  Something to keep in mind is that the cells that show the percent 
to quota range in N1-S1 are in the same cell.  This is hte part im having 
difficulty with from a formula percepective.

Thanks for the help.
Jim
0
Utf
4/7/2010 3:35:01 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
963 Views

Similar Articles

[PageSpeed] 44

Your schedule is set up incorrectly and you will not have luck with that. I 
created this table in A2:B7

10%	125
20%	500
40%	1,500
100%	2,000
200%	5,000
401%	10,000

and used this formula
=INDEX(B2:B7, MATCH(C20, A2:A7,1))

-- 
HTH...

Jim Thomlinson


"Jim" wrote:

> Hello,
> 
> In my spreadsheet I have a percent listed in C20 (206%), this is the 
> salespersons percent to quota.  Below I have a chart that breaks down the 
> salespersons payout based on that percent
> 
>         Sales Bonus Schedule:	
>         Percent to Quota	    Bonus
> N1    10-19%	            N2     $125 
> O1    20-39%	            O2     $500 
> P1    40-99%	            P2     $1,500 
> Q1    100-199%	            Q2     $2,000 
> R1     200-400%	            R2     $5,000 
> S1     401% and above           S2     $10,000 
> 
> In D20 I would like to return the amount of the bonus based on the percent 
> to quota.  Something to keep in mind is that the cells that show the percent 
> to quota range in N1-S1 are in the same cell.  This is hte part im having 
> difficulty with from a formula percepective.
> 
> Thanks for the help.
> Jim
0
Utf
4/7/2010 3:54:06 PM
Jim,

your solution is perfect.  I don't know I would have thought about making 
change.  I have one follow up.  Adding one additional criteria: How would you 
write the formula for the following, where percent to quota is listed in C20 
and the reps title is listed in B20?  The headers for the payout amounts 
match B20, but there are three possibilities.

 Percent to Quota 	AE	SAE	MAE
0	 $ -   	 $ -   	 $ -   
20	$350 	$500 	$750 
50	$700 	$1,000 	$1,500 
70	$1,050 	$1,500 	$2,250 
80	$1,750 	$2,500 	$3,750 
90	$2,450 	$3,500 	$5,250 
100	$3,500 	$5,000 	$7,500 
125	$4,375 	$6,250 	$9,375 
150	$5,250 	$7,500 	$11,250 
200	$7,000 	$10,000 	$15,000 
250	$8,750 	$12,500 	$18,750 
350	$12,250 	$17,500 	$26,250 
500	$15,750 	$22,500 	$33,750 


"Jim Thomlinson" wrote:

> Your schedule is set up incorrectly and you will not have luck with that. I 
> created this table in A2:B7
> 
> 10%	125
> 20%	500
> 40%	1,500
> 100%	2,000
> 200%	5,000
> 401%	10,000
> 
> and used this formula
> =INDEX(B2:B7, MATCH(C20, A2:A7,1))
> 
> -- 
> HTH...
> 
> Jim Thomlinson
> 
> 
> "Jim" wrote:
> 
> > Hello,
> > 
> > In my spreadsheet I have a percent listed in C20 (206%), this is the 
> > salespersons percent to quota.  Below I have a chart that breaks down the 
> > salespersons payout based on that percent
> > 
> >         Sales Bonus Schedule:	
> >         Percent to Quota	    Bonus
> > N1    10-19%	            N2     $125 
> > O1    20-39%	            O2     $500 
> > P1    40-99%	            P2     $1,500 
> > Q1    100-199%	            Q2     $2,000 
> > R1     200-400%	            R2     $5,000 
> > S1     401% and above           S2     $10,000 
> > 
> > In D20 I would like to return the amount of the bonus based on the percent 
> > to quota.  Something to keep in mind is that the cells that show the percent 
> > to quota range in N1-S1 are in the same cell.  This is hte part im having 
> > difficulty with from a formula percepective.
> > 
> > Thanks for the help.
> > Jim
0
Utf
4/7/2010 4:40:01 PM
similar to this... with your table in A1:D14

=INDEX(B2:D14, MATCH(C20, A2:A14, 1), MATCH(B20, B1:D1, 0))
-- 
HTH...

Jim Thomlinson


"Jim" wrote:

> Jim,
> 
> your solution is perfect.  I don't know I would have thought about making 
> change.  I have one follow up.  Adding one additional criteria: How would you 
> write the formula for the following, where percent to quota is listed in C20 
> and the reps title is listed in B20?  The headers for the payout amounts 
> match B20, but there are three possibilities.
> 
>  Percent to Quota 	AE	SAE	MAE
> 0	 $ -   	 $ -   	 $ -   
> 20	$350 	$500 	$750 
> 50	$700 	$1,000 	$1,500 
> 70	$1,050 	$1,500 	$2,250 
> 80	$1,750 	$2,500 	$3,750 
> 90	$2,450 	$3,500 	$5,250 
> 100	$3,500 	$5,000 	$7,500 
> 125	$4,375 	$6,250 	$9,375 
> 150	$5,250 	$7,500 	$11,250 
> 200	$7,000 	$10,000 	$15,000 
> 250	$8,750 	$12,500 	$18,750 
> 350	$12,250 	$17,500 	$26,250 
> 500	$15,750 	$22,500 	$33,750 
> 
> 
> "Jim Thomlinson" wrote:
> 
> > Your schedule is set up incorrectly and you will not have luck with that. I 
> > created this table in A2:B7
> > 
> > 10%	125
> > 20%	500
> > 40%	1,500
> > 100%	2,000
> > 200%	5,000
> > 401%	10,000
> > 
> > and used this formula
> > =INDEX(B2:B7, MATCH(C20, A2:A7,1))
> > 
> > -- 
> > HTH...
> > 
> > Jim Thomlinson
> > 
> > 
> > "Jim" wrote:
> > 
> > > Hello,
> > > 
> > > In my spreadsheet I have a percent listed in C20 (206%), this is the 
> > > salespersons percent to quota.  Below I have a chart that breaks down the 
> > > salespersons payout based on that percent
> > > 
> > >         Sales Bonus Schedule:	
> > >         Percent to Quota	    Bonus
> > > N1    10-19%	            N2     $125 
> > > O1    20-39%	            O2     $500 
> > > P1    40-99%	            P2     $1,500 
> > > Q1    100-199%	            Q2     $2,000 
> > > R1     200-400%	            R2     $5,000 
> > > S1     401% and above           S2     $10,000 
> > > 
> > > In D20 I would like to return the amount of the bonus based on the percent 
> > > to quota.  Something to keep in mind is that the cells that show the percent 
> > > to quota range in N1-S1 are in the same cell.  This is hte part im having 
> > > difficulty with from a formula percepective.
> > > 
> > > Thanks for the help.
> > > Jim
0
Utf
4/7/2010 4:58:03 PM
Perfect again.  Thank you!!!

"Jim Thomlinson" wrote:

> similar to this... with your table in A1:D14
> 
> =INDEX(B2:D14, MATCH(C20, A2:A14, 1), MATCH(B20, B1:D1, 0))
> -- 
> HTH...
> 
> Jim Thomlinson
> 
> 
> "Jim" wrote:
> 
> > Jim,
> > 
> > your solution is perfect.  I don't know I would have thought about making 
> > change.  I have one follow up.  Adding one additional criteria: How would you 
> > write the formula for the following, where percent to quota is listed in C20 
> > and the reps title is listed in B20?  The headers for the payout amounts 
> > match B20, but there are three possibilities.
> > 
> >  Percent to Quota 	AE	SAE	MAE
> > 0	 $ -   	 $ -   	 $ -   
> > 20	$350 	$500 	$750 
> > 50	$700 	$1,000 	$1,500 
> > 70	$1,050 	$1,500 	$2,250 
> > 80	$1,750 	$2,500 	$3,750 
> > 90	$2,450 	$3,500 	$5,250 
> > 100	$3,500 	$5,000 	$7,500 
> > 125	$4,375 	$6,250 	$9,375 
> > 150	$5,250 	$7,500 	$11,250 
> > 200	$7,000 	$10,000 	$15,000 
> > 250	$8,750 	$12,500 	$18,750 
> > 350	$12,250 	$17,500 	$26,250 
> > 500	$15,750 	$22,500 	$33,750 
> > 
> > 
> > "Jim Thomlinson" wrote:
> > 
> > > Your schedule is set up incorrectly and you will not have luck with that. I 
> > > created this table in A2:B7
> > > 
> > > 10%	125
> > > 20%	500
> > > 40%	1,500
> > > 100%	2,000
> > > 200%	5,000
> > > 401%	10,000
> > > 
> > > and used this formula
> > > =INDEX(B2:B7, MATCH(C20, A2:A7,1))
> > > 
> > > -- 
> > > HTH...
> > > 
> > > Jim Thomlinson
> > > 
> > > 
> > > "Jim" wrote:
> > > 
> > > > Hello,
> > > > 
> > > > In my spreadsheet I have a percent listed in C20 (206%), this is the 
> > > > salespersons percent to quota.  Below I have a chart that breaks down the 
> > > > salespersons payout based on that percent
> > > > 
> > > >         Sales Bonus Schedule:	
> > > >         Percent to Quota	    Bonus
> > > > N1    10-19%	            N2     $125 
> > > > O1    20-39%	            O2     $500 
> > > > P1    40-99%	            P2     $1,500 
> > > > Q1    100-199%	            Q2     $2,000 
> > > > R1     200-400%	            R2     $5,000 
> > > > S1     401% and above           S2     $10,000 
> > > > 
> > > > In D20 I would like to return the amount of the bonus based on the percent 
> > > > to quota.  Something to keep in mind is that the cells that show the percent 
> > > > to quota range in N1-S1 are in the same cell.  This is hte part im having 
> > > > difficulty with from a formula percepective.
> > > > 
> > > > Thanks for the help.
> > > > Jim
0
Utf
4/7/2010 5:05:01 PM
Reply:

Similar Artilces:

Special Pasting a work book with many sheets and formulas
I have a workbook with many sheets that all have formulas and links to other data. I want to save the workbook as another name with all the worksheets keeping the values only (no links or formulas). Is there a quick way to do this for everysheet without having to special paste every sheet in the workbook. So can I save everysheets data values at workbook level. See this page for a code example http://www.rondebruin.nl/values.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "lex63" <lex63@discussions.microsoft.com> wrote in message news:ED708...

Too Many IF Statements Nesting Error (Excel Formula Loop w/o VBA)
Hello Excel Problem Gurus, First of all, let me thank you in advance. I find it exemplary that you all can devote time to helping others who are having issues with their work. Hopefully one day I can be at a mentor level, and help others too. Hope you can help! I have an issue where I don't know how to write the formula that I need without going over on the nesting. The current formula that I have is as follows: =IF(OR(B7="",J7="",L7="",M7="",N7="",O7="",P7=""),"No Data",IF(V7="Yes",&qu...

How do I set up a daily average of unit sales formula
More info required. -- HTH RP (remove nothere from the email address if mailing direct) "jim m" <jim m@discussions.microsoft.com> wrote in message news:7E6D4510-97C1-42D4-A402-5590201C6065@microsoft.com... > ...

Event ID: 8270 ,LDAP Error--Need help to fix it..
Event Type: Error (win2000 Srv+exchange2000) Event Source: MSExchangeAL Event Category: LDAP Operations Event ID: 8270 Computer: Exchange_2000_Server_Name Description: LDAP returned the error [b] Administration Limit Exceeded. dn: "GUID=8F37136AFCEE0B41BDD353B78F8E6067" changetype: Modify mail:USER1@Domain_Name.Domain_Suffix proxyAddresses:MS:NET/PO/User1 : CCMAIL:User1, PO at NET : SMTP:USER1@EMAIL.Domain_Name.Domain_Suffix : X400:c=US;a= ;p=Exchange_Org_Name;o=Exchange_Site_Name;s=Last_Name;g=First_Name; : smtp:USER1@MAILLIST.Domain_Name.Domain_...

office 2000 [excel] cannot open previously OK file. Help
When trying to open a Microsoft Excel Work Sheet that I had accessed modified and saved to only yesterday [Office 2000] a window pops up with: "name.xls cannot be accessed. The file may be resd-only, or you may be trying toaccess a read only location. Or, the server the document is stored on may not be responding." Older files stored on the same mediums ;HD, Flash memory or 3.5in. diskette are accessed with no problem. This is on a network drive? ...

Excel and some disappeared sheets
Anybody has the same or similar experiences : - I worked with excel file / closed as usually - new day opened and unfortunatelly some very important sheets disappeared / why ? Don=B4t know - I had about 7-9 sheets just now only 3 ones are there but not so important as another ones - the file has original size / I quickly looked into file by normal text editor only for reading and all relevant data are there, however if I open file by Excel, NO original sheets or data are there. - this file is VERY IMPORTANT for me - why happened ? Don=B4t know since I ask anybody for help Thanks in advan...

OL2002 Problem : HELP, SOS, MAYDAY
Hi, I have an IMAP account and OL2002 as client. I have checked for my account ( tools, send/receive settings, define send/receive groups, <my_account>, edit) download item description only. It works only the first time I start OL2002, but it does not work anymore for subsequent automatic/manual send/receive operations. Please could someone really help me ? Thank you in advance, Patrick ...

Dynamic Range Selection Using VBA
What I'm trying to accomplish is to be able to run a procedure that selects a range based on a number provided in another cell. For example; if the number 10 is in cell A1, then cells A20:A30 would be selected when I run the macro. If the number 6 is provided, then cells A20:A26 would be selected. Not sure where to start, so any help is appreciated. this may do what you want range("A20").Resize(range("A1").Value+1).select -- Gary Keramidas Excel 2003 "TEK" <TEK@discussions.microsoft.com> wrote in message news:DA9FFF99-FC28-...

Accommodating for empty cells in this formula?
I have a formula in cell H21, for example, reads like this: =IF($G21<>"",($H20-$G21),"") is there a way to adjust the formula so that an empty cell in G21 doesn't give the #VALUE! in subsequent cells in column H? Just to give a similar example, this formula =SUMIF(A1:A9,"<>0") adjusts for any and all empty cells in A2 to A9. It no longer matters if any of the cells are empty, the formula correctly gives the correct addition of A1 plust a sum of everything between A2 to A10 without any #VALUE! results. Was hoping to have the formula above als...

Help With Margin Formula
Hello, I need help with a margin forumla (calculated from retail). Say I have a cost of $10.00, and I need the formula to calculate a 40% margin from retail. So the retail should end up at $16.67. Not sure how to get from $10.00 to $16.66, I just know the cost and the margin I need to make. Thanks JR =A1/(100%-40%) -- Kind regards, Niek Otten "JR" <gaspower@aol.com> wrote in message news:eGszf.424$2O6.53@newssvr12.news.prodigy.com... > Hello, > I need help with a margin forumla (calculated from retail). Say I have a > cost of $10.00, and I need the formul...

GETPIVOTDATA data_field help
When I enter a reference to a cell, eg A2, as the value for the data_field item in the GETPIVOTDATA I get a #REF error. However, when I instead type the value of A2, eg 2003, manually into the GETPIVOTDATA formula, it works. Can anyone please help--I want this formula to vary based on what is in A2. Cheers This function is so variable in action, and difficult to handle, that have given it up in favour of VLOOKUP. In fact, having no problem i use with pivot tables -- Message posted from http://www.ExcelForum.com Try copying the headings from the pivot table, and pasting them into the ce...

Exchange server crashed, please help....! Need to restore two priv.edb and pub.edb files into one....!
Hi Guys, I was wondering if I could get some help with the following problem we are having on our company. Here is the scenario; Our Windows NT 4.0 SP4a server running Exchange 5.5 SP4 crashed (Server 1) due to the exchange database reaching its 16 Gig's max limit. I went ahead and moved some mailboxes' e-mails to a few .pst files in order to make some space. This worked ok. Then, I decided to build another exchange server (Server 2) to moved some mailboxes and alleviate the load. Once the server was ready and configured as part of the current exchange site, I went ahead and move...

Help!!!!
I am running Exchange 5.5 on NT 4.0 sp6a. Norton Anti- virus deleted my edb.log file. I have done an on-line restore with Arcserve 2000 and now my directory service won't start. getting 1166 internal processing error. I have been to support and could find anything that helped. Thanks in advance You may want to run eseutil /mh <drive and path to your dir.edb and include the dir.edb > c:\edb.dmp. Find this file and see if your dir is also inconsistent. If so you will need to restore your dir. -- Nan Bennett (MSFT) Microsoft Exchange Support Please do not send e-mail...

Removing text from cells leaving numbers (help with function)
I need a function that will remove all text from a cell and just leav numbers. Formatting cells to number does not work. For example if I have: (Sired] Tennessee 37013 (herein I just want 37013 left. Anybody know a function to resolve this -- Message posted from http://www.ExcelForum.com The following will strip the text from the active cell and place the number in the adjcent cell one column to the left. If there are subsequent numbers in the original string you will get erroneous results. Put the cursor on the cell to be processed and run the macro. ********************************...

Formula to count the number of different values in a range
I'm looking for a formula that will give me the number of different values in a range. Example: Column A may have five cells that are "4", five cells that are "7", five cells that are "9". Of the fifteen cells that contain data, there are only 3 different values. I'd like to use a formula that will count the number of different values in column A, in this case the result is "3". Thanks, Paul Try... =SUMPRODUCT((A1:A15<>"")/COUNTIF(A1:A15,A1:A15&"")) OR =SUM(IF(A1:A15<>"",1/COUNTIF(A1:A...

Formula to display nearest following Thursday in mm/dd/yyyy format
Hello, I have been reading and trying different suggestions here to no avail. What I need is a formula to calculate the nearest following thursday, and display it in mm/dd/yyyy format. To be clear, I have a column of varying dates. I need a formula to return the next thursday for each of those dates. To illustrate, say I have 05/22/2010, 05/23/201, 05/24/2010, & 05/26/2010 in cells A1 through A4. In cells B1 through B4, I would like to see 05/27/2010, 05/27/2010, 05/27/2010, & 05/27/2010 representing the following thursday. Thank you for your help! BW T...

I really need help with a formview problem
I have a formview that I place a toolbar onto. In my OnInitialUpdate(), after I create the toolbar, I say GetParentFrame()->RecalcLayout(); ResizeParentToFit(FALSE); ResizeParentToFit(TRUE); The formview is still not sized properly. It looks like the problem is the toolbar on the top of the formview. It is not being taken into account in sizing the formview. The difference at the bottom of the form that is not displayed is the size of the toolbar. Any suggestions? Should work if your toolbar was created (and docked) back in your CMainFrame::OnCreate() method. ----- William Gower ...

Bank One Download is not working
We are trying to download transaction data directly from Bank One and the file is not being recognized by Money - i.e. the Bank One website states that the download is complete, but Money does not recognize it. Note that a file is being saved on our hard drive but we can't figure out how to read it. It is entitled mnyimprt.exe-(numerous numbers and letters follow). In microsoft.public.money, Miki wrote: >We are trying to download transaction data directly from >Bank One and the file is not being recognized by Money - >i.e. the Bank One website states that the downloa...

Find Highest Score In List Formula
Hello all, I'm looking to return the highest score for a users with multiple scores in a list of other users with multiple scores. Thank you, Ron Say the data is like: frank 56 joe 9 frank 74 frank 101 jim 143 jim 146 joe 200 frank 164 joe 135 joe 127 joe 177 jim 10 jim 135 jim 53 frank 190 joe 109 jim 193 jim 29 jim 8 jim 107 joe 93 joe 9 jim 153 jim 186 joe 36 jim 174 jim 141 frank 55 jim 92 frank 141 joe 15 frank 5 frank 34 joe 161 jim 103 joe 88 and we want the max score for frank: =MAX(IF(A1:A36="frank",B1:B36,""...

help #2
Hi, I need help with the following: I need to plot a 2-D column graph comparing the profit margin (y-axis) for the 3 items (x-axis) for Malaysia and Singapore, using the data below; Malaysia Item Mfr's Agent's Retailer's Selling GST Profit Total Cost Charge Markup Price Margin Lipstick 9.85 15.76 12.81 38.42 1.54 27.03 105.40 Toner 11.90 19.04 15.47 46.41 1.86 32.65 127.33 Cleanser 15.90 25.44 20.67 62.01 2.48 43.63 170.13 Total 37.65 60.24 48.95 146.84 5.87 103.31 402.86 Singapore Item Mfr's Agent's Retailer's Selling GST...

Simple Formula
I have a formula, bt4/37 (bt4 = 6) and it returns 5. However, my calculator and an Access database returns 16. Can someone tell me why Excel returns 5? thanks. -- Kat3n hi, Either I'm reading this post incorrectly or you have a broken calculator and are gettting results out of excel & access that are equally incorrect. 6/37= 0.162 recurring So if we assume that your result of 16 is a typo and you meant .16 there must be something your not telling us about the formula your using in Excel. How is the 6 derived in BT4 ? What is the format of BT4 ? Post the pr...

problem opening up program/file
Hello, All. I am having some trouble opening up Word. It was working okay until I loaded some fonts to FontBook. Okay, now I removed all my fonts but I still get this error message about corrupt fonts I have on my computer. When I open Word, I see the blue starting-up window: Word:Mac 2004. Right underneath the product ID #, I see that it is initiating... when it gets to the point "Optimizing font menu performance" I start getting this error windows popping up one after another. (E.g. The font M TimesSmallText is corrupt and should be removed.) I keep on clicking okay for a whole lot...

"external data sources" and "external data ranges"
what is the difference between these? i have run the vb macro code on http://support.microsoft.com/kb/330383 to check if i have any external data sources or data ranges but there are none. the reason i need to know is becuase i'm working with office sharepoint server 2007 and i cannot access a file thru the web access web part because it says: "The workbook that you selected cannot be loaded because it contains the following features that are not supported by Excel Services: External data ranges (also called query tables)" anyone? thanks ...

How do you sort a date range by month?
We are trying to find out how many birthdays fall with in a given month using excel. You could use a helper column, with something like = A2. Format this (Format - Cells - Number - Custom: mmm. Now you can use Data - Subtotals, "At each change in: Month" and "Use function: Count." -- Earl Kiosterud www.smokeylake.com/ ------------------------------------------- "Brewisc13" <Brewisc13@discussions.microsoft.com> wrote in message news:44E02AAC-8216-43F5-846F-E981E978E44B@microsoft.com... > We are trying to find out how many birthdays fall with in...

Credit Card Downloading HELP!
I had 2005 and this worked ok but not on 2006. I download my 20 credit card transactions in QIF format. Money says it has 20 to review so I click that and they simply do not appear in the register! The the heck?!? I've tried quite a few things already to no avail. Deleted history and temp, deleted the statement and previous transactions/statements, repaired my money file a few times, tried to import in a different file but it just doesn't show up. Suggestions please...tia In microsoft.public.money, lucas.allen@gmail.com wrote: >I had 2005 and this worked ok but not on 2006....