Function help needed urgently!!! #2

Hi anyone who can help...

I have two sets of figures 1 is sales and 1 is target.
Eg:

Sales    Target  Incentive
  5         5

In the Incentive column I want a formula that will 
calculate the Incentive payment for employees who exceed 
there target.  The easy part is the if statement for 
example 10% of their sales if they exceed target: 
=if(sales>target,Sales*10%,0)

The complicated part comes in to play when only when they 
hit multiples of 10 are they entitled to an incentive:

If the Sales are 5 and the Target is 5 - no incentive.
If the Sales are 10 and the Target is 5 - no incentive.
If the Sales are 15 then the Incentive should show 5
If the Sales are 20 then the Incentive is still 5.
If the Sales are 25 the the Incentive should show 10.
If the Sales are 30 then the Incentive is still 10.
If the Sales are 35 then the Incentive should show 15.

For every multiple of 10 in the Sales the Incentive 
increases by 5.  Any ideas???

In other words every time they hit 10 over the target they 
get 5 (Euro)

Thanks in advance 
Ann Ireland
0
anncshaw (37)
8/3/2004 3:50:46 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
273 Views

Similar Articles

[PageSpeed] 35

One way:

    =MAX(0,INT((A1-0.1)/10)*5)


In article <ad3001c47971$a422b5a0$a601280a@phx.gbl>,
 "Ann Shaw" <anncshaw@yahoo.com> wrote:

> Hi anyone who can help...
> 
> I have two sets of figures 1 is sales and 1 is target.
> Eg:
> 
> Sales    Target  Incentive
>   5         5
> 
> In the Incentive column I want a formula that will 
> calculate the Incentive payment for employees who exceed 
> there target.  The easy part is the if statement for 
> example 10% of their sales if they exceed target: 
> =if(sales>target,Sales*10%,0)
> 
> The complicated part comes in to play when only when they 
> hit multiples of 10 are they entitled to an incentive:
> 
> If the Sales are 5 and the Target is 5 - no incentive.
> If the Sales are 10 and the Target is 5 - no incentive.
> If the Sales are 15 then the Incentive should show 5
> If the Sales are 20 then the Incentive is still 5.
> If the Sales are 25 the the Incentive should show 10.
> If the Sales are 30 then the Incentive is still 10.
> If the Sales are 35 then the Incentive should show 15.
> 
> For every multiple of 10 in the Sales the Incentive 
> increases by 5.  Any ideas???
> 
> In other words every time they hit 10 over the target they 
> get 5 (Euro)
> 
> Thanks in advance 
> Ann Ireland
0
jemcgimpsey (6723)
8/3/2004 4:46:58 PM
Reply:

Similar Artilces:

Gantt Chart in Excel. Do I need conditional formatting?
Hello all. I have created a Gantt chart that shows approx. 15 tasks in each project. I am using this to show percentage of completion for each task. It is working just fine and looks great but I need it to insert a date when a task is at 100% completion to show the client the date that particular task was completed. How would I do this without ruining my chart I already have working fine? I know this is probably easy to do but I cannot seem to keep the rest of the chart intact. Is conditional formatting the answer? Thanks! You could use Rob Bovey's XY Chart Labeler to do this: h...

quick way to combine 2 files?
If you have two excel files, both of which have 3 or 4 worksheets that are all used in lookup routines etc etc, is there an easy way to combine the two into a single file, as I think it won't work just by copying and pasting individual worksheets over in their entirety tks in advance LeeH Lee, With both workbooks open, group the sheets of one workbook (using Ctrl clicks on the tabs) then right click and select Move or Copy and then move them into the other workbook. HTH, Bernie MS Excel MVP "Lee Harris web" <lee.harris4@virgin.net> wrote in message news:32c0bd8d.04012...

timeclock report help
We use the timeclock report to print out total hours worked for any given two-week pay period. I am trying to modify the timeclock report by adding an additional column that will tell me whether the date on the report was in week 1 of the pay period, or week 2. I would like to return the string 'Week1' if the TimeIn value for that row is less than or equal to the FilterLoLim value + 7, but this does not work, i can't refer to this in my SQL formula. Any ideas?? Thank you, Kevin here is a simple select statement to demonstrate what i'm trying to accomplish: select cas...

2-D charting with multiple rows
I have a power user who wishes to increase the size of his 2-D graphics but is limited to using 32000 records. He needs to be able to use 64,000 records. Any suggestions? I think this might be something that is changed in regedit but I am not sure. Thanks! Yep, and you can bypass the 256 column limit with Windows API calls. Joking aside, how about two series, formatted identically. If you have duplicate entries in the legend, select the legend, then select the text of one entry, and press the Delete key. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorial...

Xpath Newbie Mistake, Please Help!
This code throws an exception when it should just print "C$" to the screen. What is messed up in my xpath? Thanks. test.cs: using System; using System.Xml; using System.IO; class XmlReadSimple { [STAThread] static void Main(string[] args) { XmlDocument xmlDocument = new XmlDocument(); xmlDocument.Load( Path.GetDirectoryName(System.Reflection.Assembly. GetExecutingAssembly().Location) + "\\test.xml" ); System.Xml.XmlNode xmlNode = xmlDocument.SelectSingleNode( "///[@path='c:\']/@name" ); Console.WriteLine(...

Crecordset::addnew functionality fails #2
This problem is about recordset functionality. Actually, When using RFX_Int, value 32484 is being treated as null and can't be entered. So converting RFX_Int into RFX_Long and type casting the variable to long can solve this problem. Look at the link: http://support.microsoft.com/kb/170257/en-us But again, value 1246576928 is being treated as null in case of RFX_Long. And when tried to type cast the variable to double, it shows the fractional truncation error and numeric out of range error. So wouid anyone please let me know: 1)How to resolve the issue? 2) How value can be entered in data...

inviting investors, students, entrepreneurs #2
http://garunar.wordpress.com/ PRIVATE UNIVERSITY COLLEGE owned by students, entrepreneurs DIGITAL EARTHLINK CONNECTING ENTERPRISE EqUaL OPPORTUNITY RESEARCH/SCHOLARSHIP- MBA, BA, BSC, MPHIL., MRES. EDUTAINMENT Homemade Re-Defined WEALTH MANAGEMENT Jewels of Orient: Fast-track wisdom ECONOMIC INCUBATOR LIFETIME HEALTHCARE, online CLINIC & Ayurveda GLOBAL MARITIME/ LAND BUSINESS with over 16000 boutique luxury businesses including 8000 in Asia BEHAVIORAL FINANCE/CANCER THERAPY/ MULTICULTURAL vipassana CEOs, Glamour, Parties, Garunar schools offering microfinance DIGITAL EMPIRES FOR EMOT...

Outlook Client v 1.2 not working in offline mode
Hello, we have installed several CRM Outlook Clients, version 1.2, on the laptops of our salespeople. They are able to work online perfectly, and when going offline, synchronization seems to work, as well. However, after going offline and synchronizing, when they try to open any of the CRM folders, they get an "Access Denied" message (this does not happen in online mode!). I have adjusted the permissions on the UNC Snapshot share on the SQL server, and the Publication Access Lists on the SQL server, and still get the same errors (identical on all of the clients). Does anyone have a...

Help on displaying recession periods in a financial/ stock chart
Using excel version (10.6501.6626)sp3 How do you shade the chart background to show economic recession periods in a financial/ stock chart. I've fiddled with the drawing function-rectangle box, transparency, etc- but it isn't very satisfactory since it corrupts the column/ line colors and usually washes out when printed. So that doesn't appear to be the solution. And after checking thru Microsoft help and the discussion group messages I haven't found a message addressing my problem. Since we see charts all the time with the recession periods shown I know there is a way to acco...

Need help removing characters in multiple cells
I have an excel 2007 sheet that has over 300 cells in a column that have a name of a store plus a number between paranethesis. Is there a one step way I can remove the paranethesis? Here is an example: K-Mart (ABC#73846365) and I want to have just the following K-Mart Each cell has a different store name and set of numbers within the paranethesis. Thanks Select the range to fix. Edit|replace what: _(* (spacebar, open paren, asterisk) with: (leave blank) replace all It looked like you'd want to remove the space character after the t in k-mart. "<----- Mardm...

Help Understanding Microsoft Server Products
Hi all We are looking to upgrade servers (currently running WinServer 2003) - new hardware and software. The Essential Business Server Premium pack appears to be ideal in terms of the software and package offered. BUT it appears that there will not be a version of this supporting Exchange 2010 for some months. This means we put a significant investment into 3 year old software, rather than catching the up-to-date flavour. So what's the difference between buying the EBS package and buying individual software applications separately? Also, is there additional software i...

File in 2007 form
I was trying the Beta Office 2007 and made the mistake of saving one of you Excel file in the new 2007 form. I really need it back in the 2003 form. Problem: due to a variety of issues, I de-installed the Beta version and went back to 2003 but for got about the one file.. So; are there any utilities that anyone knows of the will convert this file back two 2003 form?? It is a simple Excel file, meaning that there are no clever or fancy formula or macros, just a collection of parts and part numbers gathered over MUCH time surfing various web sites. Any help or advise (beside the obvious....

Downloading Crystal report 9.2.2
Hi, I am trying to download and install Crystal report 9.2.2. I found a link in one of the postings ftp://ftp.crystaldecisions.com/outgoing/TS/CC/CRPro922.zip, but it seems like the link is no longer valid. Could anyone guide me to where else I can download Crystal Report 9.2.2? Thanks in advance, Alvin Hi Alvin, If you have a license for v9 you can get 9.2.2 by contacting BusinessObjects. They will validate your crystal 9 license then either send you 9.2.2 or give you a download location. You can download from the following location but you need login details - https://businessobj...

Deleting a store from HQ #2
We have a customer that bought a 24 store HQ license and set up 25 stores, and now his HQ won't work because he has to many stores. How would I go about deleting a store to fix this situation? Any help would be appreciated. LAllison wrote: > We have a customer that bought a 24 store HQ license and set up 25 stores, > and now his HQ won't work because he has to many stores. How would I go > about deleting a store to fix this situation? Any help would be appreciated. You can delete the 25° stores from databases via sql .. cod How would I delete just one of them?...

Mail Security
I am running Exchange 5.5 on Windows advanced server2K. From my Exchange server, logged in as Administrator, you can do the following: start->settings->control panel->mail....then change the mailbox user to anyone in the company......open MS Outlook and read their email... Is there a way I can prevent this from happening (even if only for my own mailbox) Thank you! If Administrator has the role of Service Account Admin on the site object, then this can happen. You wouldn't be the first person who used this account as the service account. You could change the service ac...

help with range lookup and date criteria
Hello, I have a list of doctors (column A) that are each on-cal through a number of days. My argument is: if the date value of B1 an C1 is within the current date, then repeat the value of A1 -- James Spaldin ----------------------------------------------------------------------- James Spalding's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2567 View this thread: http://www.excelforum.com/showthread.php?threadid=39088 hi, James ! > ... a list of doctors (column A) that are each on-call through a number of days. > ... argument is: if the date value ...

Why does this confuse me?? -- ? on Offset function
I'm trying to understand how/why this works... =OFFSET(A12,MATCH(MAX(E12:E300),E12:E300,0)-1,) So the reference cell is a12 - got that Understand the MATCH/MAX functions. I'm confused about the -1 aspect of the OFFSET function. If I understand this it's referencing one row below the reference A12... however it's referencing the same row.... the formula works, though I'm confused as to why... MATCH returns a number from 1 to n (or, possibly an error). So, if you want to include the value of cell A12 in the calculation then you have to correct the MATCH of...

Help with complex query 05-09-07
Hi all, I've got a query that I'm not sure how to develop. My tables: Quotes - QuoteNo, RaisedBy, Customer QuoteItems - RecordID, QuoteNo, PartNo, Lifecycle, Value There's a one-to-many relationship between Quotes and QuoteItems, i.e. one quote can have many items. I need to run a query to show a list of quotes with totals from the QuoteItems table i.e. QuoteNo, RaisedBy, Customer, List of PartNos, List of Lifecycles, TotalValue I haven't got a clue how to start this, I know it needs to be nested queries, but the listing of parts and lifecycles is particularly stumpin...

Help with Date/Time Function
I am working with date/time functions trying to figure out how many hours were spent doing a job. I have a start time and a finish time. But I need to subtract out any time between five pm and six am and weekends from the times. These would be times that no one was actually working on the job. For example I have 4/5/2005 18:23 4/6/2005 17:04 Could someone give me a clue as to how this might be done? Thanks, Steve Monczka smonczka@hotmail.com It's not a trivial thing, here's an example http://www.cpearson.com/excel/DateTimeWS.htm or here http://tinyurl.com/cbphn -- R...

How to combine contents of 2 outlook caledar folders back into onl
Trying to undo archiving of calendar "golfingsailor" <golfingsailor@discussions.microsoft.com> wrote in message news:1447A0BF-070A-4AD5-B991-A9B4074C3C7E@microsoft.com... > Trying to undo archiving of calendar If it's not open already, open the archive PST (File>Open>Outlook Data File). Using either the Calendar or Folder List view of the Navigation Pane, open the calendar in the archive PST. Display that calendar in a table view like By Category or Events. Select all the items in the calendar by pressing Ctrl+A or select the ones you wish to re...

listview control display as a group #2
I have a requirement to display the list items as group. I have been trying to use the CListCtrl::insertGroup() method. The method works fine but the grouping doesnt show it does show the items just as regular items. Here is the code: <html> <head> </head> <body> <font SIZE="2"> <font SIZE="2" COLOR="#0000ff"> <p>void</font><font SIZE="2"> Ctt1View::OnInitialUpdate()</p> <p>{</p> <blockquote> <p>CListView::OnInitialUpdate();</p> </font> <p></...

IF function 05-21-10
I have a list of numbers in column A. Some are formated in 'red' color and the others are 'black'. How do i format column B so there is an 'X' next to the numbers that are 'red' and nothing next to numbers that are 'black.' Thank you I would take the opposite approach. I'd put an X in the adjacent column or leave it blank, then use Data|Validation to color the cell near it. gudencough wrote: > > I have a list of numbers in column A. Some are formated in 'red' color and > the others are 'black'. How do ...

MOP/SOP LINK #2
Hi, I created a new item and its fulfillment method is Make to Order-Manual. I created a sales order and a mfg order for this item. In GP 8.0, I opened the MOP/SOP Link window, only saw the MO on the right side for this item and the SO didn't show up on the left side. Did I miss any step to link MOP and SOP? What should I do, so the SO will show up in the left side? Thanks. stien Make sure the end date is set correctly, it must be out farther than the requested ship date on the SO (the default is the current date) also make sure you have a SITE ID in, even if you only have one s...

Check box and Command button code #2
This code will change the named range of cells yellow when the check box is checked and the command button is pressed which is what I want it to do, but when you check the check box, it will take the user over to my second worksheet named form where the range of cells are. I would like for it to wait to take them over after they press the command button because multiple check boxes will need to be checked. Any help is greatly appreciated. Option Explicit Private Sub CommandButton1_Click() Call CheckBox2_Click End Sub Private Sub CheckBox2_Click() If Me.CheckBox2.Value = False Then ...

Jetstress #2
Hi, Can I use Jetstress on Production environment after Exchange is installed? I still do not have any data on Exchange and has not gone live yet. Will running Jetstress after installing Exchange break anything? Any insight would be much appreciated. Thanks, On Tue, 1 Aug 2006 04:09:02 -0700, Jack Dorson <JackDorson@discussions.microsoft.com> wrote: >Hi, > >Can I use Jetstress on Production environment after Exchange is installed? Yes >I still do not have any data on Exchange and has not gone live yet. > >Will running Jetstress after installing Exchange break ...