Is there a way to create 'dynamic' formulas?

Alan Rappa · 08/13/20

As I'm sure all of you deal with every day, my stakeholders are constantly changing their minds about what they want to appear on our dashboards.

No biggie, I just go and modify the formulas, and all is good... until the next change... and the next change.. and the next change...

So, I started to wonder if I could use a combination of IF statements and checkboxes to create more dynamic formulas to avoid having to modify them day after day.

Here is an example formula I use on my Dashboard Metrics Page.

=SUMIFS({Investment}, {Area}, [Metric 1]@row, {Target Completion Q}, "2020-Q1", {Cancelled, Hold, Backlogged}, <>1, {Team_check}, <>1, {Risk-Loss}, <>1)

This sums the investment column, when the area = X, and the Target Completion Q is 2020-Q1, the project is not cancelled, on hold, or backlogged, and team_check is NOT checked and RIsk-Loss is not checked.

This evening, after a day of meetings, one of my stakeholders said they want to show all Risk-Loss items, meaning regardless if the box is checked or unchecked.

That means I need to go into my metrics page, and update all the formulas to disregard that IF.

There has to be a better way, right? I was thinking, that if I could just have some checkboxes on my metrics page that if checked included the appropriate criteria in the formula.

For example, I could have a column called "Include Risk Loss", and when the box is checked in that column, the "{Risk-Loss}, <>1" criteria gets added.

Perhaps there is another way to accomplish this? Anything to save me from making this change tonight only to have to reverse it again in the morning :)

I look forward to / appreciate your thoughts and input.

thanks,

-Alan

(Ps - I hope my stakeholders don't even pop into the forumns here :)

pps - trying to edit this to include my line breaks. Apologies for the giant, wall of text

David Joyeuse · 08/13/20

Hi @Alan Rappa

Well, If I'm understanding you correctly you don't have much choice.

What you'll have to do is to breakdown all the SUMIFS in mini SUMIFS so this read like this:

=SUMIFS({Investment}, {Area}, [Metric 1]@row, {Area}, {Area checked}) + SUMIFS({Investment},{Target Completion Q}, "2020-Q1",{Target Completion Q},{Target Completion Q Checked}) + SUMIFS...

It's quite cumbersome and boring.

Another option is to have an hidden sheet (hidden to your stakeholders that is). Where you already calculate them separately.

=SUMIFS({Investment}, {Area}, [Metric 1]@row) on row one

=SUMIFS({Investment},{Target Completion Q}, "2020-Q1") on row two

and so on until you're done with SUMIFS

On column two, make it a checkbox type only.

Then on their dashboard display this formula:

=SUMIFS([Column One]:[Column One], [Column Two]@row,1)

Then checking or unchecking the checkbox will remove the SUMIFS from the last SUMIFS.

It will also saves you time in the future when they'll have for 2021 - Q3 or whatever.

Hope it helped!

David Joyeuse · 08/13/20