- My Forums
- Tiger Rant
- LSU Recruiting
- SEC Rant
- Saints Talk
- Pelicans Talk
- More Sports Board
- Fantasy Sports
- Golf Board
- Soccer Board
- O-T Lounge
- Tech Board
- Home/Garden Board
- Outdoor Board
- Health/Fitness Board
- Movie/TV Board
- Book Board
- Music Board
- Political Talk
- Money Talk
- Fark Board
- Gaming Board
- Travel Board
- Food/Drink Board
- Ticket Exchange
- TD Help Board
Customize My Forums- View All Forums
- Show Left Links
- Topic Sort Options
- Trending Topics
- Recent Topics
- Active Topics
Started By
Message
Need some Excel Help
Posted on 1/14/20 at 4:49 pm
Posted on 1/14/20 at 4:49 pm
Everyone here has been so great in my past questions that i'm hoping I can get some help again. I am working on developing a commission statement for our sales team and cannot figure out how to get it to work due to there being several variables. Here's the situation:
They have a monthly budget of $100,000
*If they hit $100,000 - $xxx,xxx then they are getting 10% of their budget
*If they hit $90,000 - $99,999 then they would get 8% of their budget
*If they hit $80,000 - $89,999 then they would get 5% of their budget
*If they hit less than $79,999 then they are getting 1% of their budget.
I'm assuming this would be an if statement but because there are 4 ranges, I am not sure how to account for each of those. I would like there to be one cell (commission cell) that holds the formula and then based on the cell that has the actual budget number, the commission cell would change accordingly.
Any help would be greatly appreciated.
They have a monthly budget of $100,000
*If they hit $100,000 - $xxx,xxx then they are getting 10% of their budget
*If they hit $90,000 - $99,999 then they would get 8% of their budget
*If they hit $80,000 - $89,999 then they would get 5% of their budget
*If they hit less than $79,999 then they are getting 1% of their budget.
I'm assuming this would be an if statement but because there are 4 ranges, I am not sure how to account for each of those. I would like there to be one cell (commission cell) that holds the formula and then based on the cell that has the actual budget number, the commission cell would change accordingly.
Any help would be greatly appreciated.
1
Posted on 1/14/20 at 5:11 pm to CHiPs25
There may be better ways than how I would do it but I would chain a bunch of IF formulas the logic test being if the sales amount is greater than or equal to the lower number in that range...if true than that number times the % for that range....if false redo the if formula for the next range down...hopefully I explained it well enough for you to understand.
ETA: IF(Sales Amount >=100000,Sales amount * .1,IF(sales amount >=90000,Sales amount *.08,IF(Sales amount >=80000,Sales amount * .05,Sales amount * .01)))
I probably screwed something up in there but I think you can get the point.
ETA: IF(Sales Amount >=100000,Sales amount * .1,IF(sales amount >=90000,Sales amount *.08,IF(Sales amount >=80000,Sales amount * .05,Sales amount * .01)))
I probably screwed something up in there but I think you can get the point.
This post was edited on 1/14/20 at 5:20 pm
Posted on 1/14/20 at 5:20 pm to Drakeo1990
=If(A1>=100000, A1*.1), If(And(A1>=90000, A1<99000), A1*.05), If
Keep going and plug in your needed values
A1 being where the tots for the employee goes. Ranges go in the and statements.
Keep going and plug in your needed values
A1 being where the tots for the employee goes. Ranges go in the and statements.
This post was edited on 1/14/20 at 5:21 pm
Page 1 of 1
Popular
Back to top
Follow TigerDroppings for LSU Football News