Closed Thread
Results 1 to 6 of 6

Excel Function to return nearest non blank cell value from a rectangular array of cells

  1. 01-10-2014, 05:07 PM #1
    omer60 is offline
    Registered User
    Join Date
    11-19-2009
    Location
    Atlanta, GA, USA
    MS-Off Ver
    Excel 2003
    Posts
    15

    Excel Function to return nearest non blank cell value from a rectangular array of cells

    Hi all, I have been struggling with this for weeks. Assume you have a rectangular range. Say A1:M18. There are values in some random cells (any number of cells) within the range. The rest of cells are blank. In cell M19 (ie cell below the bottom right corner of the range), I need a excel function that returns the nearest (ie physical location) non blank cell value. I have searched the internet for weeks. The closest formula is the following (see below) but it is flawed as it returns zero if there are values in rows below the row of the nearest cell with non blank...and also zero if there is value in a column greater than the column of the nearest cell with non blank.

    Closest formula so far:
    INDEX(A1:M18,LARGE(IF(ISBLANK(A1:M18),"",ROW(A1:M18)),1)-ROW(A1:M18)+1,LARGE(IF(ISBLANK(A1:M18),"",COLUMN(A1:M18)),1)-COLUMN(A1:M18)+1).

    And the function should be relative not absolute as the range can be extended..but the location of the formula remain same relative to the array size. (ie. always at below the right bottom cell).

    NOTE: Ignore the special case where two non blank cells are the nearest. That will not happen.

    I really need help on this. So, PLEASE HELP.

    I am attaching a file to show what I mean.
    Attached Files Attached Files
    Last edited by omer60; 01-10-2014 at 05:14 PM.
    Reply With QuoteRegister To Reply
  2. 01-10-2014, 07:13 PM #2
    Max, Singapore is offline
    Valued Forum Contributor
    Join Date
    09-07-2006
    Posts
    520

    Re: Excel Function to return nearest non blank cell value from a rectangular array of cell

    Here's one guess / tinker for you to try out ...

    In N1:
    =IF(OFFSET($A$1,MOD(ROWS($1:1)-1,18),INT((ROWS($1:1)-1)/18))="","",OFFSET($A$1,MOD(ROWS($1:1)-1,18),INT((ROWS($1:1)-1)/18)))

    In O1:
    =IF(OFFSET($A$1,INT((ROWS($1:1)-1)/13),MOD(ROWS($1:1)-1,13))="","",OFFSET($A$1,INT((ROWS($1:1)-1)/13),MOD(ROWS($1:1)-1,13)))

    Copy N1:O1 down to O234, this exhausts the contents in the rectangle area A1:M18 (18 rows x 13 columns) in 2 zig-zag ways. Col N extracts content in a column-by-column manner from col A to col M (exhaust A1:A18, then B1:B18, etc), while col O does it row-wise: A1:M1, then A2:M2, etc

    To evaluate results for the "nearest" non blank cell value to the bottom right corner cell of the source area A1:M18, use these to extract the "bottomest" value in cols N and O in say M19:M20

    In M19: =LOOKUP(2,1/(N1:N234<>""),N1:N234)
    In M20: =LOOKUP(2,1/(O1:O234<>""),O1:O234)

    M19 is the result taken from a column angle, M20 gives the row angle result. Both formulae will return the same result if the "nearest" non blank cell happens to lie on the left-to-right diagonal of the source area, viz A1,B2,C3,D4 ... M13, or in M14 to M18
    ------------------------------------------
    Success? Celebrate it, click on the little star at the bottom left of my responses
    Reply With QuoteRegister To Reply
  3. 01-10-2014, 08:21 PM #3
    omer60 is offline
    Registered User
    Join Date
    11-19-2009
    Location
    Atlanta, GA, USA
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Excel Function to return nearest non blank cell value from a rectangular array of cell

    Max...thanks a lot for taking the time to help. Sounds interesting but unfortunately I can not do that. See the range grows dynamically...I mean...there is in fact same formula (within other formula) buried within the range itself. In other words, for every range, this formula has to be evaluated at cell below the right bottom corner of range above. As range gets larger by a column or row, still same formula has to be at the relative position. So, it has to be a formula that can be copied down in larger areas. Using other cells/rows/columns to compute intermediate steps will be problematic to the overall process.

    From excel standpoint, the idea is to return the nearest non blank cell which is the intersection of the largest row number and largest column number that meet at a cell address that has a value in it. I hope someone can figure that out.
    Last edited by omer60; 01-10-2014 at 08:25 PM.
    Reply With QuoteRegister To Reply
  4. 01-11-2014, 12:32 PM #4
    omer60 is offline
    Registered User
    Join Date
    11-19-2009
    Location
    Atlanta, GA, USA
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Excel Function to return nearest non blank cell value from a rectangular array of cell

    Anybody. HELP
    Reply With QuoteRegister To Reply
  5. 01-12-2014, 09:04 AM #5
    alvin-chung is offline
    Valued Forum Contributor
    Join Date
    12-25-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2016
    Posts
    506

    Re: Excel Function to return nearest non blank cell value from a rectangular array of cell

    Hi omer60, try this array formula (ctrl+shift+enter) in M19.

    Formula: copy to clipboard 
    Please Login or Register  to view this content.




    _______________________________________________________________________________________________________________________________
    1. Click on the * Add Reputation if you think someone helped you
    2. Mark your thread as SOLVED when question is resolved

    Alvin
    Reply With QuoteRegister To Reply
  6. 01-13-2014, 01:01 AM #6
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,168

    Re: Excel Function to return nearest non blank cell value from a rectangular array of cell

    Thread posted in CS subforum. Hence closed.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]
    Reply With QuoteRegister To Reply
Closed 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. Excel 2007 : IF function to return blank cell
    By jayb in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-15-2013, 04:28 AM
  2. using frequency function not including blank cells in the data array
    By alliugiv in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-12-2013, 08:53 AM
  3. Return Blank Cell if Sum Cells Blank or Zero
    By Battledeck in forum Excel General
    Replies: 6
    Last Post: 06-11-2012, 05:55 PM
  4. IF function with multiple cells to return a blank cell
    By ato178028 in forum Excel General
    Replies: 4
    Last Post: 10-29-2011, 03:21 AM
  5. [SOLVED] VLOOKUP CONDITION TO RETURN BLANK CELL WHERE LOOKUP VALUE IS NOT IN TABLE ARRAY
    By Scott Lolmaugh in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-09-2006, 07:10 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