Formulate number based on percentage drop

New Contributor

Hello,

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

@jfruhling 

Please see attached workbook.

@Patrick2788 

 

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

 

Have a great weekend!!