Formula to Count Symbols in Health column and Formula to Average between the Symbol collors

bobbyf

I am looking to add a couple formulas to the Sheet Summary or a cell in the sheet. Formula 1 - that counts if cell symbol = Red, Green, or Yellow. So I get a total number of red symbols, green symbols etc. , Formula 2 - Calculate the avg of the symbols to determine if symbols in one column average to Red, Yellow or Green based on total of all symbols.

Just not sure how to get if the overall health of the plan is red, yellow or green based on the average of all health symbols of each task.

Paul Newcome

Where exactly are you planning to put these formulas?

Brian Wilson DC

@bobbyf Since there is the possibility that you could end up with an equal number of Reds to Yellows to Greens, you won't be able to index, match, or collect a top value and be assured that there isn't another value that meets the same criteria. For example, if you have 5 total entries and one is red and the other two are split equally amongst yellow and green, you will return values of 20%, 40% and 40%. An index function would stop at the first value that is that meets your criteria. My advice to work around this would be to have 3 columns with three rows in each of them to generate your results, OR, to use the summary tab setup with the same information.

For the column function I would create something like below:

"Symbol Totals" - Just Text Entry for the name of the colors

"Symbol Value"

=COUNTIF(Symbol:Symbol, ="Red")

=COUNTIF(Symbol:Symbol, ="Yellow")

=COUNTIF(Symbol:Symbol, ="Green")

"Percent of Health Plan"

=[Symbol Value]@row / SUM([Symbol Value]:[Symbol Value])

You could easily put this same information into the summary tab, but you'd have to reference the specific cell not "@row" as I've done above.

So your "Percent of Health Plan" would need to be changed to a percentage format and use three separate statements that read :

=[Symbol Value]1 / SUM([Symbol Value]:[Symbol Value])

=[Symbol Value]2 / SUM([Symbol Value]:[Symbol Value])

=[Symbol Value]3 / SUM([Symbol Value]:[Symbol Value])

That would produce this:

I hope this helps!

-Brian