LinkBack URL
About LinkBacks
Hi I am trying to break down these sales prices based on tiers to give me the final sales fee based on the tiered incentives. If that makes sense. I have attached an example spreadsheet with dummy data.
Hi I am trying to break down these sales prices based on tiers to give me the final sales fee based on the tiered incentives. If that makes sense. I have attached an example spreadsheet with dummy data.
nevermind, it didn't work for all.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
missed for a second time, didn't see all the notes.
Thank You Sambo kid for helping.
well, you are welcome but I don't think it was much help once I read all your notes.
so, let me try to figure out your sheet, you have no incentive, then a $0.04 (or 4 cents) incentive up to 2 million, and for anything over 2 million $0.01 or 1 cent additional.
so for a person who has 3,000,000 in sales the first 2,000,000 would earn them $80,000 and the second 1,000,000 would earn them an additional $10,000. Is that correct?
yeap thats it.
I basically ignored the tier calculations (I put them in earlier in post #3 but didn't save them since I figured they were wrong) but here is my if then formula to calculate the incentives based on your box and notes and your post #7. (I think a sumproduct could do this but I haven't worked out the logic yet.)
=IF(AND(L4<>"",L4>2000000),(2000000*0.04)+(L4-2000000)*0.01,IF(AND(J4<>"",J4>2000000),(2000000*0.04)+(J4-2000000)*0.01,IF(AND(H4<>"",H4>2000000),(2000000*0.04)+(H4-2000000)*0.01,IF(AND(F4<>"",F4<=2000000),F4*0.04,""))))
This mostly matches your values in column M but not always, for example you have for 3,000,000 $30,000 but if $2,000,000 gets you $80k and it is 1 cent extra for each sale over 2 million then that one should be 90k and not 30k unless I've misunderstood your notes.
Thank you, Sambo I am going to try out the if statement.
Is there formula that I can place for example 4,000,001 the first 200,000,000 would go into tier 2 and the remaining 2,000,001 would go into tier 3? based on the formulas I have produced in the tier columns?
so what appears in columns C and D? There doesn't seem to be a reason for those columns.
C&D is for no sales.
the attached appears to be what you are looking for.
In M4
=SUMPRODUCT(--($B4>{0;200000})*($B4-{0;2000000}),{0.04;-0.03})
or
=SUMPRODUCT(($B4>$Q$17:$Q$18)*($B4-$Q$17:$Q$18),$R$17:$R$18)
See attached columns N & O
Thank you Guys for the help.
this may be a dumb question but in the formula what does the <>'''' mean? Thanks
It means "is not blank".
Don
Please remember to mark your thread 'Solved' when appropriate.
I have a quick and maybe last question regarding the spreadsheet. I noticed that when I calculated my 15000000 example I received the wrong fee. Instead of 100,000. the fee should have been $130,000. The formula looks like it is capturing the fee per sales in column U.
This is the breakdown I was trying to get.
15,000,000 in sales results in a $130,000 fee.
(2,000,000 * 0.040) + (3,000,000 * 0.010) + (10,000,000 * 0.002)
are the tier / sales breakdowns still ok?
Yeap the tiers look ok and call out the number ranges in columns P to R
Should it not be, per John Topley's earlier suggestion, this formula in M4
=SUMPRODUCT(--(B4>{0,2000000,5000000})*(B4-{0,2000000,5000000}),{0.04,-0.03,-0.008})
and copy it down?
John's formula does work however when trying to place the actual fee per sales from column U into the formula I get different amounts which are not correct. I probably should have asked John about the fee that he caluclated.
It seems to work for the example you specified. I'm not clear why the amounts you have in there are what they are.
So I noticed that when I calculate 15,000,000 in sales that results in a $130,000 fee. based on the fee structure in U
(2,000,000 * 0.040) + (3,000,000 * 0.010) + (10,000,000 * 0.002)
with John's example, I get the $130,000 fee but not with the fee structure in column U. I wonder how he came up with the -0.03 and -0.008.
They are the differences between 0.04 and 0.01, and 0.01 and 0.002 respectively. Which values do you think are wrong using that formula, and why?
Oh ok so to make sure that I understand 0.002 is a -0.008 difference between 0.01 so in essence, the sales are in fact times by the Column U however in the formula the differences are caluclated. Am I saying that correctly?
The formula basically multiplies all sales by 0.04, then anything above 2,000,000 by -0.03, and anything above 5,000,000 by -0.008. Adding up those three results gives (or should give) the correct tiered commissions.
See here for explanation of calculation:
http://www.mcgimpsey.com/excel/variablerate.html
so I was away and now you've marked it as solved but I did work on it so this is what I came up with in cell M4 copied down.
=IF(L4<>"",(L4*0.002)+(J4*0.002)+(H4*0.01)+(F4*0.04),IF(J4<>"",(J4*0.002)+(H4*0.01)+(F4*0.04),IF(H4<>"",(H4*0.01)+(F4*0.04),F4*0.04)))
it appears to work fine based on your changed specs and based on straight forward calculations of each tier for what it is worth.
Thank you John And Sambo.
You're welcome and thank you for the rep!
Last edited by Sam Capricci; 04-24-2018 at 01:34 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks