multiple nesting IF formulas | Airtable Community

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

Page 1 / 1

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.

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?

Would it be clearer if you split the formula into 2 or more separate formula fields? Then you could test each part on it's own.

amazing, this fixed it and it worked perfectly! thank you so much!

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?

That's cool. I had no idea you could concatenate the first argument like that. That's a game changer for me.

Reply

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded