Forum Discussion

markdoc's avatar
markdoc
Copper Contributor
Jul 10, 2023
Solved

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 a2 <101 and a3 =150 then 9500

if a2 <101 and a3 >150 then 9500+2000+2500

if a2 >100 and a3 <137 then 7500/100*a2

if a2 >100 and a3 =150 then 9500/100*a2

if a2 >100 and a3 >150 then (9500+2000+2500)/100*a2

Here is what I've come up with:

=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))

 

However, I keep getting a "value not available" error and I can't figure out what I'm missing. Any help would be greatly appreciated!

 

Thanks,
Mark

excel
  • SergeiBaklan's avatar
    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

  • mtarler's avatar
    mtarler
    Silver Contributor
    Jul 12, 2023

    markdoc Another alternative below but first what happens if A3 is between 137 and 150?

     

    =IFS(a3<137, 7500, a3=150, 9500, a3>150, 9500+2000+2500) / if (a2 <101, 1, 100*a2)
    • markdoc's avatar
      markdoc
      Copper Contributor
      Jul 17, 2023
      Thanks for your feedback. Based on our internal data, the number would never fall between 137 and 150.
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    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

  • leoperdia's avatar
    leoperdia
    Brass Contributor
    Jul 11, 2023
    Hi ! This formula should works....

    =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);7500/100*$A$2;IF(AND($A$2>100;$A$3>150);(9500+2000+2500)/100*$A$2;"No value"))))))

    Regards
    https://www.upwork.com/freelancers/~01cf0fc8446b00f44c

Resources