Nested IF statement not multiplying formula by the designated cell

[randomraz]

Would massively appreciate some help, my small amount of remaining hair is being pulled out as we speak.

I'm attempting to create a basic calculator which calculates the commission due based on entry of a "Revenue Generated" value (B11).

In the IF statement I've tried to create what should be happening is that each additional tier should be calculated with the relevant rate of commission for that band, then added to the commission generated for the previous band.

The issue seems to be that after the 2nd tier the commission that should be applied from column D the new commission rate is not being applied, for all additional IF statements beyond this point it appears as though the commission rate from D3 is being applied instead of D4 and D5 a accordingly. Up to D3 the correct rate is applied, beyond then it's not.

I'd be extremely grateful if someone could help me with this.

[AliGW]

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do: what is the issue and what help are you after?

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[Limor_OP]

my small amount of remaining hair is being pulled out as we speak.
.

LOL

=IF(FALSE,B11*D2,IF(TRUE,F2+(B11-9999.99)*D3, IF(TRUE,F3+(B11-29999.99)*D4,IF(TRUE,F4+(B11-49999.99)*D5))))
Look at your conditions result . when your revenueis 60k 3 conditions meet as TRUE, so the formula will take the 1st occurance and return its calculation.

[randomraz]

Thanks so much for that. It does help.

So my question would be how do I ensure that the condition in the 10,000 - 29,9999 range and then also in the 30,000 - 59,999 range is False so that when the value in B11 is at 60,000 the value calculated in B12 actually comes to 9,900 as it's supposed to?

[Limor_OP]

Hi,
Please see AliGW's post and amend your title.

[randomraz]

I did, does it need amending again?

[Limor_OP]

Oh sorry I didnt know you have. Just noticed now the edit time.

You need to add double condition and create a range limit by using IF(AND

In B12:
=IF(B11< 9999.99,B11*D2,IF(AND(B11> =9999.99,B11< 29999.99),F2+(B11-9999.99)*D3, IF(AND(B11> = 29999.99,B11 < 49999.99),F3+(B11-29999.99)*D4,F4+(B11-49999.99)*D5)))

[randomraz]

Thanks so much for that, this was exactly what was required.

[Limor_OP]

you're welcome.
if your problem is solved, you can go to Thread Tools and select Mark This Thread Solved.