# I have a average problem

I want to average a colum(sp) of numbers that get a new cell added daily. The trouble is I have days that are not entered yet so I have blank cells above the formula that will have data by the end of the month. How do I average all cells that have data and stop when it gets to the blanks or 0 cells? current formula is =average (c5:c35)...I now have to change this daily to show the last row with data..ie c5:c27

Thanks Eldon

edit retag close merge delete

Sort by » oldest newest most voted

Perhaps look at using some combination of the SUMIF and COUNTIF functions. This will allow you to set a condition e.g., greater than zero.

EDIT: As @mariosv indicates the AVERAGEIF function is the better option.

more

will not work as I would still have to do a manuel entry to let it know how many cells were added...? and then do the averge formula.

( 2014-12-28 04:23:29 +0200 )edit
1

Using conditional functions you don't need adapt the range. Try with =AVERAGEIF(C5:C35;"<>0").

( 2014-12-28 13:50:53 +0200 )edit

this seems to have fixed it!!! thanks lots!!!!

( 2014-12-29 04:01:59 +0200 )edit

I just made a test. The function =average(....) neglects empty cells.

Just make a test with a few numbers and include in the range of the a few empty cells. And than the same formula but the range without empty cells.

more

but my sheet has zeros in it and it does average them.

( 2014-12-25 13:52:32 +0200 )edit

I have tryed averageif but it will not do it eather as the cells are always new every day...might not be possible.

( 2014-12-25 20:42:33 +0200 )edit

@eldon.dehart - If possible attach the file with a reduced set of your data. If confidentiality - this is a public forum - does not allow original data, create a sheet with artificial data having the same structure and formats like your original sheet.

( 2014-12-29 04:54:57 +0200 )edit