Formulate number based on percentage drop

New Contributor


I need help and I am not sure wether this needs a nesting function or sumif or what - I apologize i am not an expert and i have searched but can not find what i need - 

I am in need of a calculator to show commission payables based on sold value. The commission drops 1% per 2% below the expected amount of the sale (PAR) - (starting at 10% pay down to 1% pay) in other words - if the sale (PAR) needs to be at $1000 and they sell for $1000 then 10% is paid ($100) - if it is sold at $990 or $980 (or anywhere between) then it is paid at 9% ($90) if it is sold at $970 or $960 (or anywhere in between) then it is paid at 8% or $80 and so on down to 82% of PAR which pays out at 1% of PAR ($10) (I hope i am making sense here) -  


Screen Shot 2022-04-08 at 3.35.58 PM.png



2 Replies


Please see attached workbook.



Thank you so much - I TRULY appreciate it!!!!


Have a great weekend!!