# Help with IF formula containing multiple conditions + number ranges?

#### ALT003

##### New Member
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,

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

#### gaz_chops

##### Well-known Member
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

##### New Member
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

##### Well-known Member
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

##### New Member
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

##### Well-known Member
OK try this

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

#### ALT003

##### New Member
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

Replies
7
Views
88
Replies
3
Views
583
Replies
3
Views
102
Replies
5
Views
222
Replies
8
Views
225

1,195,904
Messages
6,012,212
Members
441,682
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back