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

**KevinNiemeyer** · 11-06-2019, 07:49 PM

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?

**NARAYANK991** · 11-07-2019, 12:15 AM

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

**KevinNiemeyer** · 11-07-2019, 12:27 AM

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.

**NARAYANK991** · 11-07-2019, 12:50 AM

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

**KevinNiemeyer** · 11-07-2019, 02:34 AM

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.

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

how to average a value in blank cells in a column between cells with data

[SOLVED] Average column of cells,but ignore errors and return average of numbers that are there...

Average the last 10 cells of a dynamic column but ignore blank cells & 0

[SOLVED] find blank cells in column and average the cells below

Match multiple cells and use corresponding cells to reach a value with conditions

Subtracting values in cells from a total and counting the number of cells to reach <0

[SOLVED] Returning the average of cells, based on the value of cells in a previous column

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions