+ Reply to Thread
Results 1 to 8 of 8

Find the value of the 2nd to last cell in a column that includes blank cells

  1. #1
    Registered User
    Join Date
    07-23-2013
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    5

    Cool Find the value of the 2nd to last cell in a column that includes blank cells

    Hi

    I am struggling to write a formulae that will give the value of the 2nd to last cell in a column that includes blank cells. I tried

    =LOOKUP(2,2/(B1:B100>0),B5:B88)

    However I think that is looking halfway down the column only. I just want the value of the 2nd to last cell with a value.

    Any help would be appreciated.

    ;o)

  2. #2
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: Find the value of the 2nd to last cell in a column that includes blank cells

    =INDEX(B1:B100,LARGE(IF(B1:B100<>"",ROW(B1:B100)),2))

    Array formula: Press Ctrl+Shift+Enter, not just Enter

  3. #3
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Find the value of the 2nd to last cell in a column that includes blank cells

    try non array
    =INDEX(B1:B100,LARGE(INDEX((B1:B100<>"")*ROW(B1:B100),0),2))
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Registered User
    Join Date
    07-23-2013
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Find the value of the 2nd to last cell in a column that includes blank cells

    Hmmm, neither of these seem to work. Each gives 0.00.

  5. #5
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Find the value of the 2nd to last cell in a column that includes blank cells

    well i know were are not both wrong!
    Attached Files Attached Files

  6. #6
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Find the value of the 2nd to last cell in a column that includes blank cells

    Try this array formula**:

    =INDEX(B:B,LARGE(IF(B5:B88<>"",ROW(B5:B88)),2))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  7. #7
    Registered User
    Join Date
    07-23-2013
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Find the value of the 2nd to last cell in a column that includes blank cells

    Sorry to have doubted you Martin, and you too Mama. They are 2 working solutions. Thanks. I must have made a typo. When I get that promotion I will think of you both, before I spend my pay rise on booze.

  8. #8
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Find the value of the 2nd to last cell in a column that includes blank cells

    there you all go array mad again!

+ 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] > or = to 0 includes blank cells
    By KJC in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-25-2013, 10:00 PM
  2. [SOLVED] Find first non-blank cell after a group of blanks copy offset cells, find next blank, loop
    By gwsampso in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-21-2012, 06:11 AM
  3. [SOLVED] Counting Cells that includes exact match and also if a cell includes specific text
    By smclachlan in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-19-2012, 07:42 PM
  4. [SOLVED] How to find the minimum value from a set of cells which includes both numbers and #N/A
    By kannanr03 in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 07-28-2012, 02:54 PM
  5. Find a blank cell and calculate the average of all cells above till the next blank
    By joshnathan in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-27-2012, 11:51 AM

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