Rollup or Find Average Progress Symbol
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.
Answers

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 Crosssheet 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

Hi @Genevieve P. ,
I want the output to be a symbol.

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
Help Article Resources
Categories
Check out the Formula Handbook template!