Help with IF formula containing multiple conditions + number ranges?

[ALT003]

I have been working to create a formula to work out sales commission from sales targets.

So far I have this formula:

=IF(B13<B11*0.70, B13="0"),IF(B13=(B11>=0.7,B11<0.99, B13*0.075)))

Any idea why it isn't working?

Thanks,

 

[gaz_chops]

Welcome to the forum.

"0" is text, change it to just 0

=IF(B13<B11*0.70, B13=0),IF(B13=(B11>=0.7,B11<0.99, B13*0.075)))


Also, your formula stops at the 1st if. what exactly are you trying to do?

 

[ALT003]

Welcome to the forum.

"0" is text, change it to just 0

=IF(B13<B11*0.70, B13=0),IF(B13=(B11>=0.7,B11<0.99, B13*0.075)))


Also, your formula stops at the 1st if. what exactly are you trying to do?

Here is an outline of our sheet:

MONTH DATA	Jan
Comms Breakdown	100%
Target	£15,000
Comms Start	£0
Actual
Paid On	£0
Comms Rate
Comms	£0
Base	£0
Total	£0

<colgroup><col><col></colgroup><tbody>
</tbody>

We are looking to apply a sales commission to the Paid On Sheet. This sales commission is variable on the percentage of the target earned. Below 70% of the target earned we want the Paid On column to display 0 as commission is not paid. From 70% to 90% of the target met we want a commission of 7.5% of the 'actual' column to be shown on the 'paid on' column.

Would this be possible for the formula?

Thanks,

 

[gaz_chops]

What are the cell references in your example above and which is the value to compare to the target? Also, what if it's over 90%?

 

[ALT003]

ADVERTISEMENT

Please find the sheet shown below as a picture:

In regards to the percentages, I will make myself more clear -

If the number in the Actual Box (Cell B13) is less than 70% of the number in the Target box (Cell B11) the desired outcome is for a 0 to be shown in the Comms box (Cell B16)

If the number in the Actual Box (Cell B13) is between 70-99% of the number in the Target box (Cell B11) the desired outcome is for 7.5% of Actual Box Value (Cell B13) is to be shown in the Comms box (Cell B16

If the number in the Actual Box (Cell B13) is between 100-120% of the number in the Target box (Cell B11) the desired outcome is for 10% of Actual Box Value (Cell B13) is to be shown in the Comms box (Cell B16)

If the number in the Actual Box (Cell B13) is 121+% of the number in the Target box (Cell B11) the desired outcome is for 12.5% of Actual Box Value (Cell B13) is to be shown in the Comms box (Cell B16)

I understand this is a complicated formula and I am very grateful for your help.

 

[gaz_chops]

OK try this

=B13*LOOKUP(B13/B11,{0,0.7,1,1.21},{0,0.075,0.1,0.125})

 

[ALT003]

OK try this

=B13*LOOKUP(B13/B11,{0,0.7,1,1.21},{0,0.075,0.1,0.125})

Thanks! I'll try this now