Column Average

saraha83081saraha83081 ✭✭✭✭✭
edited 12/09/19 in Formulas and Functions
04/01/19 Edited 12/09/19


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

  • saraha83081saraha83081 ✭✭✭✭✭

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

Sign In or Register to comment.