Rollup or Find Average Progress Symbol

Tab

I am trying to rollup from another sheet that has the progress displaying as symbols. I created a sheet that rolls up hours, costs, dates, and progress. What would be the correct formula to rollup progress using symbols from another sheet? So, finding the average on progress as a symbol.

Genevieve P.

Hi @Tab

Do you want to output to be in symbol format or in a percent format?

The way I would do this is assign the appropriate percentage based on the progress symbol. So, a "Quarter" symbol is 0.25.

=COUNTIF({Progress Column}, "Quarter") * 0.25

See: COUNTIF Function and Cross-sheet formulas. Do this for each of the Symbols and add them together:

=(COUNTIF({Progress Column}, "Quarter") * 0.25 + COUNTIF({Progress Column}, "Half") * 0.5 + COUNTIF({Progress Column}, "Three Quarter") * 0.75 + COUNTIF({Progress Column}, "Full"))

This will give you a SUM of all the values. Then you can take this and divide it by the number of values to get your Average, including the 0 ones:

=(COUNTIF({Progress Column}, "Quarter") * 0.25 + COUNTIF({Progress Column}, "Half") * 0.5 + COUNTIF({Progress Column}, "Three Quarter") * 0.75 + COUNTIF({Progress Column}, "Full")) / COUNT({Progress Column})

The output of this will be a decimal that you can turn into a % by formatting that cell as Percent from the toolbar. Will this work for you?

Cheers,

Genevieve

Tab

Hi @Genevieve P. ,

I want the output to be a symbol.

Genevieve P.

Hi @Tab

You could build this out into one long formula, but what I would suggest doing is have two formulas. One that looks across sheets to find the Average Value, and then one that translates that number into a symbol.

Ex. Formula One in a column called "Formula":

=(COUNTIF({Progress Column}, "Quarter") * 0.25 + COUNTIF({Progress Column}, "Half") * 0.5 + COUNTIF({Progress Column}, "Three Quarter") * 0.75 + COUNTIF({Progress Column}, "Full")) / COUNT({Progress Column})

Formula Two, in your Symbol column:

=IF(Formula@row = 1, "Full", IF(Formula@row >= 0.75, "Three Quarter", IF(Formula@row >= 0.5, "Half", IF(Formula@row >= 0.25, "Quarter", "Empty"))))

Let me know if this makes sense!

Cheers,

Genevieve