Row-Average of Percentages in cells

Hi, I am trying to do the average of % (data in percentage) in a row.

Few of my cells are blank too. Rest all data is in % format. Formula i am using is avg(selecting the rows)

Error getting is #Divide By Zero

Please help



Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Gurpreet Jaggi

    How are you populating the numbers that end up being percents in your columns? I can see that the numbers are appearing on the left side of the cell, which indicates that Smartsheet is actually reading that as text values, not numbers.

    You'll notice that with the AVG formula, if the cells are completely blank or if they have text in them you get an error. However if the cells have numerical values (appearing on the right side of the cell) then you get an Average:


    As @Bassam Khalil pointed out, you will want your columns to have the % format applied, then type in the decimal to create the percentage. This is instead of typing in the % symbol which would create a text value.

    See this Help Center on Formatting Options. See also Tips for easier formula creation and this FAQ on numerical values in formulas.

    Let me know if this makes sense! If you're still having issues, it would be helpful to know how you're populating these columns and if you're using a formula (if so, please paste the formula into your response).

    Cheers,

    Genevieve

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @Gurpreet Jaggi 

    Hope you are fine, is all your columns( even the average column) are % type

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Gurpreet Jaggi

    How are you populating the numbers that end up being percents in your columns? I can see that the numbers are appearing on the left side of the cell, which indicates that Smartsheet is actually reading that as text values, not numbers.

    You'll notice that with the AVG formula, if the cells are completely blank or if they have text in them you get an error. However if the cells have numerical values (appearing on the right side of the cell) then you get an Average:


    As @Bassam Khalil pointed out, you will want your columns to have the % format applied, then type in the decimal to create the percentage. This is instead of typing in the % symbol which would create a text value.

    See this Help Center on Formatting Options. See also Tips for easier formula creation and this FAQ on numerical values in formulas.

    Let me know if this makes sense! If you're still having issues, it would be helpful to know how you're populating these columns and if you're using a formula (if so, please paste the formula into your response).

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!