Calculate percentage above specific amount

[YasserKhalil]

Hello everyone

I have such table of commission

E01.png

And I am using this simple formula

Code: Select all

=IFERROR(VLOOKUP(D30,$B$6:$C$18,2),0)

I need to modify the formula so as to calculate exactly what is above the limit 15000
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

[HansV]

With your table in H2:I15 (headers in H1:I1), and a value in A2. (Note that I added a row for 0)

=INDEX($H$2:$H$15, MATCH(A2,$I$2:$I$15))+QUOTIENT(MAX(A2-15000, 0), 1000)*1500

S2539.png

[YasserKhalil]

Amazing. Thank you very much.