Results 1 to 6 of 6

Thread: Get the average of the last 5 non-zero cells in a row (not column)

  1. #1

    Get the average of the last 5 non-zero cells in a row (not column)



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Objective: Get the average of the last 5 non-zero cells in a row (not column).
    I am using Excel 2010.
    I have a row of 15 cells with values in the first 10 cells, some of which are zeros.
    I will be adding additional values (or zeros) each week for a total of 15 weeks.
    I have done a lot of research and have discovered that there are many ways to accomplish this - far too many for me to investigate given my time constraints and low level of expertise.
    Spreadsheet attached.

    Please, help me accomplish this. I would be most grateful.
    Many thanks.
    Attached Files Attached Files

  2. #2
    Conjurer sambit's Avatar
    Join Date
    Sep 2014
    Posts
    143
    Articles
    0
    Excel Version
    Excel2007
    Hi,
    plz see the file.
    Attached Files Attached Files

  3. #3
    Conjurer hossat's Avatar
    Join Date
    May 2015
    Location
    Algiers, Algeria
    Posts
    102
    Articles
    0
    Excel Version
    365
    Hi,
    what do you mean by "last"? the five lowest values?

  4. #4

    Reply to hossat

    Quote Originally Posted by hossat View Post
    Hi,
    what do you mean by "last"? the five lowest values?
    Hello. By "last" I mean the most recently entered five numbers...going from left to right...disregarding the zero/blank cells that may be in some of the cells.

    Example: 1 0 2 3 0 4 5 6 0 7 . . .

    In this case I want the average of 3, 4, 5, 6, 7, which would be 25/5=5. The zeros should be ignored. Only the last five cells with values will be averaged. Any of the cells could have zeros or even left blank...whichever would be best for use in a formula.

    Thanks for your help.

  5. #5
    Conjurer sambit's Avatar
    Join Date
    Sep 2014
    Posts
    143
    Articles
    0
    Excel Version
    Excel2007
    Hi,
    in cell Q6 put the below array formula (Ctrl+Shift+Enter)
    =SUM(P6:INDEX(B6:P6,LARGE((COLUMN(B6:P6)-MIN(COLUMN(B6:P6))+1)*(B6:P6<>0),5)))/5
    Attached Files Attached Files

  6. #6
    Greetings sambit,
    Your formula works grrrrrreat! I plan to learn it from the inside out.
    Many, many thanks to you, my friend.

Posting Permissions

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