+ Reply to Thread
Results 1 to 5 of 5

Help Understanding "Return Last Non-Blank Row #" Formula

  1. #1
    Registered User
    Join Date
    06-18-2019
    Location
    Seattle, WA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    8

    Question Help Understanding "Return Last Non-Blank Row #" Formula

    Hello, I came across a formula that is built to return the last non-blank row number in a range.

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


    I am having trouble understanding how it works.

    I understand that ROW(A2:A20) will return the value "2".

    I understand that A2:A20<>"" SHOULD* return "TRUE" if there is at least one non-blank cell, and "FALSE" if all cells in the range are blank. (*See below).

    I do not understand how TRUE * 2 or FALSE * 2 works, or how inserting that into a MAX function works. Isn't it only trying to max 1 singular number?

    Lastly, I don't understand how the SUMPRODUCT formula brings this all together.

    *Checking the "range not equal to blank" portion of the formula, I ran into the following issue. Any small-scale test I did yielded the expected results. However, I am trying to check an entire column, down to the end of the sheet, so there are many blank cells. Even though I have 65 non-blank cells, the function for some reason returns "FALSE" for when I check the entire range.

    My formula is =H70:H1048576<>"" and returns "FALSE" even through H71:H135 are all filled with values.
    Attached Images Attached Images
    Last edited by Cryptikfox; 10-09-2019 at 01:34 PM.

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Help Understanding "Return Last Non-Blank Row #" Formula

    The Sumproduct function allows you to use an array formula without using ctrl shift enter.

    So the formula (A2:A20<>"")*ROW(A2:A20)

    Will return a list of zeros and row numbers

    The zeros corresponding to blank rows, the row numbers for the non blank rows

    So the max function will return the row number of the last non blank row

    The sumproduct allows this to work.

    if you highlight (A2:A20<>"")*ROW(A2:A20) in the formula bar and press f9 you will see the data before the max function gets to work on it.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Registered User
    Join Date
    06-18-2019
    Location
    Seattle, WA
    MS-Off Ver
    Office 365 ProPlus
    Posts
    8

    Re: Help Understanding "Return Last Non-Blank Row #" Formula

    Quote Originally Posted by mehmetcik View Post
    So the formula (A2:A20<>"")*ROW(A2:A20)

    Will return a list of zeros and row numbers
    Hmm, alright, this is starting to make a little more sense... Is there any way of understanding why this formula returns specifically a list of numbers? Because also if you put =(A2:A20<>"")*ROW(A2:A20) in a cell, it just comes back with 0.

    Thanks for mentioning the F9 trick, didn't know about that.

  4. #4
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Help Understanding "Return Last Non-Blank Row #" Formula

    paste =(A2:A20<>"")*ROW(A2:A20) into a cell

    Select the cell and enter it using Ctrl Shift Enter

    You may well see a 0.

    select (A2:A20<>"")*ROW(A2:A20) and press f9

    you will see a string of numbers.

    change your formula to
    =max((A2:A20<>"")*ROW(A2:A20) ) and enter it using Ctrl Shift Enter.

    ok?

  5. #5
    Forum Contributor
    Join Date
    12-13-2013
    Location
    Calgary,AB,Canada
    MS-Off Ver
    2016,O365
    Posts
    236

    Re: Help Understanding "Return Last Non-Blank Row #" Formula

    =lookup(1,0/(a1:a50<>""),row(a1:a50))

    or

    =sumproduct((row(a1:a50)= max(if(a1:a50<>"",row(a1:a50))))*(row(a1:a50)))

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] If vlookup return blank means "paid", otherwise is "unpaid"
    By Dewdrop in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-12-2019, 05:51 AM
  2. [SOLVED] add if blank cell then return "" to existing formula
    By cristylk in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-05-2017, 09:58 AM
  3. [SOLVED] If formula result is zero, return "" (blank) - Any way to simplify this?
    By D.Lovell in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-01-2015, 06:07 PM
  4. Formula returns "00:00" when I want it to return zero/blank
    By johnmitch38 in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 03-24-2015, 12:45 PM
  5. Replies: 8
    Last Post: 07-06-2014, 08:51 PM
  6. formula to return blank rather than a "0"
    By xlr8r in forum Excel General
    Replies: 9
    Last Post: 10-04-2011, 03:52 PM
  7. Replies: 3
    Last Post: 02-16-2011, 02:55 PM

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