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.
