take an average only cells with data

[futurejock]

Hi I'd like to ask a cell to calculate an average of data (numbers) in a column.

I'd like to be able to insert rows to increase the column length accomodating new data as it comes in.

Also if I clear a cell in the column from a previous quantity to zero, I want it to be left our of the average; that is zero does not equal a zero value instance, and should not count as an increase in the denominator of the average; that is not 0/1 but rather as if it did not exist.

I can make a zero cell blank but would rather allow either "0" or blank to not count in the average.

Finally and this would be so cool I'd like the average to refer to a column that accounts the date of the entry, and for the avergae to filter for only one week at a time ending on Fridays.

If that would be possible I'd love to have a pop up calendar in my date column, and if that helps to make this easier to then I'd need to know how to do that. Currently I hand enter dates nn/dd/yy

Thanks

 

[VoG]

For the first part

=AVERAGE(IF(A:A<>0,A:A))

Entered using CTRL + Shift + Enter

 

[Aladin Akyurek]

Taking up the first question...

Hi I'd like to ask a cell to calculate an average of data (numbers) in a column.

I'd like to be able to insert rows to increase the column length accomodating new data as it comes in.

Also if I clear a cell in the column from a previous quantity to zero, I want it to be left our of the average; that is zero does not equal a zero value instance, and should not count as an increase in the denominator of the average; that is not 0/1 but rather as if it did not exist.

I can make a zero cell blank but would rather allow either "0" or blank to not count in the average.

Let column A the column of interest...

=SUMIF(A:A,">0")/COUNTIF(A:A,">0")