Multiple IF Statement to calculate commission

I am trying to work out commission.

If a car has been in stock more than 182 days then the commission is £100
If a car has been in stock more than 90 days but less than 182 then then commission is £50
If a car has been in stock less than 90 days and the profit is more than 0 then commission is 10%
If a car has been in stock less than 90 days and the profit is less than 0 then commission is 0

I have a cell (B4) which calculates the "days in stock", and "profit" (B16) but I just haven't a clue how to formulate the commission.

I would be most grateful for any help

Thank you so much

Good afternoon,

Please see the formula below:

=IF(B4>182,100,IF(AND(B4<182,B4>90),50,IF(AND(B4<90,B16>0),10%*B16,IF(AND(B4<90,B16<0),0,"ERROR"))))

One note, there need to be some "or equal to's" in your formula. For example, at stock = 182 days, the formula will throw an error because there is no scenario in which the stock can = 182 (has to be greater than or less than).

I've nested the formulas in the order your list did, so I hope it will be easy to follow. Basically you just need some equal signs distributed to catch the in between's and you should be good to go.

Best of luck,

Thank you, thank you, thank you, bgore09!

A more accurate description should be:

If a car has been in stock more than 182 days then the commission is £100
If a car has been in stock more than 90 days but EQUAL TO or less than 182 then then commission is £50
If a car has been in stock EQUAL TO or less than 90 days and the profit is more than 0 then commission is 10%
If a car has been in stock less than 90 days and the profit is less than 0 then commission is 0

So I changed the formula to:

=IF(C4>182,100,IF(AND(C4<=182,C4>90),50,IF(AND(C4<=90,C16>0),10%*C16,IF(AND(C4<90,C16<0),0,"ERROR"))))

Does this look OK to you?

Looks good. One more addition, the very last if should be c4<=90. Otherwise if you have a stock of 90 and a profit of 0 it will throw an error.

Have a good day,

Thank you so much - this saved me hours of head-scratching!

Good to hear . Glad to help