# Thread: Last Digits Ignoring Blanks

1. Registered User
Join Date
Mar 2012
Posts
1

## 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)
=OFFSET(INDEX(4:4,,COUNT(\$B\$4:\$S\$4)),,-2,1,1)

Would greatly appreciate any insights or recommendations you have.

Thanks so much,
Kevin

2. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511
Hi Kevin,

Getting the most recent one is pretty straightforward:

=IF(COUNT(4:4),LOOKUP(9.99E+307,4:4),"")

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

=IF(COUNT(4:4)>=2,INDEX(4:4,LARGE(IF(ISNUMBER(4:4),COLUMN(4:4),""),2)),"")

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:

=IF(COUNT(4:4)>=3,INDEX(4:4,LARGE(IF(ISNUMBER(4:4),COLUMN(4:4),""),3)),"")

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
•