Jonathong
asked on
IF Functions in Excel
Hello Excel pros!
I am trying to build a commission tracking sheet in Excel for my sales guys. I want to type in the sales guy's sales every day, and have an equation that tells me what his commission should be for each pay period.
The commission tiers are what makes this formula beyond my expertise.
If the sales guy sells up to $5,000 he makes %15 commission
If he sells up to $10,000 he makes 20%
If he sells up to $15,000 he makes 25%
If he Sells up to $30,000 he makes 30%
If he sells more than $30,000 he makes 40%
Thank you
I am trying to build a commission tracking sheet in Excel for my sales guys. I want to type in the sales guy's sales every day, and have an equation that tells me what his commission should be for each pay period.
The commission tiers are what makes this formula beyond my expertise.
If the sales guy sells up to $5,000 he makes %15 commission
If he sells up to $10,000 he makes 20%
If he sells up to $15,000 he makes 25%
If he Sells up to $30,000 he makes 30%
If he sells more than $30,000 he makes 40%
Thank you
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
=IF(A1>30000,A1*0.4,IF(A1>
ASKER
That's perfect Kevin. Thank you!
What does the +1E307 do?
I didn't attempt the other responses since the first response worked perfectly.
What does the +1E307 do?
I didn't attempt the other responses since the first response worked perfectly.
might want to check that accepted solution if =$30,000 .... not sure what you expected but you stated if more than $30,000 then 40% this is %40 if = $30,000
The MATCH function works by finding the best match less than or equal to an entry in the first array. The +1E307 value is the largest 64-bit positive value to cap the last (40%) range. There has to be an equal number of values in each array passed to the MATCH function.
Think of the last tier as a sales number greater or equal than 30,000 and less than some really large number. Which leads me to realize that my formula is a wee bit off - it will use 40% if the sales is exactly 30,000. This corrects that issue:
=CHOOSE(MATCH(A1,{0,5000.0
Kevin
Think of the last tier as a sales number greater or equal than 30,000 and less than some really large number. Which leads me to realize that my formula is a wee bit off - it will use 40% if the sales is exactly 30,000. This corrects that issue:
=CHOOSE(MATCH(A1,{0,5000.0
Kevin
ASKER
I tested the formula, and it works exactly as I was hoping it would! Thank you Kevin.
Open in new window
Considering the sales sum is in cell A2