Forum Discussion

mrkitt's avatar
mrkitt
Copper Contributor
Apr 27, 2021

Advanced If Formula Help

Hi how can I make this  formula work ? Currently itshowing error

 

=(IF(E12="Handbags"),(E13>4994),(E13*0.2),IF((E13>995),(E13*0.3),IF((E13>145),(E13*0.5),IF((E13<146),(E13*0.6)))), (IF(E12="ACCESSORIES",((E13>145),(E13*0.5),IF((E13<146),(E13*0.6)))))),(IF(E12="HOME ITEMS"), ((E13>995),(E13*0.3),IF((E13>145),(E13*0.5),IF((E13<146),(E13*0.6)))))),(IF(E12="WATCHES"), ((E13>2494),(E13*0.2),IF((E13>995),(E13*0.3),IF((E13>145),(E13*0.5),IF((E13<146),(E13*0.6)))))),(IF(E12="JEWELRY", ((E13>995),(E13*0.3),IF((E13>145),(E13*0.5),IF((E13<146),(E13*0.6)))))

excel
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Apr 27, 2021

    mrkitt What exactly do you want to achieve here? The formula doesn't make sense.

    For instance, the part "IF(E12="Handbags"),(E13>4994),(E13*0.2),". This checks if E12 contains "Handbags". If so, determine if E13 is greater than 4994 (True or False) otherwise multiply E12 by 0.2. And then you have more IF statements following it. The syntax is simply wrong.

     

    I suspect that you want to check if E12 = "Handbags" AND that the value in E13 > 4994, then multiply E13 by 0.2, otherwise evaluate the next IF statement. If so, you need to use the AND function and carefully place the parentheses in the correct places. Without your file, though, it's difficult to come up with the correct formula.

  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Apr 27, 2021

    mrkitt 

    I guess that is something like

    =IF(E12="Handbags",    LOOKUP(E13, {0,145,995,4995},{0.6,0.5,0.3,0.2}),
 IF(E12="ACCESSORIES", LOOKUP(E13, {0,145},{0.6,0.5,}),
 IF(E12="HOME ITEMS",  LOOKUP(E13, {0,145,995},{0.6,0.5,0.3}),
 IF(E12="WATCHES",     LOOKUP(E13, {0,145,995,2495},{0.6,0.5,0.3,0.2}),
 IF(E12="JEWELRY",     LOOKUP(E13, {0,145,995},{0.6,0.5,0.3}),
    "no match")))))

Resources