Ask Your Question

I have a average problem

asked 2014-12-25 04:50:10 +0200

eldon.dehart gravatar image

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 c5:c27

Thanks Eldon

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2014-12-28 04:01:18 +0200

oweng gravatar image

updated 2014-12-31 02:16:57 +0200

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.

edit flag offensive delete link 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.

eldon.dehart gravatar imageeldon.dehart ( 2014-12-28 04:23:29 +0200 )edit

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

m.a.riosv gravatar imagem.a.riosv ( 2014-12-28 13:50:53 +0200 )edit

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

eldon.dehart gravatar imageeldon.dehart ( 2014-12-29 04:01:59 +0200 )edit

answered 2014-12-25 07:57:54 +0200

ROSt52 gravatar image

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.

edit flag offensive delete link more


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

eldon.dehart gravatar imageeldon.dehart ( 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.

eldon.dehart gravatar imageeldon.dehart ( 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.

ROSt52 gravatar imageROSt52 ( 2014-12-29 04:54:57 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2014-12-25 04:50:10 +0200

Seen: 234 times

Last updated: Dec 31 '14