average with zero

[Pammy]

=AVERAGE(AC6:AC11342<>0,AC6:AC11342)
This is my formula for an average, the rows are filled in from ac6 - ac831
and it gives a number beginning with ac6 from a calculation. If there is no
entry then it populates a zero. I get an average of 13 that is showing on
the status bar if I select the cells ac6:ac831, but in my cell where the
average formula is (notice it goes to ac11342 I get an average of 10. All
the cells in this column have zeros where there have been no entries yet, but
will change as data is entered on the row. I have it set to 11342 so the
person can enter without having to do anything.
I do shift+control and enter in the formula bar. Any idea? It is formatted
to a number. Thanks,

 

[Peo Sjoblom]

You are using an incorrect formula


=AVERAGE(IF(AC6:AC11342<>0,AC6:AC11342))

entered with ctrl + shift & enter

--


Regards,


Peo Sjoblom

 

[Mike H]

There was nothing wrong with the formula you posted earlier today when you
asked the same question so changing to an incorrect formula isn't likely to
get you the answer you want.

 

[Pammy]

Peo Sjoblom:

I changed the formula to what you wrote and did Ctrl + Shift +Enter and I
get 24, but if I select ac06:ac11342 and look at the status bar it is 10.4.
Any idea? I even copied and pasted your formula.

 

[Peo Sjoblom]

It's because the average in the statusbar includes zero in it's calculation
so if you have zeros you should
get a smaller number using the statusbar and selecting the whole range as
opposed to the formula

--


Regards,


Peo Sjoblom

 

[Mike H]

The statusbar result isn't the same formula as the array formula and will
include zero values in the average.

Mike

 

[Peo Sjoblom]

Do as follows to check the statusbar, temporarily put a header in AC5 unless
you have one already, then apply data>filter>autofilter, select custom from
dropdown and does not equal 0, now select the visible range and check the
statusbar. To remove the filter do data>filter and clear the check mark

--


Regards,


Peo Sjoblom

 

[Pammy]

On my average problem and the formula you gave me below, this is looking at
the zero and giving me a result based ont the >zero, but this is my concern:
I have a formula in the AC cells that are results from other cells and it is
copied all the way down to 11342, so those cells that have no entries at all
will have a 0 in that cell until the information is added. Some of the rows
that have entries but not every field is filled in also have a 0 in that
field until it is completed. I would like to average the cells that have
entries and may contain a 0, but not the cells where nothing is entered at
all it just has the formula copied down. I'm thinking maybe there needs to
be a new formula in the AC column that would let excel know not to include
those zeros but include the zero in the aveage where some infor is entered.