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
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.
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.
Using conditional functions you don’t need adapt the range. Try with =AVERAGEIF(C5:C35;"<>0").
this seems to have fixed it!!! thanks lots!!!
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.
but my sheet has zeros in it and it does average them.
I have tryed averageif but it will not do it eather as the cells are always new every day…might not be possible.
@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.