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. Follow

4 Replies
730 Views

Similar Articles

[PageSpeed] 35

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
Reply:

Similar Artilces:

Downloaded Statements don't appear in register
I use MOney 2002 and have background banking set up with US Bank. It has been working succesfully for over a year until just last week. It has stopped showing the downloaded transactions in the account register but it does alert me that there are statements to read. I have cleared my temporary internet files folder but that did not work. Any suggestions would be appreciated. Thanks. Ngoni. Good Morning, I can't offer you any answers, but, wanted to mention that I'm running Money 99 and am having the same issue. I have just contacted my bank's technical support to see ...

SQL statement to find a particular column within all tables
I am looking for a query that will allow me to find all instances of a particular column within all tables so I know where they are all located. Does anyone have such a query they would be willing to share? Thank you. Pam, I posted this query a few months aback on my blog (http://dynamicsgpblogster.blogspot.com/2008/03/in-past-days-i-have-found-lot-of-people.html), but here is the excerpt: select distinct rtrim(objs.name) from syscolumns cols inner join sysobjects objs on (cols.id = objs.id) inner join sysindexes indx on (cols.id = indx.id) where (cols.name = 'ACTINDX') and (ob...

Downloaded "Statement Quantity" does not match Money 2007
I have been using Money's Account Download service with my financial institution (AG Edwards) for over a year with absolutely no problems. Last week I downloaded my last batch of transactions and Money could not reconcile what was in the software vs. what was being reported by AGE. It suggested I add 19 shares of a stock I do not own so that Money and AGE could synch up. Not only have never owned this symbol, but my online AGE account doesn't show that I own it either. Yet the Position Matching screen in Money shows a position for this stock in the "Statement Quantity&q...

SQL statement in form text control
Hello! In a social services DB, one of the reports is all incidents involving a particular client. There are a few thousand records. I am using an SQL statement which filters records by the client's full name and Birthdate. The SQL is sent to the rowsource property of a combo box. The combination of name and DOB provides 1 unique client. (or none if the DOB is mis-entered or is wrong) The report (controlled by a query with parameters provided by the DOB text control and the combo box.) The combo box seems to be just one more thing to click through, so I thought of changi...

How to clear the Open Recent Database pane on Getting Started scre
In Access 2007, on the "Getting Started with Microsoft Office" page, I want to clear all of the file names under "Open Recent Database" on the right. Deleting the actual files from my computer doesn't remove the names from that pane. Why does it matter? I never tried this in 2007 but changing how many to display on older versions cleared list. Click on the Office button, Access Options, Advanced, Display, and set to 0 (zero). Close application. Restart and check. -- Build a little, test a little. "Judith9" wrote: > In Access 20...

change date in a sql statement from a cell
Hello, I am using Excel 2007 I have a pivot table that gets refresh everyday. The data from the pivot table is based on a sql statement, which the data is connected to a AS/400 table. Here is my problem every morning I go in the connection properties and change the SQLstatement (date) in the command text. I don't want my user to do this. What other option can I do? I was thinking change the date in a cell (A1) and somehow the SQL statement picks up the new date or maybe some sort of parameter, but I am clueless in how to do this. Any tips or website to visit I will a...

What is wrong with this IF statement? need help.
This statement works fine but I want the cell to say 0" if the cell H6 has a zero in it. As the stetments stands right now it displayes 6" in the cell if H6 is 0. How can I make that happen? =IF(H6<101,"6""",IF(AND(H6>101.1,H6<151),"7""",IF(AND(H6>151.1,H6<201),"8""",IF(AND(H6>200.1,H6<275.1),"9""",IF(AND(H6>275.2,H6<350.1),"10""",IF(AND(H6>350.2,H6<601),"12""",IF(AND(H6>601.1,H6<901),"14"""))))))) Pleas...

Getting Starting Microsoft Word as your e-mail editor . when clicking on email hyperlink.
Getting "Starting Microsoft Word as your e-mail editor" message when clicking on email hyperlink. Receive the above message on one compute but not the other. Both have updated versions of all Windows XP and Office software. No errors on computer with respect to Word or Normal.Dot. Just something I noticed recently. Of course I can live with it but being inquisitive, I wonder why I receive this or if there is some setting somewhere. I t5ried changing the setting in IE to another mail program and resetting back but that did not eliminate the message. Thank you Patty ...

Help.. Please! Outlook 2003 using 100% CPU every few seconds.. run out of ideas!
Hi anyone with an idea! I have a 1.2 Ghz/800Meg PC with Outlook 2003 running on it. I said teh first bit to show that its not on a small system. Outlook 2K3 is behaving really badly. its installed on XP SP1 with all the most up to date patches. Every 5 or 6 seconds Outlook simply locks the CPU at 100%. Watching this in Task manager is like watching a square wave. Regular as closckwork, and its Outlook.exe thats peaking at 100% CPU usage every few seconds. I use a pop3 acount so no exchange issues here, and I am not using business contact manager This locks outlook and makes it almost ...

Exchange administrator console not starting
Sometimes on my Exchange 5.5 server the exchange administrator console will not start. I would double click on the icon and nothing would happen. A reboot fixes this problem. The server itself is OK as emails are still working fine and I can open the console from my desktop or another exchange server. Any tips on how to resolve this. If you need more information then please do ask. Thanks F. What errors (and the details of the errors) are you getting in the application or system logs? -------------------- >Thread-Topic: Exchange administrator console not starting >thread-index: ...

if/then statements with "counta"
I want to count the number of cells in a column that have a value less than x. Any tips? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200507/1 =countif(Column,"<x") "Patty via OfficeKB.com" wrote: > I want to count the number of cells in a column that have a value less than x. > Any tips? > > > -- > Message posted via OfficeKB.com > http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200507/1 > I replaced COLUMN with the range of cells and I replaced X with my value, but it didn't work. It returne...

Download bank statement
How Can I download on line banking information from non - US bank that have the data available on Line? I am trying to do this with Italian Bank, but the list MS Money 200 offer is only for US institution. Fabio Fontana If the Italian Bank provide downloads, then you might just be able to try it and see whether Money accepts the import. If the bank isn't listed in the US list, it doesn't necessarily mean you can't use it. However, I don't think you'll get any support from MS if things go wrong, so try it in a test file first. -- Glyn Simpson, Microsoft MVP - Money Ch...

Getting address in Statement ID to appear on invoice.
I am trying to the address ID that is listed under Statement Address ID to appear onthe Bill To section of SOP Blank Invoice. I have tried mapping to the RM Statement Header Temp Table that has this information, but it doesn't appear possible. Is there a RW function to pull this information out? Thanks! Why don't they just put the statement address ID in the Bill To address ID of the SOP document? I'm sure I'm missing something here, but just wondering how the Bill To address ID field was 'repurposed'. "CC Account" wrote: > I am trying to the addr...

windows media player 12 crashes on starting up
This is a multi-part message in MIME format. ------=_NextPart_000_000A_01CA7C36.9B9609A0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original Content-Transfer-Encoding: 7bit i have windows 7 ( verified genuine ) i recently replaced the motherboard and cpu to a better spec but the ONLY thing that doesnt work following upgrading is media player 12 i start it up and get a error message followed by the usual search for problem resolution but as yet i have heard NOTHING to resolve the issue it happens EVERYTIME i start it ...

SBS 2008 POP3 Connector will not start, is misconfigured
Hi. New SBS 2008 install, 2 months old, Just starting to configure users POP accounts for the connector. Client is NOT interested in switching from POP collection. I get this error message when trying to start the SBS 2008 POP3 Connector: The Windows SBS POP3 Connector service terminated with service-specific error 13 (0xD). The message contents are: POP3 schedule is misconfigured (there should be 1, but there is actually 0) I have removed and reinstalled the pop3 connector as per MS, no issues have been fixed, I STILL cannot start the service without getting this error....

Outlook Express is not starting
Hi, My Outlook Express does not start, when I try to access newsgroups using Explorer. I type news:// and the Pop up complains that it cannot find MSOE.dll. I've XP home edition with Small Business Office. Where can I doenload the MSOE.dll from? I would wppreciate any pointers in this regard. Thanks, Jaffar This newsgroup is for support of Outlook 97-2003 from the Office family for Windows PCs. For Outlook Express (OE) support try posting in one of these newsgroups: microsoft.public.inetexplorer.ie4.outlookexpress for OE 4.x microsoft.public.windows.inetexplorer.ie5.outlookexpre...

iis on xp does not start The server {A9E69610-B80D-11D0-B9B9-00A0C922E750} did not register with DCOM within the required timeout
When I try to start iis I get 2 errors Tipo evento: Errore Origine evento: Service Control Manager Categoria evento: Nessuno ID evento: 7024 Data: 21/02/2010 Ora: 11.39.32 Utente: N/D Computer: D630 Descrizione: The IIS Admin Service service terminated with service-specific error 2148073478 (0x80090006). and Tipo evento: Errore Origine evento: DCOM Categoria evento: Nessuno ID evento: 10010 Data: 21/02/2010 Ora: 11.40.02 Utente: D630\Administrator Computer: D630 Descrizione: The server {A9E69610-B80D-11D0-B9B9-00A0C922E750} did not register with DCOM within...

Mails get deleted when starting Windows Live Mail Desktop
Hi, Since today all my mails are deleted. And it happens when WLM is started. When I receive a mail, I can open the mail and read it. I also see the .eml file appear in the Inbox folder. But when I shutdown WLM, and when I start WLM again I see the *.eml file gets deleted form the Inbox folder. WLM shows the header from the mail, but when I try to open it, I get the message that there was an error occured when opening the mail. Any help appreciated ? Regards, Richard Bergmans What kind of mail account is it? POP3, IMAP or HTTP? For an IMAP or HTTP account, if a ...

how do i use an if is null, and if is not null statement together
I have to compare two columns of data, and show the status in column three. Column1 = enrolleddate, Column 2= DisenrolledDate, Column 3 = Status If column1 and column2 are null, "Active", if column2 is not null, then disenrolleddate Thanks for helping me. If you were using a spreadsheet, you might need that third column. In Access you can simply use a query to do the comparison and generate the "calculated" value. Regards Jeff Boyce Microsoft Office/Access MVP "latha" <latha@discussions.microsoft.com> wrote in message news:A4366DA3-9E0E-48C8-BB30-452...

Adding "If Statement" with DSum
I am currently working with a database that needs a small adjustment to the following code: =DSum("[WeeksService]","Service Record Query","[EmployeeID] = " & [EmployeeID] & " And [DepartmentName] = Current_Department_Name") This code works to give me the sum of all Weeks Service where the Department Name is equal to the Current Department Name and I want to keep that code. In addition to that code though, I need to add, I'm guessing, an "IF Statement" that will provide me with the sum of ALL Weeks Service of ALL ...

Start a macro using vba
Hello! Is it possible to run a macro from vba. I found ways to achieve this using palette's. But palette's are no more an option in Great Plains 8. Is there any other way by which this can be achieved? Thanks, There is a method that can be used to run a macro from VBA. But it using an interface which is not supported by MBS. If you want an example, please email at the address below (remove the online. first). David Musgrave [MSFT] Senior Development Consultant Escalation Engineer MBS Support - Asia Pacific Microsoft Business Solutions http://www.microsoft.com/BusinessSolution...

Start with a specific worksheet when a Workbook is opened
i have a work book Call *i* and when open i want it to always start at *Data* sheet i found th following code but i dont know what else i need to modify. Private Sub Workbook_Open() On Error Resume Next Application.Goto Reference:=Worksheets("Data").Range("A1"), _ scroll:=True If err = 9 Then 'actually is subscript out of range MsgBox "Requested worksheet ""Data"" was not found " _ & "by Workbook_Open in ThisWorkbook." End If Worksheets("Menu Sheet").Activate Sheets("Data").Move Before:=Sheets(1) '...

Cisco Unified CallConnector for Dynamics service don't start
i try to implement the Cisco Callconnector for CRM4.0 v4.03 but after installation i get this error in the event log If someone could help me, thanks in advance Type de l'événement : Erreur Source de l'événement : .NET Runtime 2.0 Error Reporting Catégorie de l'événement : Aucun ID de l'événement : 5000 Date : 26/09/2008 Heure : 11:41:15 Utilisateur : N/A Ordinateur : Description : EventType clr20r3, P1 c4serverservice.exe, P2 4.0.3.0, P3 486153c5, P4 mscorli...

Changing Start & Finish Dates
I have Microsoft Projct 2007. I have a standard project plan that I use over and over for different clients. When I "Save As" a new plan for a new client I need to change the start and finish dates to reflect the current date - but it only shows the original dates when the plan was 1st created. So the 1st overall task I can't change, which obviously affects the entire plan. When I go to task information these items are greyed out. Even though I went to "Adjust dates" I put in today's date, but it is not reflected anywhere and it did not change the i...

Count Function on True Statements
I wonder if I can do a count on a specific value, like if I had this: CAR1 CAR1 CAR2 CAR2 CAR2 CAR3 Is there a way I can count how many "car2" I have? Currently, the only way I can do this is to set a true/false statement then count the trues. -- krayziez ------------------------------------------------------------------------ krayziez's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=34891 View this thread: http://www.excelforum.com/showthread.php?threadid=546334 =countif(a1:a10,"car2") This is a function that accepts wildcards, so if y...