I am trying to get the average of a column of cells, but when there is no number in a cell, it still counts it. I need a formula that does not count the blank cells, or cells containing a formula.
The number here relates to the 2 of 3 cells containing verbiage and returns a ⅔ value.
Hi Rob,
From the Function Browser
Regards,
Ian.
HiRob,
Why are you using AVERAGEIF?
Does this work:
All three columns contain the same formula in the footer row.
B11: =IF(SUM(B)<>0,AVERAGE(B),"")
Average ignores empty cells and cells containing text when summing a range of cells and when counting the number values in those cells. In column B, that would produce a count of zero values to average, and would produce a 'division by zero' error in A11. The IF statement avoids that by calculating the average only if the sum is not equal to zero.
The heading on column B was originally correct, as all body cells in this column were empty for the first test. Entering text in B8 was done after the initial test, and gave the sme result (shown)
Regards,
Barry
Hi iRob,
Try this:
quinn
Average of cells not containing numbers
