Column Average

saraha83081 ✭✭✭
edited 12/09/19 in Formulas and Functions


I am trying to calculate the average of a column.  It seems like this should be a very simple task.

I'm typing =AVG then referencing the column I need to average from another sheet.  It generates the following formula  =AVG({Employee Reviews - PFP Range 7})

However, when I hit enter, I get a #DIVIDE BY ZERO error.  Any thoughts on how to overcome this would be greatly appreciated.  I'm sure there is something very simple that I'm missing, I just can't quite figure out what it is.



Average Formula.png

Divide By Zero Error.png



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I am guessing there are probably blanks and/or text in the column. You will need an AVG(COLLECT function to only look at cells that have numbers. Something like this...


    =AVG(COLLECT({Employee Reviews - PFP Range 7}, {Employee Reviews - PFP Range 7}, ISNUMBER(@cell)))

  • saraha83081
    saraha83081 ✭✭✭

    Thanks for your help, Paul!  That function didn't work for me at first, BUT it did help me figure out what I was doing wrong.

    In the column I was trying to average I was using a formula to calculate the numeric values.  In my formula I had quotes around the numbers (i.e. "3", "3.5", etc).  This seemed to make the functions read them as text rather than numbers.

    Once I figured that out, I was able to make the correction (remove the quotes from my original formulas) and your formula as well as the simple AVG function I was trying to use worked perfectly!

    Thank you so much for helping work through that!  I appreciate it!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help! yes

    Glad you got it figured out. Those quotes will always turn the result into a text string regardless of number or not.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!