IF Statement #25

I am attempting to use an IF formula to do the following:

IF sales are less than $75 no commission 
IF sales are >=$76 but < $150 1% commission
IF sales are>=$151 but <$300  2% commision
IF sales are> $300 3% Commission

The Commission paid is stepped at each point and any amount in betwee
the breaks also must have the commission paid.

Could some one please provide some pointers as to the right way t
approach this.

Thanks

Danie

--
Daniel Sloa
-----------------------------------------------------------------------
Daniel Sloan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2894
View this thread: http://www.excelforum.com/showthread.php?threadid=48676

0
11/21/2005 7:17:31 AM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
303 Views

Similar Articles

[PageSpeed] 28

Daniel Sloan Wrote: 
> I am attempting to use an IF formula to do the following:
> 
> IF sales are less than $75 no commission 
> IF sales are >=$76 but < $150 1% commission
> IF sales are>=$151 but <$300  2% commision
> IF sales are> $300 3% Commission
> 
> The Commission paid is stepped at each point and any amount in between
> the breaks also must have the commission paid.
> 
> Could some one please provide some pointers as to the right way to
> approach this.
> 
> Thanks
> 
> Daniel

Hi Daniel

Assuming the sales figure to be in cell A1, use this formula

=IF(A1<=75,A1,IF(OR(A1>75,A1<=150),A1*1.01,IF(OR(A1>150,A1<=300),A1*1.02,A1*1.03)))

Your explanation does not cater for someone getting $150 sales, so have
given $150 1%, you can change the formula if required


-- 
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24783
View this thread: http://www.excelforum.com/showthread.php?threadid=486761

0
11/21/2005 7:55:49 AM
your value is in C1
in any other empy cell the formula is
=IF(C1<75,0,IF(AND(C1>=76,C1<150),0.01*C1,IF(AND(C1>=A151,C1<300),0.02*C1,C1
*0.03)))

check the results for various entries
chang e C1 to suit you


"Daniel Sloan" <Daniel.Sloan.1yu5fg_1132557630.8885@excelforum-nospam.com>
wrote in message
news:Daniel.Sloan.1yu5fg_1132557630.8885@excelforum-nospam.com...
>
> I am attempting to use an IF formula to do the following:
>
> IF sales are less than $75 no commission
> IF sales are >=$76 but < $150 1% commission
> IF sales are>=$151 but <$300  2% commision
> IF sales are> $300 3% Commission
>
> The Commission paid is stepped at each point and any amount in between
> the breaks also must have the commission paid.
>
> Could some one please provide some pointers as to the right way to
> approach this.
>
> Thanks
>
> Daniel
>
>
> --
> Daniel Sloan
> ------------------------------------------------------------------------
> Daniel Sloan's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=28940
> View this thread: http://www.excelforum.com/showthread.php?threadid=486761
>


0
R
11/21/2005 7:57:56 AM
Thank you very much for your suggestions. The formula is held on my
laptop at work so I will get back to you when I reach the offfice in
approximately 12 hours. Thank you once again for your promt and
hopefully helpful suggestions :)


-- 
Daniel Sloan
------------------------------------------------------------------------
Daniel Sloan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28940
View this thread: http://www.excelforum.com/showthread.php?threadid=486761

0
11/21/2005 8:06:27 AM
Another way:

=A1*((A1>75)*MIN(450,CEILING(A1,150))/150)/100

or if the commission continues in the same way after $300 then remove the
MIN function:

=A1*((A1>75)*CEILING(A1,150)/150)/100

-- 
HTH

Sandy
sandymann@mailinator.com
Replace@mailinator with @tiscali.co.uk


"Daniel Sloan" <Daniel.Sloan.1yu5fg_1132557630.8885@excelforum-nospam.com>
wrote in message
news:Daniel.Sloan.1yu5fg_1132557630.8885@excelforum-nospam.com...
>
> I am attempting to use an IF formula to do the following:
>
> IF sales are less than $75 no commission
> IF sales are >=$76 but < $150 1% commission
> IF sales are>=$151 but <$300  2% commision
> IF sales are> $300 3% Commission
>
> The Commission paid is stepped at each point and any amount in between
> the breaks also must have the commission paid.
>
> Could some one please provide some pointers as to the right way to
> approach this.
>
> Thanks
>
> Daniel
>
>
> -- 
> Daniel Sloan
> ------------------------------------------------------------------------
> Daniel Sloan's Profile:
> http://www.excelforum.com/member.php?action=getinfo&userid=28940
> View this thread: http://www.excelforum.com/showthread.php?threadid=486761
>



0
sandymann2 (1054)
11/21/2005 8:24:49 PM
Thank you to all the people who replied in this thread. Your formula was
spot on and myself and my colleagues now have a quick, easy and
effective way of calculating commissions. Thanks again, regards,
Daniel. :)


-- 
Daniel Sloan
------------------------------------------------------------------------
Daniel Sloan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28940
View this thread: http://www.excelforum.com/showthread.php?threadid=486761

0
11/22/2005 8:48:09 AM
Reply:

Similar Artilces:

Electronic Document Delivery
Is there any way to adapt EDD to send statements? It seems off to have to maintain email addresses in two separate places in a relational dataabase. ...

Update statement with Incrementing
I would like to use an update statement to update a group of records and have one of the fields update incrementally beginning with 1 (just for this group). Below is an example of code that I thought would work and the error that is being generated. Any ideas? UPDATE dbo.vw_NHSReadyToBill SET BatchID = 470, ClaimID = (SELECT ROW_NUMBER() OVER (ORDER BY LastName, FirstName) AS GetCount FROM vw_NHSReadyToBill) Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subq...

access 2007 11-25-07
Do u see Northwind 2007?Can you pls teach me how to make a form automatically appear when open the database like the login dialog in Northwind 2007.Tq. Just do this: 1. With the database open, click on the Office Button (MS Logo) 2. Then click on Access Options 3. Then click on the Current Database category 4. Next, under APPLICATION OPTIONS select the DISPLAY FORM. -- Bob Larson Access World Forums Super Moderator Utter Access VIP Tutorials at http://www.btabdevelopment.com __________________________________ If my post was helpful to you, please rate the post. "warba60@gmail.com&quo...

Macro for Date If Statement
I have a column of Dates, need the Macro to look at all the date in the column and compare to the current date and if the date in the column is more that 365 days old want to turn the date in the column GREEN! Can this be done? Currently there are over 500 dates in the column. Thanks for your help Ray Column? Are the dates in a table? If so then the following will work (with column1) of the table containing the cursor Dim oCell As Cell Dim oRng As Range With Selection.Tables(1) For Each oCell In .Columns(1).Cells Set oRng = oCell.Range oRng.End = oRng...

Help with If Sum statement
Hi, I need to be able to do the following but i can not quite arrange the formula corectly.... I have one sheet that has all the weeks in coulumns (for a project). In another sheet i have this A B C D E Analysis Task 3 12/04/2010 14/04/2010 Task 5 15/04/2010 21/04/2010 Task 3 22/04/2010 26/04/2010 Task 10 27/04/2010 10/05/2010 Task 6 11/05/2010 18/05/2010 Analysis Task 2 11/05/2010 12/05/2010 Develop Task 4 13/05/2010 18/05/2010 Task 3.5 13/05/2010 18/05/2010 Analysis Task 1 13/05/2010 13/05/2010 Develop Task 2.5 14/05/2010 18/05/2010 Task 5.8 1...

money essentials and online statements
I purchased Money Essentials about a month ago. I was able to connect to and download info for National City Bank that one time. I haven't been able to download info from the bank to Money since then. Also, I have never been able to connect online with ING Direct. Any ideas would be greatly appreciated! ~Mary ...

VBA Loop If statement
Good morning, Please could someone help me with this. I currently have the following piece of code in my workbook: Sub ProdCheck() Dim Answer As String If Range("B2").Value = "ESX" Then Range("AB2").Value = 10 '*****this is where the ELSEIF argument of the code should be***** Else: Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "Does this product exist?") If Answer = vbNo Then MsgBox "You pressed NO!" Range("AB2").Value = 0 Else MsgBox "You pressed Yes!" R...

Outlook 2000 #25
When Outlook 2000 is launched, the opening process eliminates the video, making the screen go black for about 6 seconds. When the Screen returns, the window is larger, the font is displayed larger, and Outlook functions. When Outlook is closed, the screen goes black for about 6 seconds and the system returns to its original video display, size, colors, etc. Anyone know why 2000 changes the system display? Have you tried updating the video drivers? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. After searching google.groups.com and fin...

Exchange and Windows Server 2003 R2 SP1- Cannot telnet to SMTP port 25
Greetings, We have 4 Exchange 2003 servers. 2 are on Windows 2000 and 2 are on Windows 2003. The 2 that are on Windows 2003 are experiencing a strange problem. You cannot telnet to any machine's smtp port. Telnet itself is functioning fine on both these machines, but when you try to telnet to any machine's smtp port (25), it fails with: "Could not open connection with host on port 25: Connection Failed". Now I've tried telnetting to all the Exchange servers (including themselves) with these two machines to no success. I've even tried telnetting to Sun boxes run...

Count Function 05-25-10
Hi all, I have a options group on my form with 4 option buttons, there values are 1,2,3 & 4 being saved in my table column Currentstatus, I am trying to count the various input using the code below, but it does not add up whats in my main table, it is sometimes doubling the amounts it finds, any help?b Thanks. Total Entered: Count(*) Total approved: Sum(IIf([currentstatus],1,0)) Total Reject: Sum(IIf([currentstatus],2,0)) Total NA: Sum(IIf([currentstatus],3,0)) Total TA: Sum(IIf([currentstatus],4,0)) Your IIf statements are incorrect. Try Total approved: Sum(IIf([curr...

A SQL statement in VBA
On the main form is a subform in datasheet view and 5 command buttons. On the click of one button I have an event procedure that should run this query : SELECT DISTINCT [Client Extended].ID_client, [Client Extended].date_ouverture, [Client Extended].nom_client, [Client Extended].date_naissance, [Client Extended].couriel FROM [Client Extended] RIGHT JOIN Produit ON [Client Extended].ID_client = Produit.REF_client WHERE ((([La première date :])<=[date_livraison]) AND ((Produit.date_livraison)<=[ La seconde date])) GROUP BY [Client Extended].ID_client, [Client Extend...

VBA Coding for DoCMD Statements.
I am running the following code and it works well EXCEPT the "close form" line doesn't want to work. I am not getting an error message, it just won't close the form. Is there something misworded here? Function Another_Record() MSGBOX "Do you wish to do another LRU update?", 4, "LRU UPDATE" If vbYes Then DoCmd.GoToControl "Serial Number" Else DoCmd.Close acDefault, , acSaveNo End If End Function Try Docmd.Close acForm,,acSaveNo And to be really safe you might want to specif...

Help with SQL Statement for pivot table
I'm trying to put a sql statment from an access query into a pivot table query builder. I built the query in access then took the sql view and tried to put it into the excel query builder -- not working. Access put dbo_ in front of all of the table names so I took that out but it's got other problems too. Not sure how the date range should be specified and I wanted it to search a text field for the word "implant". In access I did this by specifying *implant* but sql doesn't seem to like it. Here's the statement: SELECT ApptPending.CreateDate, ApptPending....

Case statement comparing two ranges
Dear All I have writtent some code but I belive I have done it the long way and think there is a much simpler way of doing things. I am trying to compare two ranges in a case statement. Below is the code, can anyone help with reducing the code required or a more efficient way of doing it Tahnsk Regards Private Sub Worksheet_Change(ByVal Target As Range) Dim rngData As Range, rngCell As Range Dim Wday1data As Range, Wday1Cell As Range Dim icolor As Integer Dim WrkDays As Date Dim StartDate As Date Dim Enddate As Date Dim Day1 As Integer Dim icolor2 As Integer Dim i...

What are the {'s either side of my if statement?
when I edit the cell they disappear and my formula display #value! eg: {=IF(SUM(IF(Tracking!$G$17:$G$9895="NB", IF(Tracking!$P$17:$P$9895="X",1,0),0))>0, SUM(IF(Tracking!$G$17:$G$9895="NB", IF(Tracking!$P$17:$P$9895="X",1,0),0)),"")} This means that you have an array formula, that is it is using what are typically single cell functions against an array of cells. The { } are just a way of showing in the formula bar that it is an array formula. An array formula is committed using Ctrl-Shift-Enter, not...

Can download web statements from one user login but not another in XP
When I login into money as user1 I can download my credit card statement into my without a problem. But when I am logged in as user2 the downloads will not work. The computer quickly does something when I initiate the download but I don't get and error message and nothing happen. Note the same file is used on both login cases? Any help out there?? ...

a statement on multiple backups
I've always stated that having multiple backups is a good idea, well last week I found out how good of an idea it was, I had recently installed a new dual core cpu ie faster better etc. I made a backup before I did the upgrade, I also installed a new larger hd for primary drive C, well to my sad suprise I found my latest update to my backups corrupted everything, and I had only one copy of my backups ie Image of Drive C. anyway I ran into multiple issues, bad SATA cable, etc, it's taken me all week to get it up and operating, when Id rather be outside playing in the sunsh...

Crash when printing one specific client statement
Has anyone had a problem with one specific client becoming corrupted so you cannot print their statement? -- Christopher Fazio VMD Chris Fazio | 2009-03-01 | 5:29:32 PM wrote: >Has anyone had a problem with one specific client becoming corrupted >so you cannot print their statement? I have not seen that problem. I assume you're using one of the Business versions of Money. If you give us specifics of your issue, maybe we can help figure it out. Tell us things like: * Type of hardware * Operating system version * Version of Money (Help > About) * Exactly what happens ...

Bug: Cannot import brokerage statement
I just installed Money 2005 and then added my 401k account (Prudential Investments). When it asked me what transactions I wanted to download, I selected all. After the download finished I go to the Review investment activity page. I see a list of all the investment activity. I click the Next button to update Money to this statement. I get a dialog stating that a security was found where it already exists but under a different name. There are two radio buttons, the first to confirm that it is the same security, the second to add it as a new investment. I select the first(default) and the...

installing outlook 04-25-10
I have just installed Mictosoft Office standard 2007 it works fine other than outlook I cannot get outlook to open---as it tells me "Microsoft Exchange in unavailable" I have then three options retry/work off line/cancel the Exchange Server settings my work (university of Washington) gave me do not work and the other two options result in the computer closing. What do I do now? On Sun, 25 Apr 2010 15:46:01 -0700, pjdunbar <pjdunbar@discussions.microsoft.com> wrote: >I have just installed Mictosoft Office standard 2007 it works fine other than > outlo...

Outlook Web Access #25
I hope this is the right forum, I can no longer access OWA from my home computer. I use this for access to my work emails when on the road or at home. After I log-in, the IE window simply closes. Any ideas on what to look at changing? thanks in advance for your help This is not the right forum as OWA is a part of Exchange, not Outlook. Having said that, have you tried reinstalled the JVM? I use IBM's personally but you can use Sun's as well. >-----Original Message----- >I hope this is the right forum, I can no longer access >OWA from my home computer. I use th...

emailing statements #2
Does GP 9.0 only work with Adobe for this feature? If so, which feature? Can one license be used across the network, or do I have to buy a license for each user that may want to email a customer statement? It does. MS seems to have married itself to Adobe for some reason. You will need an Adobe license for each person who will use it. -- Charles Allen, MVP "Dwayne" wrote: > Does GP 9.0 only work with Adobe for this feature? If so, which feature? > Can one license be used across the network, or do I have to buy a license for > each user that may want to email ...

Credit Card Statement Download M05...
I am having an issue with my GM Credit card. I have it set up to download the statement every 2 days. I also have some recurring bills set up to automatically enter them into my credit card account (such as the estimated payment for this card so my balance forecast for my checking account is accurate). When Money downloads the statement, it doesn't try to match the transactions. My checking account downloads the statement every day, and it does match the transactions. It just adds another transaction for the same amount. Is there a setting for each account as to how to handle ...

iif statement 10-10-07
Im back again, if anyone can help I have 2 fields that when the result is seleceted as below Level 1 pass (First field) and level 1 pass (2nd field) I want the result "Full award" or Null I have tried the following statement below Result: IIf(([aon portfolio result]="Level 1 pass" And [aon exam result]="Level 1 pass"),"Full Award",Null) It will run the query but only after acting as a parameter query, but will not show the result full award ehwn the criteria is met Help Me again I have kind of solved problem? The 2 fields are both look ups,...

Need Help with an IF statement
IFF([CSAandSELinventory]![CountOfSERIAL_NUMBER]=[CSAandSELinventory]![LOWLIMIT],"YELLOW") IIF([CSAandSELinventory]![CountOfSERIAL_NUMBER]>[CSAandSELinventory]![LOWLIMIT],"GREEN") IFF([CSAandSELinventory]![CountOfSERIAL_NUMBER]<[CSAandSELinventory]![LOWLIMIT],"RED") I am trying to build a pie chart report on a form. I am using the above to assign colors based on my inventory levels. I know this will not fill the pie chart with color, but it gives me the information I want. Any help would be appreciated. I worked it down to this: COLOR: IIf(...