+ Reply to Thread
Results 1 to 7 of 7

Return last 4 values in a Row, ignoring blank cells

  1. 07-23-2017, 11:09 PM #1
    UnitedCloud01
    UnitedCloud01 is offline
    Registered User
    Join Date
    07-22-2017
    Location
    Toowoomba
    MS-Off Ver
    2010
    Posts
    23

    Exclamation Return last 4 values in a Row, ignoring blank cells

    Hi All

    I need a formula that returns the 4 latest numbers in a row, ignoring the blanks.

    I have the formula: =INDIRECT(ADDRESS(5,MAX((BL5:DK5<>"")*COLUMN(BL5:DK5)),1)) as an array in cell at the end of the row

    =OFFSET(INDIRECT(ADDRESS(5,MAX((BL5:DK5<>"")*COLUMN(BL5:DK5)),1)),0,-1) as an array after the above

    =OFFSET(INDIRECT(ADDRESS(5,MAX((BL5:DK5<>"")*COLUMN(BL5:DK5)),1)),0,-2) as an array after the above

    =OFFSET(INDIRECT(ADDRESS(5,MAX((BL5:DK5<>"")*COLUMN(BL5:DK5)),1)),0,-3) as an array after the above

    This works great when there are no empty cells.

    I have tried to reverse this formula which uses small to return the first cell but am getting myself in knots =INDEX($V$3:$NV$3,SMALL(IF($V$3:$NV$3<>"",COLUMN($V$3:$NV$3)-COLUMN($V$3)+1),1))

    Any help will be appreciated.

    Thanks

    Scott
    Reply With QuoteRegister To Reply
  2. 07-23-2017, 11:37 PM #2
    leelnich
    leelnich is offline
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Return last 4 values in a Row, ignoring blank cells

    Oops... Duplicate post.
    Last edited by leelnich; 07-23-2017 at 11:45 PM.
    Reply With QuoteRegister To Reply
  3. 07-23-2017, 11:39 PM #3
    leelnich
    leelnich is offline
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Return last 4 values in a Row, ignoring blank cells

    Hi Scott, welcome to the forum! Paste this ARRAY FORMULA* in D5 and copy TO THE LEFT:
    Edited 11:53. Changed SMALL to LARGE.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    NOTE: Adjust the COLUMNS($D5:D5) clause to point to the cell where you paste the formula. Be sure to get the $ dollar sign right.

    Row\Col
    A
    B
    C
    D
    BK
    BL
    BM
    BN
    BO
    BP
    BQ
    BR
    5
    		 aa bb cc dd aa bb cc dd


    *Always press CTRL+SHIFT+ENTER to confirm entry after pasting or editing an ARRAY FORMULA in the Formula Bar.
    When copying the formula to other cells, make sure the copied cell is not part of the Paste Area.

    Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee
    Last edited by leelnich; 07-23-2017 at 11:57 PM.
    Reply With QuoteRegister To Reply
  4. 07-23-2017, 11:51 PM #4
    leelnich
    leelnich is offline
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Return last 4 values in a Row, ignoring blank cells

    Hey, sorry, just realized I gave you the FIRST four cells. Please see post #3 for UPDATED version (now returns LAST four).
    Last edited by leelnich; 07-23-2017 at 11:56 PM.
    Reply With QuoteRegister To Reply
  5. 07-24-2017, 12:01 AM #5
    UnitedCloud01
    UnitedCloud01 is offline
    Registered User
    Join Date
    07-22-2017
    Location
    Toowoomba
    MS-Off Ver
    2010
    Posts
    23

    Re: Return last 4 values in a Row, ignoring blank cells

    Thank you Thank you!!!!! Leelnich You are awesome!!!
    Reply With QuoteRegister To Reply
  6. 07-24-2017, 12:08 AM #6
    leelnich
    leelnich is offline
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Return last 4 values in a Row, ignoring blank cells

    You're most welcome, and thank you for the rep! That SMALL(expression,COLUMNS($D5:D5)) construction is very useful with series.
    Last edited by leelnich; 07-24-2017 at 12:41 AM.
    Reply With QuoteRegister To Reply
  7. 07-24-2017, 01:12 AM #7
    UnitedCloud01
    UnitedCloud01 is offline
    Registered User
    Join Date
    07-22-2017
    Location
    Toowoomba
    MS-Off Ver
    2010
    Posts
    23

    Re: Return last 4 values in a Row, ignoring blank cells

    Yes. It is most useful. I wish I had asked you earlier...lol...stubborn much!? lol
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Ignoring Blank Cells But Keeping Index of Non-Blank Cells w/ Data Validation and ScrollRow
    By rogerd1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-24-2017, 01:41 PM
  2. Formula that returns only values of cells and doesnt return blank/NA values
    By pageandrewr1 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-08-2016, 12:32 PM
  3. Ignoring blank Cells while Fetching Values
    By Parthan in forum Excel General
    Replies: 1
    Last Post: 08-23-2014, 11:06 AM
  4. [SOLVED] Add values from diffrent sheets to drop down menu and ignoring blank cells.
    By RinorM in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-06-2014, 02:37 AM
  5. Help to Add Time values with in a range of cells in a col. ignoring Blank cells . How?
    By rej2k7 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-24-2013, 06:29 AM
  6. Adding cells with values but if cells do not have values then return blank
    By jonnykhan in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-13-2012, 12:07 PM
  7. Return Values of adjacent cells ONLY if cell is blank
    By DPacks in forum Excel General
    Replies: 2
    Last Post: 08-17-2011, 01:35 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1