+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    Registered User
    Join Date
    10-13-2003
    Location
    Utah
    Posts
    3

    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?
    Attached Files Attached Files
    Last edited by KevinNiemeyer; 11-07-2019 at 01:14 AM.

  2. #2
    Registered User
    Join Date
    12-03-2012
    Location
    CHENNAI , INDIA
    MS-Off Ver
    Excel 2007
    Posts
    93

    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

  3. #3
    Registered User
    Join Date
    10-13-2003
    Location
    Utah
    Posts
    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.

  4. #4
    Registered User
    Join Date
    12-03-2012
    Location
    CHENNAI , INDIA
    MS-Off Ver
    Excel 2007
    Posts
    93

    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: copy to clipboard
    Please Login or Register  to view this content.


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


    See if this is correct.

    Narayan
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    10-13-2003
    Location
    Utah
    Posts
    3

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

    Quote Originally Posted by NARAYANK991 View Post
    Hi ,

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


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


    See if this is correct.

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1