Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2012

    Unanswered: Last Digits Ignoring Blanks

    Hello, I'm stumped on an issue.

    In Row 3:3, I have the dates. In Row 4:4, I have numbers from a certain day or the day is blank in column A4-Q4. I add a number or blank everyday.

    So, for
    A4: 30
    B4 36
    C4 blank
    D4 41
    E4 blank

    I am trying to obtain the last 10 digits in Row 4:4, excluding blanks. In my case, this is the last 10 days. with data (In excel, the dates are the columns furthest to the right.) However, I do not want to obtain the blanks, only the numbers.

    I researched it, and tried the following formulas. However, the formulas below don't work:

    =OFFSET(INDEX(4:4,MATCH(9.999999999999E+307,4:4)), ,-3,1,1)

    Would greatly appreciate any insights or recommendations you have.

    Thanks so much,

  2. #2
    Join Date
    Sep 2008
    London, UK
    Hi Kevin,

    Getting the most recent one is pretty straightforward:


    Getting the other four is more difficult. To get the 2nd number, use this array formula:


    Because this is an array formula, when you type it into the formula bar you have to complete the entry with CTRL+SHIFT+ENTER, not just ENTER. If done correctly then Excel should automatically surround the formula with curly braces { }.

    To get the 3rd number, you would use this array formula. It's the same as the last one except I've changed the 2's to 3's:


    It's the same idea for 4 and 5. If there aren't at least 5 numbers in the row then the relevant formulas will return blanks.

Posting Permissions

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