Results 1 to 3 of 3

Thread: Average last 5 non-blank cells
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2019
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Average last 5 non-blank cells

    1
    1
    0
    1
    1
    0

    Hello there,

    My goal is to get the 60.00% but what formula would I use to exclude blanks and only consider the bottom 5 values?

    Thanks!

  2. #2
    Board Regular
    Join Date
    Oct 2011
    Posts
    4,258
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Average last 5 non-blank cells

    See post # 5 in attached link.
    https://www.excelforum.com/excel-for...ank-cells.html

    ABC
    11
    21 0.6
    3
    40
    51
    6
    71
    80
    9
    10

    Spreadsheet Formulas
    CellFormula
    C2{=AVERAGE(IF(ROW(A1:A12)>=LARGE(IF(A1:A12<>"",ROW(A1:A12)),MIN(COUNT(A1:A12),5)),IF(A1:A12<>"",A1:A12)))}
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> Excel Jeanie HTML 4

  3. #3
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,335
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Average last 5 non-blank cells

    Another way:

     ABC
    11  
    21 0.6
    3   
    40  
    51  
    6   
    71  
    80  

    CellArray Formula
    C2{=AVERAGE(INDEX(A1:A12,LARGE(IF(A1:A12<>"",ROW(A1:A12)),5)):A12)}


    Array formulas
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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