I have such table of commission And I am using this simple formula
Code: Select all
=IFERROR(VLOOKUP(D30,$B$6:$C$18,2),0)
example say the value 25005
then according to the table the commission should be 16500
and the value is greater than the limit 15000 so each thousand should be 1500
25005 - 15000 = 10005 so this should be (10 * 1500 ) + 16500 = 31500