Formula Help

[daneagle22]

I am trying to calculate commission.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Our sales team has to have 10 demo points to get $20.00, for each additional point after 10 they get $3.00.<o></o>
<o></o>
I am trying to write a formula that does the following:<o></o>
Column A2 is the list of employees<o></o>
Column M is the sum of columns D and F<o></o>
<o></o>
If such an such employee (column A) has (Column M) greater than 10 points then they get $20.00 plus $3.00 for each additional point beyond 10.<o></o>

 

[markf5998]

=if(m2=10,"20",if(m2>10,((m2-10)*3)+20),""))

*edited

 

[texasalynn]

Welcome to MrExcel board....

try this

Excel Workbook
	M	N
1	Points
2	15	20
3	35	26
4	5	0
5	40	29
Sheet2

Excel 2003

Cell Formulas
Range	Formula
N2	=IF(M2<10,0,IF(AND(M2>9,M2<20),20,20+INT((M2-10)/10)*3))

 

[daneagle22]

Thanks!

How do I get the employees name to pull into that formula, as their are about 20 different employees, and I want to get a dollar amount for each employee?

Column M is where the points for each employee are listed for each demo/sale. Their could be the same employee on the sheet 10 times, or just two times. I guess what I am trying to say is I want to be able to come up with a amount that each employee should receive by adding up all their points they received in column M, and have it give me a number for each employee.

Thank you,

 

[texasalynn]

=if(m2<10,0,if(and(m2>9,m2<20),20,20+((m2-10)*3)))

 

[texasalynn]

Thanks!

How do I get the employees name to pull into that formula, as their are about 20 different employees, and I want to get a dollar amount for each employee?

Column M is where the points for each employee are listed for each demo/sale. Their could be the same employee on the sheet 10 times, or just two times. I guess what I am trying to say is I want to be able to come up with a amount that each employee should receive by adding up all their points they received in column M, and have it give me a number for each employee.

Thank you,

Sounds like you want a pivot table