Nested If function not working
- Thread starter cashby
- Start date
Hello Cashby,
Your nested IF is working syntactically; it’s the logic/order that’s wrong. Right now, you test the lowest tier first (M15 = 12,522). Since E15 (66,422 in your sheet) is already greater than 12,522, Excel returns N15 immediately (4%) and never reaches the higher tiers (5%, 6%).
Fix 1: Keep Nested IF, but check from highest to lowest
=IF(E15>$M$17,$N$17,
IF(E15>$M$16,$N$16,
IF(E15>$M$15,$N$15,0)))
(Use the highest threshold first, then move down.)
If you want the threshold itself to qualify, use >= instead of >.
Fix 2 (Best/Cleanest): Use LOOKUP (tiers must be sorted ascending)
If M15:M17 are your tier thresholds in ascending order, this is much simpler:
=LOOKUP(E15,$M$15:$M$18,$N$15:$N$18)
This returns the payout for the largest tier that is ≤ E15 (so 66,422 will return 6%).
Quick checks:
Your nested IF is working syntactically; it’s the logic/order that’s wrong. Right now, you test the lowest tier first (M15 = 12,522). Since E15 (66,422 in your sheet) is already greater than 12,522, Excel returns N15 immediately (4%) and never reaches the higher tiers (5%, 6%).
Fix 1: Keep Nested IF, but check from highest to lowest
=IF(E15>$M$17,$N$17,
IF(E15>$M$16,$N$16,
IF(E15>$M$15,$N$15,0)))
(Use the highest threshold first, then move down.)
If you want the threshold itself to qualify, use >= instead of >.
Fix 2 (Best/Cleanest): Use LOOKUP (tiers must be sorted ascending)
If M15:M17 are your tier thresholds in ascending order, this is much simpler:
=LOOKUP(E15,$M$15:$M$18,$N$15:$N$18)
This returns the payout for the largest tier that is ≤ E15 (so 66,422 will return 6%).
Quick checks:
- Make sure M15:M18 are real numbers (not stored as text).
- Keep the tier values in M sorted from smallest → largest (LOOKUP needs that).
JORGE W: ROSERO
New member
MUY buenas tardes, también podríamos usar nombres y fórmulas comparativas:
Y(E4>=MONT1;E4<MONT2)*PORC1+Y(E4>=MONT2;E4<MONT3)*PORC2+Y(E4>=MONT3;E4<MONT4)*PORC3+Y(E4>=MONT4)*PORC4
Y(E4>=MONT1;E4<MONT2)*PORC1+Y(E4>=MONT2;E4<MONT3)*PORC2+Y(E4>=MONT3;E4<MONT4)*PORC3+Y(E4>=MONT4)*PORC4
Hello JORGE W. ROSERO,
Thanks for sharing your suggestion. Using a nested IF formula can definitely help handle multiple conditions in this case. Just be careful with the structure and parentheses, since a small mistake there can cause the formula not to work properly.
In many cases, the issue with nested IF formulas happens because:
Appreciate your input in helping solve the problem.
Thanks for sharing your suggestion. Using a nested IF formula can definitely help handle multiple conditions in this case. Just be careful with the structure and parentheses, since a small mistake there can cause the formula not to work properly.
In many cases, the issue with nested IF formulas happens because:
- A parenthesis is missing
- The logical tests are not ordered correctly
- Or the formula becomes too complex to manage
Appreciate your input in helping solve the problem.