Re: multiple nesting IF formulas

akt · ‎Jan 10, 2023

hi! trying to get a nested if formula to work based on market and the only country that is pulling is the first (US) but not FS. if i switch "US" to "FS" for example, it will pull into the FS market and US. it seems to be an issue with the conditional statement finding a match and then stopping. any idea how would i fix this?

IF(Market="US",

IF(Channel="Natural Search",0.59

,IF(AND(Channel="SEM Brand text",Quarter="Q1"),0.1

,IF(AND(Channel="SEM Brand text",Quarter="Q2"),0.1

,IF(AND(Channel="SEM Brand text",Quarter="Q3"),0.1

,IF(AND(Channel="SEM Brand text",Quarter="Q4"),0.1

,IF(AND(Channel="SEM Nonbrand text",Quarter="Q1"),2.12

,IF(AND(Channel="SEM Nonbrand text",Quarter="Q2"),1.92

,IF(AND(Channel="SEM Nonbrand text",Quarter="Q3"),1.7

,IF(AND(Channel="SEM Nonbrand text",Quarter="Q4"),1.7

,IF(AND(Channel="SEM PLA",Quarter="Q1"),1.55

,IF(AND(Channel="SEM PLA",Quarter="Q2"),1.47

,IF(AND(Channel="SEM PLA",Quarter="Q3"),1.18

,IF(AND(Channel="SEM PLA",Quarter="Q4"),1.18

,IF(AND(Channel="Affiliates",Quarter="Q1"),0.41

,IF(AND(Channel="Affiliates",Quarter="Q2"),0.49

,IF(AND(Channel="Affiliates",Quarter="Q3"),0.36

,IF(AND(Channel="Affiliates",Quarter="Q4"),0.36

,IF(AND(Channel="Display",Quarter="Q1"),4.2

,IF(AND(Channel="Display",Quarter="Q2"),2.4

,IF(AND(Channel="Display",Quarter="Q3"),3.88

,IF(AND(Channel="Display",Quarter="Q4"),3.88

,IF(AND(Channel="Paid Social CVR",Quarter="Q1"),1.57

,IF(AND(Channel="Paid Social CVR",Quarter="Q2"),1.34

,IF(AND(Channel="Paid Social CVR",Quarter="Q3"),1.07

,IF(AND(Channel="Paid Social CVR",Quarter="Q4"),1.07

,IF(AND(Channel="Paid Social CON",Quarter="Q1"),1.57

,IF(AND(Channel="Paid Social CON",Quarter="Q2"),1.34

,IF(AND(Channel="Paid Social CON",Quarter="Q3"),1.07

,IF(AND(Channel="Paid Social CON",Quarter="Q4"),1.07

,IF(AND(Channel="Paid Social AWE",Quarter="Q1"),1.57

,IF(AND(Channel="Paid Social AWE",Quarter="Q2"),1.34

,IF(AND(Channel="Paid Social AWE",Quarter="Q3"),1.07

,IF(AND(Channel="Paid Social AWE",Quarter="Q4"),1.07,

IF(Market="FS",

IF(Channel="Natural Search",0.59

,IF(AND(Channel="SEM Brand text",Quarter="Q1"),0.1

,IF(AND(Channel="SEM Brand text",Quarter="Q2"),0.1

,IF(AND(Channel="SEM Brand text",Quarter="Q3"),0.1

,IF(AND(Channel="SEM Brand text",Quarter="Q4"),0.1

,IF(AND(Channel="SEM Nonbrand text",Quarter="Q1"),2.39

,IF(AND(Channel="SEM Nonbrand text",Quarter="Q2"),1.66

,IF(AND(Channel="SEM Nonbrand text",Quarter="Q3"),1.7

,IF(AND(Channel="SEM Nonbrand text",Quarter="Q4"),1.41

,IF(AND(Channel="SEM PLA",Quarter="Q1"),1.4

,IF(AND(Channel="SEM PLA",Quarter="Q2"),1.3

,IF(AND(Channel="SEM PLA",Quarter="Q3"),1.48

,IF(AND(Channel="SEM PLA",Quarter="Q4"),1.34

,IF(AND(Channel="Affiliates",Quarter="Q1"),0.56

,IF(AND(Channel="Affiliates",Quarter="Q2"),0.36

,IF(AND(Channel="Affiliates",Quarter="Q3"),0.52

,IF(AND(Channel="Affiliates",Quarter="Q4"),0.7

,IF(AND(Channel="Display",Quarter="Q1"),3.39

,IF(AND(Channel="Display",Quarter="Q2"),3.81

,IF(AND(Channel="Display",Quarter="Q3"),2.35

,IF(AND(Channel="Display",Quarter="Q4"),1.74

,IF(AND(Channel="Paid Social CVR",Quarter="Q1"),1.23

,IF(AND(Channel="Paid Social CVR",Quarter="Q2"),1.36

,IF(AND(Channel="Paid Social CVR",Quarter="Q3"),1.62

,IF(AND(Channel="Paid Social CVR",Quarter="Q4"),1.92

,IF(AND(Channel="Paid Social CON",Quarter="Q1"),1.23

,IF(AND(Channel="Paid Social CON",Quarter="Q2"),1.36

,IF(AND(Channel="Paid Social CON",Quarter="Q3"),1.62

,IF(AND(Channel="Paid Social CON",Quarter="Q4"),1.92

,IF(AND(Channel="Paid Social AWE",Quarter="Q1"),1.23

,IF(AND(Channel="Paid Social AWE",Quarter="Q2"),1.36

,IF(AND(Channel="Paid Social AWE",Quarter="Q3"),1.62

,IF(AND(Channel="Paid Social AWE",Quarter="Q4"),1.92))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

Greg_F · ‎Jan 10, 2023

Hi @akt,

That is one hardcore nested formula... Would it be simpler to see and maintain it if changed to SWITCH?

It would be something like

SWITCH(Market&Channel&Quarter , "USNatural Search" , 0.59 , "USSEM Brand textQ1" , 0.1 , ...)

OK and now 🥁...

I have asked Chat GPT to convert your formula to format I proposed above 😁 here is the result:

SWITCH(Market&Channel&Quarter, 
"USNatural Search", 0.59, 
"USSEM Brand textQ1", 0.1, 
"USSEM Brand textQ2", 0.1, 
"USSEM Brand textQ3", 0.1, 
"USSEM Brand textQ4", 0.1, 
"USSEM Nonbrand textQ1", 2.12, 
"USSEM Nonbrand textQ2", 1.92, 
"USSEM Nonbrand textQ3", 1.7, 
"USSEM Nonbrand textQ4", 1.7, 
"USSEM PLAQ1", 1.55, 
"USSEM PLAQ2", 1.47, 
"USSEM PLAQ3", 1.18, 
"USSEM PLAQ4", 1.18, 
"USAffiliatesQ1", 0.41, 
"USAffiliatesQ2", 0.49, 
"USAffiliatesQ3", 0.36, 
"USAffiliatesQ4", 0.36, 
"USDisplayQ1", 4.2, 
"USDisplayQ2", 2.4, 
"USDisplayQ3", 3.88, 
"USDisplayQ4", 3.88, 
"USPaid Social CVRQ1", 1.57, 
"USPaid Social CVRQ2", 1.34, 
"USPaid Social CVRQ3", 1.07, 
"USPaid Social CVRQ4", 1.07, 
"USPaid Social CONQ1", 1.57, 
"USPaid Social CONQ2", 1.34, 
"USPaid Social CONQ3", 1.07, 
"USPaid Social CONQ4", 1.07, 
"USPaid Social AWEQ1", 1.57, 
"USPaid Social AWEQ2", 1.34, 
"USPaid Social AWEQ3", 1.07, 
"USPaid Social AWEQ4", 1.07, 
"FSNatural Search", 0.59, 
"FSSEM Brand textQ1", 0.1, 
"FSSEM Brand textQ2", 0.1, 
"FSSEM Brand textQ3", 0.1, 
"FSSEM Brand textQ4", 0.1, 
"FSSEM Nonbrand textQ1", 2.39, 
"FSSEM Nonbrand textQ2", 1.66, 
"FSSEM Nonbrand textQ3", 1.7, 
"FSSEM Nonbrand textQ4", 1.41, 
"FSSEM PLAQ1", 1.4, 
"FSSEM PLAQ2", 1.3, 
"FSSEM PLAQ3", 1.48, 
"FSSEM PLAQ4", 1.34, 
"FSAffiliatesQ1", 0.56, 
"FSAffiliatesQ2", 0.36, 
"FSAffiliatesQ3", 0.52, 
"FSAffiliatesQ4", 0.7, 
"FSDisplayQ1", 3.39, 
"FSDisplayQ2", 3.81, 
"FSDisplayQ3", 2.35, 
"FSDisplayQ4", 1.74, 
"FSPaid Social CVRQ1", 1.23, 
"FSPaid Social CVRQ2", 1.36, 
"FSPaid Social CVRQ3", 1.62, 
"FSPaid Social CVRQ4", 1.92, 
"FSPaid Social CONQ1", 1.23, 
"FSPaid Social CONQ2", 1.36, 
"FSPaid Social CONQ3", 1.62, 
"FSPaid Social CONQ4", 1.92, 
"FSPaid Social AWEQ1", 1.23, 
"FSPaid Social AWEQ2", 1.36, 
"FSPaid Social AWEQ3", 1.62, 
"FSPaid Social AWEQ4", 1.92)

The GPT conversion looks roughly correct, but I would double check the numbers in detail as it sometimes tends to do wacky spelling errors.

Let me know if that solves your issue?

See Solution in Thread

AirBenderMarcus · ‎Jan 10, 2023

Hey. If you remove the US section of IF statements, does the FS part work properly? You could try the Switch function which makes the formula easier to parse and should also solve your issue.

SWITCH(Market,
    "US", 
        IF(Channel="Natural Search",0.59,
        IF(AND(Channel="SEM Brand text",Quarter="Q1"),0.1,
        IF(AND(Channel="SEM Brand text",Quarter="Q2"),0.1,
        IF(AND(Channel="SEM Brand text",Quarter="Q3"),0.1,
        IF(AND(Channel="SEM Brand text",Quarter="Q4"),0.1,
        IF(AND(Channel="SEM Nonbrand text",Quarter="Q1"),2.12,
        IF(AND(Channel="SEM Nonbrand text",Quarter="Q2"),1.92,
        IF(AND(Channel="SEM Nonbrand text",Quarter="Q3"),1.7,
        IF(AND(Channel="SEM Nonbrand text",Quarter="Q4"),1.7,
        IF(AND(Channel="SEM PLA",Quarter="Q1"),1.55,
        IF(AND(Channel="SEM PLA",Quarter="Q2"),1.47,
        IF(AND(Channel="SEM PLA",Quarter="Q3"),1.18,
        IF(AND(Channel="SEM PLA",Quarter="Q4"),1.18,
        IF(AND(Channel="Affiliates",Quarter="Q1"),0.41,
        IF(AND(Channel="Affiliates",Quarter="Q2"),0.49,
        IF(AND(Channel="Affiliates",Quarter="Q3"),0.36,
        IF(AND(Channel="Affiliates",Quarter="Q4"),0.36,
        IF(AND(Channel="Display",Quarter="Q1"),4.2,
        IF(AND(Channel="Display",Quarter="Q2"),2.4,
        IF(AND(Channel="Display",Quarter="Q3"),3.88,
        IF(AND(Channel="Display",Quarter="Q4"),3.88,
        IF(AND(Channel="Paid Social CVR",Quarter="Q1"),1.57,
        IF(AND(Channel="Paid Social CVR",Quarter="Q2"),1.34,
        IF(AND(Channel="Paid Social CVR",Quarter="Q3"),1.07,
        IF(AND(Channel="Paid Social CVR",Quarter="Q4"),1.07,
        IF(AND(Channel="Paid Social CON",Quarter="Q1"),1.57,
        IF(AND(Channel="Paid Social CON",Quarter="Q2"),1.34,
        IF(AND(Channel="Paid Social CON",Quarter="Q3"),1.07,
        IF(AND(Channel="Paid Social CON",Quarter="Q4"),1.07,
        IF(AND(Channel="Paid Social AWE",Quarter="Q1"),1.57,
        IF(AND(Channel="Paid Social AWE",Quarter="Q2"),1.34,
        IF(AND(Channel="Paid Social AWE",Quarter="Q3"),1.07,
        IF(AND(Channel="Paid Social AWE",Quarter="Q4"),1.07))))))))))))))))))))))))))))))))),
    "FS", 
        IF(Channel="Natural Search",0.59,
        IF(AND(Channel="SEM Brand text",Quarter="Q1"),0.1,
        IF(AND(Channel="SEM Brand text",Quarter="Q2"),0.1,
        IF(AND(Channel="SEM Brand text",Quarter="Q3"),0.1,
        IF(AND(Channel="SEM Brand text",Quarter="Q4"),0.1,
        IF(AND(Channel="SEM Nonbrand text",Quarter="Q1"),2.39,
        IF(AND(Channel="SEM Nonbrand text",Quarter="Q2"),1.66,
        IF(AND(Channel="SEM Nonbrand text",Quarter="Q3"),1.7,
        IF(AND(Channel="SEM Nonbrand text",Quarter="Q4"),1.41,
        IF(AND(Channel="SEM PLA",Quarter="Q1"),1.4,
        IF(AND(Channel="SEM PLA",Quarter="Q2"),1.3,
        IF(AND(Channel="SEM PLA",Quarter="Q3"),1.48,
        IF(AND(Channel="SEM PLA",Quarter="Q4"),1.34,
        IF(AND(Channel="Affiliates",Quarter="Q1"),0.56,
        IF(AND(Channel="Affiliates",Quarter="Q2"),0.36,
        IF(AND(Channel="Affiliates",Quarter="Q3"),0.52,
        IF(AND(Channel="Affiliates",Quarter="Q4"),0.7,
        IF(AND(Channel="Display",Quarter="Q1"),3.39,
        IF(AND(Channel="Display",Quarter="Q2"),3.81,
        IF(AND(Channel="Display",Quarter="Q3"),2.35,
        IF(AND(Channel="Display",Quarter="Q4"),1.74,
        IF(AND(Channel="Paid Social CVR",Quarter="Q1"),1.23,
        IF(AND(Channel="Paid Social CVR",Quarter="Q2"),1.36,
        IF(AND(Channel="Paid Social CVR",Quarter="Q3"),1.62,
        IF(AND(Channel="Paid Social CVR",Quarter="Q4"),1.92,
        IF(AND(Channel="Paid Social CON",Quarter="Q1"),1.23,
        IF(AND(Channel="Paid Social CON",Quarter="Q2"),1.36,
        IF(AND(Channel="Paid Social CON",Quarter="Q3"),1.62,
        IF(AND(Channel="Paid Social CON",Quarter="Q4"),1.92,
        IF(AND(Channel="Paid Social AWE",Quarter="Q1"),1.23,
        IF(AND(Channel="Paid Social AWE",Quarter="Q2"),1.36,
        IF(AND(Channel="Paid Social AWE",Quarter="Q3"),1.62,
        IF(AND(Channel="Paid Social AWE",Quarter="Q4"),1.02))))))))))))))))))))))))))))))))),
)

The above is a slight reformatting of what you originally shared, but there are ways we can optimize this even further depending on your use case to reduce the lines of code. For instance, if these numbers will never change you can combine SEM Brand text into a single statement since the value will always be 0.1 regardless of the market or quarter.

Greg_F · ‎Jan 10, 2023