Need help with some rather complex formulas......
Thanks for this formula, but I need to have each level broken out
separately. I tried to take your formula and split it up like so: =IF(C14<51,MIN(7500,R14)*8%) =IF(C14<51,MIN(25000,R14)*7%) =IF(C14<51,MIN(50000,R14)*6.5%) But this will not get what I need and it does not allow for the 4th level calculation which is what ever the remaining balance is, if any. Based on the amount of $64832, your formula showed a commission on 4339.92, which is correct, but I do need it in 4 different formulas. Can that be done? Thanks a ton for your help this far. "daddylonglegs" wrote in message ... ...or another way to calculate the same thing, leaving out for a minute your check of C14..... =R14*6%+MIN(50000,R14)*0.5%+MIN(25000,R14)*0.5%+MI N(7500,R14)*1% "daddylonglegs" wrote: You can calculate the total commission with one formula....see here http://www.mcgimpsey.com/excel/variablerate.html "Dan B" wrote: Complex to me anyway... I'm trying to create a commission schedule that will calculate at multiple levels. The first level is $7500, the second level is $17,500 more then the first, the third level is $25,000 more then the second, then anything over that. Each level is a separate formula. For example, if there is a sale for $100,000 I need to calculate 8% of the first $7500, then 7% on the next $17,500, then 6.5% on the next $25,000, then 6% on anything thereafter. It also needs to check another cell to see if its value is 50 or under. Here is my initial formula for the first level, which obviously doesn't give the desired result: =IF (c14<51,IF(AND(R14<7500.01),R14*$BI$4,"")) In my data, C14=7 and R14=$64,832, so it returned 5186.55, because both conditions were true. But I needed to just give me 8% of the first $7500 out of the $64,832, which is $600 My second level formula would need to calculate 7% of the next $17500 after the first $7500, which is $1225 The third level formula would calculate 6.5% of the next $25000 after the initial $7500 and next $17500 The next formula would calculate 6% on the balance in this case of $14,832, which is $964.08 BI4 is where the 8% comes from, BI5 is the 7% and so on. As a side note, there is another section in here that is for when C14 is over 50 that calculates higher sale levels at different percentages. Same idea, just higher numbers. I hope that makes at least some sense. Thanks!!! Dan |
All times are GMT +1. The time now is 01:49 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com