Column Average

saraha83081

Hello!

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.

 

 

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

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

Happy to help!

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