Multiple nested IFs + Ands

Welcome to the community, @Ximena_Duray! :grinning_face_with_big_eyes: You provided a lot of detail in your post, but the only thing you indicated about your problem is that the formula doesn’t work for the “Content Demand Team” option. You didn’t say exactly what’s not working. It’s difficult to know how to dig into the formula to find the problem when we don’t know the specifics of the problem. :slightly_smiling_face:

There are also ways you could optimize your formula a bit, but I’ll hold off on that until we get the bigger problem addressed.

You are right! If the formula was working I would expect to obtain a result on a column but in the case of “Content Demand Team” nothing is shown, the column is empty even when I complete all fields to obtain a result. So there is no message an error has occurred but something is not working.

I’m guessing that the {Department} field is a single-select. If so, the first thing to check is that you don’t have an accidental space at the beginning or end of the “Content Demand” entry. That would mean it wouldn’t match in the formula, and none of the other tests would be run.

The next thing I’m curious about are the various other fields you’re checking, where you look to see if they’re either 0 or 1. Are they all checkbox fields, or are they a different field type?

Yes, I have several checkboxes that are checked only if the person wants to consider the category of that field into the result. For example, if the persons want to take in count only 2 of the 4 possible values to do the average that is possible due to those checkboxes that are activated when they need to be taken account in the average of left out.

I checked for the possible accidental space, unfortunately that wasn’t the problem :cry:

Glad to hear that you found the issue! :thumbs_up:

To get back to the optimizations I hinted at earlier, the main one that comes to mind is to change your testing system from a series of nested IF() functions into a SWITCH() function. The core test you’re doing is based on what’s in the {Department} field. With the SWITCH() function, you only provide the test criteria once, and it’s often the value in a field. After that, you provide pairs of things: a possible result of that test, and what to do if that result is found. Here’s a simple example. First the IF() version:

IF(Status = "Pending", "Output A",
IF(Status = "In-Progress", "Output B",
IF(Status = "Complete", "Output C")))

And now the same thing with SWITCH():

SWITCH(Status,
    "Pending", "Output A",
    "In-Progress", "Output B",
    "Complete", "Output C"
)

The SWITCH() function also lets you specify a default output if nothing else matches. In your case, because you’ve got seven departments that all want to see the basic {What} output, you can retool your formula to make {What} the default output for every department except those that want something custom. That instantly takes a lot of meat out of your formula.

With those optimizations in place, we now have this:

SWITCH(Department,
    "Motion Graphics",
        IF({Motion To Publish}=1,({%Project}+{%Q&A}),(70+{%Q&A})),
    "Graphic Design",
        IF(AND({Did SC}=1,{Did Assets}=1), AVERAGE({Assets Performance %}, {SC Performance %}),
    	IF(AND({Did SC}=1,{Did Assets}=0),{SC Performance %},
	    IF(AND({Did SC}=0,{Did Assets}=1),{Assets Performance %}))),
    "Content Demand",
        IF(AND({Roadmap}=1,{Reporte Continuo}=1,{Pry Análisis}=1, {User Voice}=1),
            AVERAGE({%UV}, {%Pry Análisis}, {%RC},{%Roadmap}),
        IF(AND({Roadmap}=1,{Reporte Continuo}=0,{Pry Análisis}=0, {User Voice}=0),
                {%Roadmap},
        IF(AND({Roadmap}=0,{Reporte Continuo}=1,{Pry Análisis}=0, {User Voice}=0),
               {%RC},
        IF(AND({Roadmap}=0,{Reporte Continuo}=0,{Pry Análisis}=1, {User Voice}=0),
            {%Pry Análisis},
        IF(AND({Roadmap}=0,{Reporte Continuo}=0,{Pry Análisis}=0, {User Voice}=1),
            {%UV},
        IF(AND({Roadmap}=1,{Reporte Continuo}=1,{Pry Análisis}=0, {User Voice}=0),
            AVERAGE({%RC},{%Roadmap}),
        IF(AND({Roadmap}=1,{Reporte Continuo}=0,{Pry Análisis}=1, {User Voice}=0),
            AVERAGE({%Pry Análisis}, {%Roadmap}),
        IF(AND({Roadmap}=1,{Reporte Continuo}=0,{Pry Análisis}=0, {User Voice}=1),
            AVERAGE({%UV},{%Roadmap}),
        IF(AND({Roadmap}=0,{Reporte Continuo}=1,{Pry Análisis}=1, {User Voice}=0),
            AVERAGE( {%Pry Análisis}, {%RC}),
        IF(AND({Roadmap}=0,{Reporte Continuo}=1,{Pry Análisis}=0, {User Voice}=1),
            AVERAGE({%UV}, {%RC}),
        IF(AND({Roadmap}=0,{Reporte Continuo}=0,{Pry Análisis}=1, {User Voice}=1),
            AVERAGE({%UV}, {%Pry Análisis}),
        IF(AND({Roadmap}=0,{Reporte Continuo}=1,{Pry Análisis}=1, {User Voice}=1),
            AVERAGE({%UV}, {%Pry Análisis}, {%RC}),
        IF(AND({Roadmap}=1,{Reporte Continuo}=0,{Pry Análisis}=1, {User Voice}=1),
            AVERAGE({%UV},{%Pry Análisis},{%Roadmap}),
        IF(AND({Roadmap}=1,{Reporte Continuo}=1,{Pry Análisis}=0, {User Voice}=1),
            AVERAGE({%UV},{%RC},{%Roadmap}),
        IF(AND({Roadmap}=1,{Reporte Continuo}=1,{Pry Análisis}=1, {User Voice}=0),
            AVERAGE({%Pry Análisis},{%RC},{%Roadmap})
    ))))))))))))))),
    What
)

But wait…there’s more! :slightly_smiling_face:

If you want to get crazy about optimizing this formula, you can optimize the testing of the numerous options for “Content Demand”. Whenever I’m optimizing, I look for patterns, and I see a really clear pattern here: you’re including specific values in your AVERAGE() formula based on which related boxes are checked. Here’s what’s really fun: you can write a single AVERAGE() function that does just that. I also noticed a similar pattern in the “Graphic Design” category that I didn’t catch before, and that can have the same optimization applied.

Finally, because it’s possible for {Department} to be blank, and you probably want a blank output, I added one more test for the SWITCH() function to take care of that.

Here’s your formula with all of those changes:

SWITCH(Department,
    "Motion Graphics",
        IF({Motion To Publish}=1, {%Project}+{%Q&A}, 70+{%Q&A}),
    "Graphic Design",
        AVERAGE(
            IF({Did SC}, {SC Performance %}),
            IF({Did Assets}, {Assets Performance %})
        ),
    "Content Demand",
        AVERAGE(
            IF({Roadmap}, {%Roadmap}),
            IF({Reporte Continuo}, {%RC}),
            IF({Pry Análisis}, {%Pry Análisis}),
            IF({User Voice}, {%UV})
        ),
    "", BLANK(),
    What
)

That’s it! :grinning_face_with_big_eyes:

Here’s what’s going on with those AVERAGE() functions. If a given checkbox is checked, its related field value is included in the average. If it’s not checked, the AVERAGE() function doesn’t get zero for that item, it gets nothing. If you check three out of four boxes related to the “Content Demand” group, it only averages those three values and completely ignores the fourth. If you check just one box, it averages that one related value, effectively passing it through unmodified because it’s the only value. This function is really smart that way, and I only discovered this while writing this update and running some tests.

WOW @Justin_Barrett this is so cool! :exploding_head: :star2: I never thought that could be optimized like that! Thank you very much for your help I will put that into practice :grinning_face_with_big_eyes: