Calculating bonus formula
Im trying to create a formula to calculate bonuses for my employees and I cant figure it out.
Here is what im trying to do.
If cell C36 is $30000-$50000 they get .5%
If cell C36 is $50000 or above they get 1%
Please help, its driving me crazy!
Thank you so very very much!
Welcome to the forum.
That's a little ambiguous; maybe
=C36 * IF(C36>=50000, 1%, IF(C36>=30000, 0.5%, 0))
Entia non sunt multiplicanda sine necessitate
How about:
Cheers,
Please Login or Register to view this content.
Would you like to say thanks? Please click the: "Add Reputation" button, on the grey bar below the post.
=B4*VLOOKUP(B4,$D$4:$E$5,2) B4 holds the sales amount, D4-E5 is a table referenced by the Vlookup
D4 30000 in D5 50000
E4 0.05 and E5 0.1
Windows 7 using Office 2007 & 2010
Remember your [ code ] [ /code ] tags, makes reading soooo much easier
I can't get it tow work.
Sales total is $70000 I need one break down for calculating .5% on amounts over $30000-$50000 (so $20000 in this instance the bonus should equal $100).. Then in a separate break down for calculating 1% on anything over $50001 ($19999 in this instance the bonus should equal $199.99)
Please Login or Register to view this content.
Date Order # Amount Item #
$10,000 $30,000-$50,000 @ .5% $50,001+ @ 1%
$10,000
$10,000 I need this cell to calculate .5% for the total on C13's if total is from $30K-$50K I need this cell to calculate 1% for the total on C13's if total is from $50001 and above
$10,000
$10,000
$10,000
$10,000
Total $70,000
One cell to state: $30,000-$50,000 @ .5%
I need this cell to calculate .5% for the total on C13's if total is from $30K-$50K
And another cell to state: $50,001+ @ 1%
I need this cell to calculate 1% for the total on C13's if total is from $50001 and above
Can you see my new reply below? I tried to post this again to see if I can make more sense, but someone closed my post cause it was a duplicate. The replies that were previously posted have not been able to work. If you cannot assist, I understand and I will cancel my profile with this website.
I still don't know how someone can cancel my post, when all im looking for is assistance. Im not a happy camper right now.
Did you try my suggestion in post #6? If that doesn't work, please post an example that demonstrates.
I wonder why the OP don't consider multi-tier calculation? If so she could use a formula like this:
Credit goes to DonkeyOte and Collin Legg who showed me how to do multi-tier calculation.Please Login or Register to view this content.
Alf
Im still having a hard time. Is there anyway I can email someone the spread sheet and they can do the calculations directly on it. I feel so lame right now.
You can post it here, Liz. See the forum rules.
Ok, lemme take a look
Alrighty... I attached my file.. Fingers crossed. Details for what I am looking to do are on the spread sheet. Thanks ya'll
To calculate the bonus for the amount in September!C36, here's one of many different formulas you could use:
= MAX(0, 0.5% * (C36-30000)) + MAX(0, 0.5% * (C36-50000))
Alright.. Im sorry, I attached the wrong one! Here's the correct one! So sorry about that! LONG DAY! Thanks again for all your help!
In I13,
= MAX(0, 0.5% * (C13-30000)) + MAX(0, 0.5% * (C13-50000))
That returns $400.
It's possible that the higher rate starts at $50,001 instead of $50,000, but never for any bonus program I've seen.
Its half a percent for sales between $30000-$50000 and one percent for sales over $50001
Pulled from our memo at work:
Sales Range: Incentive:
$0-$29,999 0.00%
$30,000-$50,0000 0.50%
$50,001 and above 1.00%
Any update with my spreadsheet and the last two posts?
Can you confirm the bonus percents? is 0.5 percent 50% or 0.05 and if so what is 1%
Its half a percent and one percent
ok, thanks for replying so quick. In your sample, there's a sales figure of 80,000 and if i understand correct the bonus should be half a percent for the first 50,000 then 1 for anything over 51,000 is that correct?
It is half a percent for sales total from $30000 to $50000 and one percent for anything over $50001.
So $0 - $29999 gets nothing.
Liz, did even try the formula, or are you just convinced it doesn't work?
The figures you inserted in the formula aren't what i was looking for.
Its half a percent for sales between $30000-$50000 and one percent for sales over $50001
=SUM(IF(C13>30000,MIN(50000-30000,C13)*0.005,0),IF(C13>50001,((C13-50001)*0.01),0))
I've done this all in one cell giving a total pf 399.99 do you need it seperated into 2 cells?
slight Edit
Last edited by scottylad2; 10-02-2011 at 07:55 PM.
Shg's figures may not have beenwhat you wanted returned because you were displaying half a percent as .5 which is 50%
It's a minor point, but with that formula, you get no commission on the first dollar over 30,000. That's with it comes out a penny short of what you would expect.
Yes, thank you. Can you separate into two cells for me?
Im sorry if im confusing everyone. Im just sooooo over this! But its got to be done and my brain is fried.
Im sorry again.
Yeah your right, think this fixes that
=SUM(IF(C13>29999,MIN(50000-30000,C13-29999)*0.005,0),IF(C13>50001,((C13-50001)*0.01),0))
in E13 =IF(C13>29999,MIN(50000-30000,C13-29999)*0.005,0)
in H13 =IF(C13>50001,((C13-50001)*0.01),0)
Can you separate into two cells for me?
Thank you! I should have refreshed first! Thank you soooooooooo much!!!
OMG! Thank you so much again! YOU MADE MY DAY!
Glad to help
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks