#### If Statement - no idea where to start!

I'm trying to develop a formula to calculate a sales team incentive bonus.  A
bonus amount is calculated based on sales made, but then is adjusted 3 months
later based on the criteria below.

Less than 50% of sales still on the books 0% payable
50% to 60% of sales 25%
61% to 70% of sales 	50%
71% to 80% of sales 	75%
81% to 90% of sales 	100%
Over 90% of sales 125%

I'm afraid I don't know where to start - can anyone point me in the right
direction?

Thanks

 0
mattymoo (9)
5/21/2008 6:10:00 PM
excel.newusers 15348 articles. 2 followers.

4 Replies
632 Views

Similar Articles

[PageSpeed] 9

Look at the VLOOKUP() function

Here's an excellent tutorial:

http://www.contextures.com/xlFunctions02.html

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Mattymoo" <Mattymoo@discussions.microsoft.com> wrote in message news:DA62E0DA-CEEE-4C8F-B078-21CCFC23DFC6@microsoft.com...
| I'm trying to develop a formula to calculate a sales team incentive bonus.  A
| bonus amount is calculated based on sales made, but then is adjusted 3 months
| later based on the criteria below.
|
|
| Less than 50% of sales still on the books 0% payable
| 50% to 60% of sales 25%
| 61% to 70% of sales 50%
| 71% to 80% of sales 75%
| 81% to 90% of sales 100%
| Over 90% of sales 125%
|
| I'm afraid I don't know where to start - can anyone point me in the right
| direction?
|
| Thanks
|

 0
nicolaus (2022)
5/21/2008 6:22:18 PM
hi,

You could use an IF statement but that can get unweildy. would you like an
alternative, if so try this. Build a table somewhere which in my case is in
A1 - B5 and enter your conditions looking like this:-

50.00%	25.00%
61.00%	50.00%
71.00%	75.00%
81.00%	100.00%
91.00%	125.00%

Note the left hand column is sorted ascending. Then this formula
=VLOOKUP(C1,\$A\$1:\$B\$5,2,TRUE)

the formula look looks at C1 and then looks for a closest match less than C1
in the table starting at 50% and returns the commission from the second
column.

Mike

"Mattymoo" wrote:

> I'm trying to develop a formula to calculate a sales team incentive bonus.  A
> bonus amount is calculated based on sales made, but then is adjusted 3 months
> later based on the criteria below.
>
>
> Less than 50% of sales still on the books 0% payable
> 50% to 60% of sales 25%
> 61% to 70% of sales 	50%
> 71% to 80% of sales 	75%
> 81% to 90% of sales 	100%
> Over 90% of sales 125%
>
> I'm afraid I don't know where to start - can anyone point me in the right
> direction?
>
> Thanks
>
 0
MikeH (222)
5/21/2008 6:29:02 PM
Thank you both for your help.  i'll give it a go and report back if I get stuck

thanks

Pauline

"Mike H" wrote:

> hi,
>
> You could use an IF statement but that can get unweildy. would you like an
> alternative, if so try this. Build a table somewhere which in my case is in
> A1 - B5 and enter your conditions looking like this:-
>
> 50.00%	25.00%
> 61.00%	50.00%
> 71.00%	75.00%
> 81.00%	100.00%
> 91.00%	125.00%
>
> Note the left hand column is sorted ascending. Then this formula
> =VLOOKUP(C1,\$A\$1:\$B\$5,2,TRUE)
>
> the formula look looks at C1 and then looks for a closest match less than C1
> in the table starting at 50% and returns the commission from the second
> column.
>
> Mike
>
>
> "Mattymoo" wrote:
>
> > I'm trying to develop a formula to calculate a sales team incentive bonus.  A
> > bonus amount is calculated based on sales made, but then is adjusted 3 months
> > later based on the criteria below.
> >
> >
> > Less than 50% of sales still on the books 0% payable
> > 50% to 60% of sales 25%
> > 61% to 70% of sales 	50%
> > 71% to 80% of sales 	75%
> > 81% to 90% of sales 	100%
> > Over 90% of sales 125%
> >
> > I'm afraid I don't know where to start - can anyone point me in the right
> > direction?
> >
> > Thanks
> >
 0
mattymoo (9)
5/21/2008 7:39:01 PM
With total sales in A1 and percentage of sales in B1, enter this formula in C1

=LOOKUP(B1,{0,50,61,71,81,91},{0,0.25,0.5,0.75,1,1.25})*A1

Gord Dibben  MS Excel MVP

On Wed, 21 May 2008 11:10:00 -0700, Mattymoo
<Mattymoo@discussions.microsoft.com> wrote:

>I'm trying to develop a formula to calculate a sales team incentive bonus.  A
>bonus amount is calculated based on sales made, but then is adjusted 3 months
>later based on the criteria below.
>
>
>Less than 50% of sales still on the books 0% payable
>50% to 60% of sales 25%
>61% to 70% of sales 	50%
>71% to 80% of sales 	75%
>81% to 90% of sales 	100%
>Over 90% of sales 125%
>
>I'm afraid I don't know where to start - can anyone point me in the right
>direction?
>
>Thanks

 0
Gord
5/21/2008 11:51:34 PM

Similar Artilces:

* In IF Statement
Novice Excel 2003 user who knows absolutely nothing about VBA. I am trying to use an IF statement referencing "1*" in my criteria but it doesn't seem to be able to do this. I have departments "AR" in cell F1, "BSC" in F2, "Finish" in F3, and "Surface" in F4. AR is equal to 1, BSC equal to 2, Finish equal to 3, and Surface equal to 4. In B2:B29 I reference department numbers 1, 2, 3, or 4, and then a letter equal to A through V. Basically what I am looking for is a formula that will enter the department name in C2:C29 based on th...

Statements: Numbering and then being able to apply based on state
Have a client who can have 20,000 invoices for a customer in a month. The customer only gets a statement. Would like to be able to number statements and then if the customer pays the full amount be able to pay based on the statement number. Thought about using lockbox processing to try and set up a format based on the electronic data that comes back with the check as well. But need to find if anyone has a solution for the statement numbering We created a customization for a customer about 5-6 years ago that does something like this...it was pretty involved. From what I recall, ther...

Starting over?
How do I start again from scratch in Outlook with a new .pst file? I don't want to uninstall and reinstall Outlook. You can try creating a fresh profile here: Control Panel > Email. From there you can create new mail accounts & new Personal Folder files "John Smith" <jbloggs@nospam.net> wrote in message news:1ZydnRfQG9kcFOzXnZ2dnUVZ8n2dnZ2d@giganews.com... > How do I start again from scratch in Outlook with a new .pst file? I don't > want to uninstall and reinstall Outlook. > Uninstall / Reinstall wont do anything. Control Panel>Mail Applet...

If Statement inside If statement....
Hello all, I'd like to start by thanking all on this forum for the great advice and assistance you provide! Saved me more times than I can count! OK.... Here is the formula I currently have... =IF(J16="W",ABS(SUM(H16/I16)),- I16) This formula works great however in place of "ABS(SUM(H16/I16))" I need to tell it to do that if the value in H16 is negative. If H16 is positive I want it to perform "=I16*(H16/100)" I'm not sure I explained what I want clearly so I'm going to put it in words.... IF J16 is "W" then if the value in H16 is negat...

Can I create a statement from a spreadsheet
I have created a spreadsheet of 90 people going on a trip for our school showing the amount of the trip and each payment and six or seven payments with check numbers. I also have their current balance. Would I be able to print a statement of their account? The short answer is "yes". However, the best method of doing this, depending on how your data is arranged, is probably to use the XL sheet as a data source for a data merge in Word. In article <2EAF704F-6D0D-4C68-BDF7-EEA5F38EB85E@microsoft.com>, pfb99 <pfb99@discussions.microsoft.com> wrote: > I have crea...

Cannot start Microsoft Outlook #3
I get the message "Cannot start Microsoft Outlook". I've reinstalled Office 2000 and still get the same message. Any ideas? 1) try running scanpst.exe on your Personal Folder 2) try renaming outcmd.dat to .old and restart Outlook. All the Toolbars will reset then. This is quite a common problem with Outlook . The default location for this file is: C:\Documents and Settings\%username%\Local Settings\Application Data\Microsoft\Outlook 3) try recreating the profile in Control Panel-> Mail-> button Show Profiles 4) try running Outlook with the /safe switch from your St...

Create Powerful Joint Ventures Starting Today
************************************************************ YOU Can Create Powerful Joint Ventures Starting Today No Matter What You Sell Or How Long You Have Been Online! I Guarantee It!! ************************************************************ My name is Roy Oron. I'm no genius or 'guru'. I'm a regular person just like you. In the last 12 months I've been blessed to earn over \$500,000 by creating simple Joint Venture arrangements. Frankly, it was easy. Now I'm going to show you EXACTLY how I did it and how YOU CAN DO IT TOO! This is ...

how to transfer id into report on start from crmForm??
hi, I need to put a report into crmForm of an object (like account) so it could run for current object after choosing. I made a button in isv.config but I don't know how to transfer id on start to the report. Does anybody can help me? Thanks crmForm.ObjectId contains the ID of the entity instance being displayed. If you are unsure how to use that, maybe post the corresponding section of the ISV.config file. While I do not mind at all answering your questions, I would also suggest reading the SDK about client-side scripting as it might save you some time posting and waiting for ans...

IF OR Statements
Anybody know how to write a formula for a statement involving "if" and "or" statements such as: if (cell x=? and (cell y=? or cell z=?)) then cell Q=? or blank In general terms: =IF(AND(X=?,OR(Y=?,Z=?)),?,"") This formula would have to be in cell Q An example with actual cells: =IF(AND(A1=10,OR(A2=15,A3=20)),100,"") This formula must be in the cell to which the result should be returned. A formula can't "reach out" and put a value in another cell; it just returns a value to the cell that has the formula. Please note th...

BITS service not starting
windows update stopped working suddenly on Vista Home. error code:80246008.Followed steps per windows guidelines to resolve problem of error 80246008, however when finally staring the BITS service,it starts and stops instantly with message: THE BITS SERVICE ON LOCAL COMPUTER STARTED THEN STOPPED.SOME SERVICES STOP AUTOMATICALLY IF THEY ARE NOT IN USE BY OTHER SERVICES OR PROGRAMS>> pls help....... Hello warya, I recommend downloading and installing MalwareBytes' Antimalware (MBAM) and SUPERAntiSpywaŃe (SAS). Do a full scan with MalwaŃeBytes' and SUPERAntiSpywa...

Cannot start Microsoft Office Outlook. Unable to open the Outlook window. The set of folders could not be opened
i have be using outlook 2003 with exchange 2000 in cashe mode, when i changed over to exchage 2003 after merging the 2000 mail box, i get the following error: Cannot start Microsoft Office Outlook. Unable to open the Outlook window. The set of folders could not be opened and outlook closes. Are you using a PST from earlier than Outlook 2003 and trying to open it with Outlook 2003? -- K. Orland Such as are your habitual thoughts, such also will be the character of your mind; for the soul is dyed by the thoughts - Marcus Aurelius "wizardontherun@hotmail.com" wrote: > i have...

Due Date in Statement of accounts??
Hi , Can i print the sales statement of account showing the DUE DATE ? If yes, kindly please help me to create the field. Thanks in advance. ...

Statement Line Items not purging off statement
I have a few customer statements that although payments and returns have been applied to invoices and paid in full are not purging off their statement?? There are a few different ways to achieve this outcome. This method is when NOT using National Accounts. I find most customers do not want to run the Paid Transaction Removal process too soon, in case of voids and credits are required. We modify the RM Statement and add a cacluated field to supress the body when the current transaction amount is zero. First step is to link the RM Open File to the RM_Statements_TRX_Temp, us...

Starting Outlook Using a different Identity
My wife and I both use Outlook to access our emails. We each have a separate account with our ISP. When Outlook is started up it defaults to the last user. Often times I am the last user and when my wife opens it opens with my identity. The problem with this is that I look at my emails from my ISP when I am in my office if my wife opens Outlook and I was last user then Outlook automatically downloads the emails and then I can't see them through the internet. SO! Is is possible to determine which identity is to be opened when starting up Outlook Adam I am unsure if this is w...

If statement- formula
Well I'm stumped again. If close!ag2:ag19999 contains "xyz" enter contents of close!ag2:ag19999, otherwise enter contents of close!ae2:ae19999 thanx, ~Julz Hi what do you mean with 'enter contents'?. Do you want to add them? If yes try =SUMIF(close!ag2:ag19999,"xyz",close!ag2:ag19999)+SUMIF(close!ag2:ag199 99,"<>xyz",close!ae2:ae19999) -- Regards Frank Kabel Frankfurt, Germany Julz wrote: > Well I'm stumped again. > > If close!ag2:ag19999 contains "xyz" enter contents of > close!ag2:ag19999, otherwise enter cont...

Details in statement
One of my employees' turned something on in store operations to show details on every statement I print I need to turn it off how? ...

Hi, After I download my statements from Fidelity and another similar institution, I get a page with the results of the download. At the top is a caption which tells me how many unread statements I have. There is a column to the left labeled Unread which may have an arrow pointing to one or more statements. Sometimes, even though the page caption says their are no unread statements, there are arrows in the Unread column to one or more accounts, and occasionally, there are in fact transactions when I review the individual account. Is this contradiction a minor bug in Money 2004, or is it tell...

IF statement
Need help with an IF statement. If cell B25 has either 25 or -25, I need cell d7 to return the value of 3 Thank You James, In D7..... =IF(ABS(B25)=25,3,"") John "James Beam" <jimbeam27@earthlink.net> wrote in message news:lShVb.15586\$F23.13831@newsread2.news.pas.earthlink.net... > Need help with an IF statement. > > If cell B25 has either 25 or -25, I need cell d7 to return the value of 3 > > > > Thank You > > Try this in D7: =3*(MOD(B25,25)=0) -- HTH, RD ------------------------------------------------------------------...

Can DMax Statement be added to Where Statement?
On a data entry form for entering data from paper "field" forms, we want to record the paper's "line numbers" for the rows. It is very handy to have the DefaultValue for the Line Number field be incremented automatically, so that only in rare cases does the field need to be dealt with by the person doing the data entry. Allen Browne's CD Library database example gave me a framework learn from that helped me to get this code working: Private Sub Form_Current() Dim strWhere As String If Me.Parent.NewRecord Then Me![txtLine].DefaultValue = 1 El...

Is there a limit to number of successive IF-THEN statements?
I am listing a number of deliverables, and want the deadlines for those deliverables to automatically appear in the next column (I have the deadlines on a tab named "Lists"). Unless I am misunderstanding something, there is a limit of 7 successive IF-THEN statements you can use. Here is my IF-THEN formula: =IF(E4="User Guide",'Lists'!D4,IF(E4="Installation",'Lists'!D5,IF(E4="HW Upgrade",'Lists'!D3,IF(E4="Maintenance",'Lists'!D6,IF(E4="RAS Trouble",'Lists'!D3,IF(E4="Online Help"...

I think a win-98 forum should be started on this website:
http://forums.mydigitallife.info/index.php Checkout this thread: http://forums.mydigitallife.info/threads/2827-Server-2008-Server-2008-R2-OEM-SLP-Keys It's stuff like this that Microsoft wants to kill, and they think they're going to do it by killing their usenet groups. This site is well known for posting XP WGA hacks and cracks. FU Microsoft. I'm going to enjoy watching Apple kicking your as.s Not with Guy like you in a Apple nnpt! ISO macintosh Fullversion Download (981kb/s) ISO macintosh With Crack + Serial (634kb/s) ISO macintosh (981kb/s) ISO ...

How to start fresh with Money 2004
I installed Money 2004 recently, but I accidentally deleted "My money.mny" file, which is a user created file, stand alone in "my document" folder. I thought I could just start fresh and build a new profile, but hey NO! Every time I start Money, an error message jumps out saying the program has encountered a problem and has to be shut down. But it can start from "sample.many" just fine. I have tried uninstall and re-install the program, the problem persists. Money 2004 starts with no problem with "sample.many" but I can't create a personalized prof...

emailing earnings statements
I have a client with approximately 240 employees and he has been asked to email the earnings statements to all his employees. is there a way to configure this in GP or is there a third party product that will accomplish this? Thanks -- Paula Check out Integrity Data. I have worked with them in the past. http://www.integrity-data.com/ Andy "Paula" wrote: > I have a client with approximately 240 employees and he has been asked to > email the earnings statements to all his employees. is there a way to > configure this in GP or is there a third ...

email out Direct Deposit Earning Statements?
Any solutions out there that allow you to email out direct deposit earning statements? Thanks, John Yes, we have a solution. Email me to discuss it. sales@binarystream.com. "johnb" wrote: > Any solutions out there that allow you to email out direct deposit earning > statements? Thanks, John ...

SQL Statement Insert Into
I need to append data from one table1 to table2 using the field in table3 as the criteria. This field in question is 'account number'. How can I write a sql statement to accomplish this? Troy, What binds the tables? In other words, how are they related to each other? Unless the account number is the same for every entity in Table2? -- Gina Whipp 2010 Microsoft MVP (Access) "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "Troy" <Troy@discussions.mic...