I need to create a tiered commission formula. Its supposed to read like this:
200-299 = .5 pts
300-399 = 1 pt
400-499 = 1.5 pts
500-599 = 2 pts.
600-699 = 2.5 pts.
700-799 = 3 pts.
800-899 = 3.5 pts.
900-999 = 4 pts.
1000-1099 = 4.5 pts.
1100-1199 = 5 pts.
1200+ = 10 pts.
Can anyone help please?
Untested, but
=LOOKUP(A1,{200,300,400,500,600,700,800,900,1000,1100,1200},{.5,1,1.5,2,2.5,3,2.5,4,4.5,5,10})
You should hit F5, because chances are I've edited this post at least 5 times.
Example of Array Formulas
Click the * below on any post that helped you.Originally Posted by Jacc
Sorry for double post, every time I hit edit the site times out.
This will take care of numbers under 200 causing errors.
=IFERROR(LOOKUP(A1,{200,300,400,500,600,700,800,900,1000,1100,1200},{0.5,1,1.5,2,2.5,3,2.5,4,4.5,5,10}),"Under 200")
That formula works but leaves an #N/A error on anything without an amount. How can I get those with a $0.00 to show up as o pts.?
Thank you by the way.. Awesome!!
Zero as Zero
Edit the RED to reflect what you want to happen if it is between 1 and 199
=IFERROR(IF(A1=0,=,LOOKUP(A1,{200,300,400,500,600,700,800,900,1000,1100,1200},{0.5,1,1.5,2,2.5,3,2.5,4,4.5,5,10})),"Under 200")
The formula worked wonders.. thank you.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks