Take an average of a range of cells but exlude blanks from that av
 

I have a spreadsheet that I am trying to determine the average price. The
columns are each a different product and each column does not have a value on
each row. I would like to find the average price of each column, but need to
exclude the rows that are blank. I had thought I could do this with a
countif and avg formula, but got an error that I have too few arguments.

column E has 125 values in it and there are 575 rows, so 350 are blank. I
don't want to have those 350 included into the formula that gives me the
average.

I know this is simple, but not sure what needs to go in to exlude any cells
are that blank.

--
Barb

Take an average of a range of cells but exlude blanks from that av
 

The AVERAGE formula excludes blank cells, or cells not containing a value.

=AVERAGE(A1:A200) would sum the cells in that range, count the number of
cells having a value and use the count as the divisor.
--
Kevin Backmann


"B G" wrote:

I have a spreadsheet that I am trying to determine the average price. The
columns are each a different product and each column does not have a value on
each row. I would like to find the average price of each column, but need to
exclude the rows that are blank. I had thought I could do this with a
countif and avg formula, but got an error that I have too few arguments.

column E has 125 values in it and there are 575 rows, so 350 are blank. I
don't want to have those 350 included into the formula that gives me the
average.

I know this is simple, but not sure what needs to go in to exlude any cells
are that blank.

--
Barb

Take an average of a range of cells but exlude blanks from that av
 

=SUM(E1:E575)/COUNT(E1:E575)

Vaya con Dios,
Chuck, CABGx3




"B G" wrote:

I have a spreadsheet that I am trying to determine the average price. The
columns are each a different product and each column does not have a value on
each row. I would like to find the average price of each column, but need to
exclude the rows that are blank. I had thought I could do this with a
countif and avg formula, but got an error that I have too few arguments.

column E has 125 values in it and there are 575 rows, so 350 are blank. I
don't want to have those 350 included into the formula that gives me the
average.

I know this is simple, but not sure what needs to go in to exlude any cells
are that blank.

--
Barb

Take an average of a range of cells but exlude blanks from that av
 

"B G" skrev i en meddelelse
...
I have a spreadsheet that I am trying to determine the average price. The
columns are each a different product and each column does not have a value
on
each row. I would like to find the average price of each column, but need
to
exclude the rows that are blank. I had thought I could do this with a
countif and avg formula, but got an error that I have too few arguments.

column E has 125 values in it and there are 575 rows, so 350 are blank. I
don't want to have those 350 included into the formula that gives me the
average.

I know this is simple, but not sure what needs to go in to exlude any
cells
are that blank.

--
Barb

Hi Barb

The AVERAGE() function automatically excludes blank cells.


--
Best regards
Leo Heuser

Followup to newsgroup only please.

Take an average of a range of cells but exlude blanks from that av
 

B G wrote:
I have a spreadsheet that I am trying to determine the average price. The
columns are each a different product and each column does not have a value on
each row. I would like to find the average price of each column, but need to
exclude the rows that are blank. I had thought I could do this with a
countif and avg formula, but got an error that I have too few arguments.

column E has 125 values in it and there are 575 rows, so 350 are blank. I
don't want to have those 350 included into the formula that gives me the
average.

I know this is simple, but not sure what needs to go in to exlude any cells
are that blank.

--
Barb

Hi Barb,

If the blank cells are true blanks then the average formula does not
include them in the calculation.
If the blank cells are zero values that have been rendered blank then
they are considered as zero and included in the calculation. To
determine if this is the case go Tools|Option|View tab. If Window
options "zero values" does not have atick in the box then cells with
zero will appear as blank and will be included in the calculation, so
you should put a tick in that box to avoid confusing true blanks and
hidden zero blanks.

Ken Johnson