Column Average

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.

Average Formula.png

Divide By Zero Error.png

Find more posts tagged with

Formulas

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Countifs result not matching filter
In my sheet, I have a filter for 2 values (see below images). The result is 294 In my report this formula yields 304. =COUNTIFS({helper-child}, "1", {gapStatus}, <>"Rejected (not a GAP)", {gapStatus}, <>"removed - duplicate", {gapStatus}, <>"removed - not valid") Why are they not matching? What am I missing?
Using CONTAINS in a MAX(COLLECT) function
I have a schedule that has a task name column, a date column and a task type column. I am trying to build a formula (in another sheet) that will return the latest date based on when the task type is "APP" and the task name contains "GS" somewhere in the cell. Here is the formula I have come up with: MAX(COLLECT({Schedule…