# Average a column of cells until you reach a row where two cells are 0.

1. ## Average a column of cells until you reach a row where two cells are 0.

I have a sheet where I need to average the scores on the last column of data.
There are multiple rows with data in them, and some data will be missing in some of the cells, but the row will still tally the results in the last column.
Two specific cells in each row contains either a 0 or a positive number. If both cells contain a 0, then it is the end of the rows, and the row count used in the average needs to end there.
So, I just need to look for those two cells, and if they both contain a zero, take the last cell in the row above, and average it an all the values above it.
How can I go about doing this?  Register To Reply

2. ## Re: Average a column of cells until you reach a row where two cells are 0.

Hi ,

Can you mention , with reference to the workbook you have uploaded , which are the two cells , and what the average should be ?

Is it the value you have at present in cell Q14 ? If yes , then how did you arrive at 59 as the end of the column range ?

Narayan  Register To Reply

3. ## Re: Average a column of cells until you reach a row where two cells are 0.

The column I want to average is N. When it sees a 0 in both the E and H columns, I want it to stop at the row before the 0's.
So, if row 10 has a 0 at E10 and H10, I want to average cells N4 to N9, and put the result in cell Q14.
Hopefully that makes sense.  Register To Reply

4. ## Re: Average a column of cells until you reach a row where two cells are 0.

Hi ,

I have used a helper column T , which has the following formula copied down from T4 through to T59 :
Formula:  `Please Login or Register  to view this content.`

The average is displayed in cell Q1 , which has the formula :
Formula:  `Please Login or Register  to view this content.`

See if this is correct.

Narayan  Register To Reply

5. ## Re: Average a column of cells until you reach a row where two cells are 0. Originally Posted by NARAYANK991 Hi ,

I have used a helper column T , which has the following formula copied down from T4 through to T59 :
Formula:  `Please Login or Register  to view this content.`

The average is displayed in cell Q1 , which has the formula :
Formula:  `Please Login or Register  to view this content.`

See if this is correct.

Narayan
That's exactly what I needed!
Thank you.  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 