Nested IF/AND/OR Statement

I'm trying to create a nested statement and I seem to be missing something that keeps it from working properly. I'm trying to combine the following statements: if a2 <101 and a3 <137 then 7500 if a...

excel

SergeiBaklan

Jul 12, 2023

markdoc

As variant

=IF(
    A2 < 101,
    7500 +
        IF( A3 = 150, 2000,
            IF(A3 > 150, 6500, 0)),
    (7500 +
        IF( A3 = 150, 2000,
            IF(A3 > 150, 6500, 0))) /
        100 * A2
)

If with IFS() your formula

=IFS(
    IF(AND($A$2 < 101, $A$3 < 137), 7500),
    IF(AND($A$2 < 101, $A$3 = 150), 9500),
    IF(AND($A$2 < 101, $A$3 > 150), 9500 + 2000 + 2500),
    IF(
        AND($A$2 > 100, $A$3 < 137),
        (7500 / 100) * $A$2
    ),
    IF(
        AND($A$2 > 100, $A$3 = 150),
        (9500 / 100) * $A$2
    ),
    IF(
        AND($A$2 > 100, $A$3 > 150),
        (9500 + 2000 + 2500) / 100 * $A$2
    )
)

could be corrected as

=IFS(
    AND($A$2 < 101, $A$3 < 137), 7500,
    AND($A$2 < 101, $A$3 = 150), 9500,
    AND($A$2 < 101, $A$3 > 150), 9500 + 2000 + 2500,
    AND($A$2 > 100, $A$3 < 137), (7500 / 100) * $A$2,
    AND($A$2 > 100, $A$3 = 150), (9500 / 100) * $A$2,
    AND($A$2 > 100, $A$3 > 150), (9500 + 2000 + 2500) / 100 * $A$2
)

when it works

Forum Discussion

Nested IF/AND/OR Statement

Resources