Ewen Posted August 11, 2008 Share Posted August 11, 2008 Help with homework please ! I'm stuck on a sum....I need to work out commission for individuals sales figures based on three levels.... up to £20k of sales earns bonus of 2% £20,001 to £40k of sales earns bonus of 3% everything above £40k earns 4% Some individuals sell less than £20k, some more than £40k. I have a table of individuals vs sales etc, its the formula I'm stuck on. Any help much appreciated. Quote Link to comment Share on other sites More sharing options...
Branners Posted August 11, 2008 Share Posted August 11, 2008 its going to need a nested IF statement. Something along the lines of @if(a120000,a1*0.03,if(a1>40000,a1*0.4,))) not sure if that would work exactly, but it would be close. Quote Link to comment Share on other sites More sharing options...
SteveR Posted August 11, 2008 Share Posted August 11, 2008 =if(a1>20000,(if(a1>40000,(((a1-40000)*0.04)+300+400),(((a1-30000)*0.03)+400))),a1*0.02) Where a1 is the cell containing the £sales Quote Link to comment Share on other sites More sharing options...
Ewen Posted August 11, 2008 Author Share Posted August 11, 2008 Ooh, I understand this Can I use if and Vlookup combined ? Quote Link to comment Share on other sites More sharing options...
SteveR Posted August 11, 2008 Share Posted August 11, 2008 Ooh, I understand this Can I use if and Vlookup combined ? Hmmm, there isn't a need to. A VLOOKUP would be more elegant. I'll knock an example together then attach, 2 ticks... EDIT hang on I get what you're asking. OK I'll do that... Quote Link to comment Share on other sites More sharing options...
RedM Posted August 11, 2008 Share Posted August 11, 2008 I love vlookup. What a great way to 'lose' ten minutes! Quote Link to comment Share on other sites More sharing options...
Branners Posted August 11, 2008 Share Posted August 11, 2008 =if(a1>20000,(if(a1>40000,(((a1-40000)*0.04)+300+400),(((a1-30000)*0.03)+400))),a1*0.02) Where a1 is the cell containing the £sales why so complicated? Im trying to work out why you have a +300+400 in there? Mine would work fine and looks to be simpler.. Quote Link to comment Share on other sites More sharing options...
juanchan Posted August 11, 2008 Share Posted August 11, 2008 I'd write a macro. That'd lose an hour I did write a spreadsheet a while back that calculated the tax/NI/student loan repayments etc I would pay based on my basic salary. Took me ages, then the government changed the tax brackets. Barstewards Quote Link to comment Share on other sites More sharing options...
Branners Posted August 11, 2008 Share Posted August 11, 2008 I think I see where I went wrong. If they earn £50000 on sales then the first 20000 is 2%, the next 20,000 is 3% and above 40000 is 4% Quote Link to comment Share on other sites More sharing options...
SteveR Posted August 11, 2008 Share Posted August 11, 2008 why so complicated? Im trying to work out why you have a +300+400 in there? Mine would work fine and looks to be simpler.. If they earned >£40,000, then the 300 = 3% of £10,000 (between bracket £30,000-£40,000) and £400 is 2% of the lower braket i.e. £20,000. It was wrong anyway becaue I forgot to account for if they earned les than £20k. I'm updating another version instead but it's not pretty... Quote Link to comment Share on other sites More sharing options...
Jake Posted August 11, 2008 Share Posted August 11, 2008 =if(a1=40000,((a1-40000)*0.04)+700,((a1-20000)*0.03)+400)) Quote Link to comment Share on other sites More sharing options...
Ewen Posted August 11, 2008 Author Share Posted August 11, 2008 Thanks for your help so far. Using Vlookup is part of the marks, so I'd better try to incorporate it. Too old for this malarky. Quote Link to comment Share on other sites More sharing options...
SteveR Posted August 11, 2008 Share Posted August 11, 2008 Thanks for your help so far. Using Vlookup is part of the marks, so I'd better try to incorporate it. Too old for this malarky. Hmm I'm not sure how you'd use VLOOKUP wrt a range of values, as it's used to look for exact matches AIUI. It' s been too long since I played with Excel. EDIT Ignore that I just had a play. It doesn't help as-is though as you still need to calculate the total commission by doing (a * X) + (b * Y) + (c * Z) Quote Link to comment Share on other sites More sharing options...
couv3z Posted August 11, 2008 Share Posted August 11, 2008 Hmm I'm not sure how you'd use VLOOKUP wrt a range of values, as it's used to look for exact matches AIUI. It' s been too long since I played with Excel. i'm glad i'm not the only one:innocent: Ewen as this is home work, what is the exact question your being asked? Quote Link to comment Share on other sites More sharing options...
ivan Posted August 11, 2008 Share Posted August 11, 2008 Oi Foster - do your own homework and stop copying from the other kids. Quote Link to comment Share on other sites More sharing options...
Ewen Posted August 12, 2008 Author Share Posted August 12, 2008 For the sake of completeness, I thought I'd post up what I ended up with..... =IF(A10,A120000,A140000,1000+((A1-40000)*0.04),"error check formula")))) A1 is the amount of sales (£) over and above a target sales figure. Up to £20k sales over target earns a 2% bonus. Between £20k and £40k sales over target earns a 3% bonus. Any sales over £40k above target earns a 4% bonus. Many thanks to those who took the time to have a stab, I couldnt get them to work, but then my question was a little confusing and at least the answers pointed me in the right direction. Quote Link to comment Share on other sites More sharing options...
Jake Posted August 12, 2008 Share Posted August 12, 2008 Glad you got it sorted, Ewen. Out of interest, did you try the ones we posted? the one I posted in Post #11 seems to work just fine as far as I can see. (Apart from you've moved the goalposts with the possibility of the salesperson's sales being less than zero ) Just for future reference, you can attach xls files. It might make it a bit clearer what you're trying to achieve. So you're doing some sort of course? What is it? Accountancy? IT? Quote Link to comment Share on other sites More sharing options...
Ewen Posted August 12, 2008 Author Share Posted August 12, 2008 Not an official course Jake, I'm mirroring an Excel module in my sons degree course. I do this sort of thing regularly just to keep my hand in with stuff I need to know at work but dont use a lot. I'm not well educated in the accepted sense, but have picked stuff up over the years off my own back. Exam level stuff keeps me at a level thats competant, but no where near the level of the young guns we have at work....wether its Acad, UG, Team Centre, Sap all the way up through pure naval architecture, I need to keep up with this stuff despite the lack of training:( Quote Link to comment Share on other sites More sharing options...
Jake Posted August 12, 2008 Share Posted August 12, 2008 SAP :shudder: Quote Link to comment Share on other sites More sharing options...
Ewen Posted August 12, 2008 Author Share Posted August 12, 2008 :shudder: Quite. No choice however. BTW, I've attached (previous post) a rough working of the problem using the formulas posted...you came closest. Quote Link to comment Share on other sites More sharing options...
Jake Posted August 13, 2008 Share Posted August 13, 2008 Ah yes, I see now that we (me and Steve) were working the commission out wrong. It should be £400 for 20k sales and another £600 for the next 20k. I think your =IF(B270,B2720000,B2740000,1000+((B27-40000)*0.04),"error check formula")))) can be simplified into =IF(B34>40000,((B34-40000)*0.04)+1000,IF(B34>20000,((B34-20000)*0.03)+400,IF(B34>0,B34*0.02,"No Bonus"))) see attached. Quote Link to comment Share on other sites More sharing options...
4000gt Posted August 13, 2008 Share Posted August 13, 2008 Ewen, just a note to keep in mind, its really bad practice to have the variables in your formula. It means they are inflexible when you need to make adjustments. Lets say the percentages change... then you have to go through all the above formula and correct, opposed to having an input cell reference... Quote Link to comment Share on other sites More sharing options...
Ewen Posted August 13, 2008 Author Share Posted August 13, 2008 Ah yes, I see now that we (me and Steve) were working the commission out wrong. It should be £400 for 20k sales and another £600 for the next 20k. I think your ....can be simplified into ...see attached. Yes Jake, that works fine...far neater thanks. Ewen, just a note to keep in mind, its really bad practice to have the variables in your formula. It means they are inflexible when you need to make adjustments. Lets say the percentages change... then you have to go through all the above formula and correct, opposed to having an input cell reference... Yup, the final sheet has cell references, just wrote it like that for simplicity. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.