Assuming the sales value is in A1 and the targert is in A3 then:
=A1*0.05 +MAX(A1-A3,0)*0.01+MAX(A1-1.2*A3,0)*0.02
it does give you the final result of 10600, do you need the internediate results as well.
=MIN(A3,A1)*0.05 |
=MAX(MIN(A$1,1.2*A3)-A3,0)*0.06 |
=(MAX(A1,1.2*A3)-1.2*A3)*0.08 |
The intemediate results are:
=MIN(A3,A1)*0.05 =MAX(MIN(A$1,1.2*A3)-A3,0)*0.06 =(MAX(A1,1.2*A3)-1.2*A3)*0.08
<tbody>
</tbody>
Same assumption as before Sales value in A1 sales target in A3