Help with IF formula containing multiple conditions + number ranges?

ALT003

New Member
Joined
May 27, 2015
Messages
4
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

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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?
 
Upvote 0
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 DATAJan
Comms Breakdown100%
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,
 
Upvote 0
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%?
 
Upvote 0
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.


905bf6N.png
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top