NESTED IF formula with multiple conditions

[selinaag]

Please help - Can I created a multiple condition NESTED if Formula using cell references?

Eg: below - how would I put this into one complete formula? I don't know if I should use array, or how array works?

4 arguments in 1 complete formula (using cell references)
IF <C41 = $0 (or "no bonus")
IF between E41 & F41 = D41
IF between E40 & F40 = D40
IF = > F39 = D39

Second formula - 5 arguments (using cell references)
IF <I43 = J43
IF between K42 & L42 = 0% (or "no bonus")
IF between K41 & L41 = J41
IF between K40 & L40 = J40
IF = > L39 = J39

 

[Mohammad Basem]

Hi SelinaAR,

Yes, you can have a nested IF formula without going for array formula.

However, the defined criteria are not so clear. Cell reference that need to be tested was not specified and "between" did not specify whether the limits are included or not.

Anyway, below is a sample of the 4-argument criteria. Check if it fits with the limits and the return values, and change CellRef to the appropriate cell reference.

=IF(AND(CellRef > E41, CellRef < F41),D41,IF(AND(CellRef > E40,CellRef < F40),D40,IF(CellRef >= F39,D39,0)))

The 0 is returned if all tests failed, is this correct?

 

[selinaag]

Thanks so much - I will try that - it was coming off of the initial formula below, but the amounts/values will not stay the same, so I have to use a cell reference:

4 arguments in 1 complete formula:
IF <84850 = $0 (or "no bonus")
IF between 84850 & 93037.99 = $100.00
IF between 93038 & 101495.99 = $150.00
IF = > 101496 = $200.00

and

Second formula - 5 arguments:
IF <90% = -25% from A1
IF between 90% & 99.99% = 0% (or "no bonus")
IF between 100% & 109.99% = 10% of A1
IF between 110% & 121.99% = 15% of A1
IF = > 122% = 20% of A1

 

[Mohammad Basem]

Two criteria were missing from the original post, the 1st in each.

I think it is clearer now,

Here is the sample for the 4-argument. The 1st reference is E42, right?

=IF(CellRef < E42, 0, IF(AND(CellRef >= E41, CellRef < F41),D41,IF(AND(CellRef >= E40,CellRef < F40),D40,IF(CellRef >= F39,D39,0))))

Which column or cell are you comparing with?

 

[Robert Mika]

Insted of nested IF you can try to use VLOOKUP:
http://www.mrexcel.com/forum/showthread.php?t=620450