This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.
Accept
Reject
Preferences
Help

Re: multiple nesting IF formulas

Solved
Jump to Solution
1400 1
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
akt
4 - Data Explorer
4 - Data Explorer
See Solution in Thread

‎Jan 10, 2023 02:11 PM

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))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))
Preview file
259 KB
0 Kudos
Reply
1 Solution

Accepted Solutions
Greg_F
9 - Sun
9 - Sun
Solved See Solution in Thread

‎Jan 10, 2023 04:43 PM

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

Reply
5 Replies 5
AirBenderMarcus
7 - App Architect
7 - App Architect
See Solution in Thread

‎Jan 10, 2023 03:47 PM - edited ‎Jan 10, 2023 04:11 PM

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.

0 Kudos
Reply
Greg_F
9 - Sun
9 - Sun
Solved See Solution in Thread

‎Jan 10, 2023 04:43 PM

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?

 

 

 

 

Reply
Steve_Haysom
8 - Airtable Astronomer
8 - Airtable Astronomer
See Solution in Thread

‎Jan 11, 2023 12:41 AM

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.

0 Kudos
Reply
akt
4 - Data Explorer
4 - Data Explorer
In response to Steve_Haysom
See Solution in Thread

‎Jan 11, 2023 07:09 AM

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

0 Kudos
Reply
AirBenderMarcus
7 - App Architect
7 - App Architect
In response to Greg_F
See Solution in Thread

‎Jan 11, 2023 07:36 AM

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

0 Kudos
Reply
Copyright © 2024 Khoros, LLC