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

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • Tab
    Tab ✭✭

    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

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!