Let's say that you have numbers in cells J8 through J16.
1. This part of the formula will return the row of the last numerical entry from the range J6:J20:
MATCH(1E+300, J$6:J$20, 1)
Basically, it asks Excel to find the first instance in that range of a number that is larger than an enormous number (10^300). Because no entry will work, Excel will end up returning the last entry with a number in it in that range. In this case, the last entry is in cell J16, which is the 11th cell in the range (since J6 is the first cell). So, this formula will return 11.
[If you're using text entries, the corresponding formula would be something like: MATCH("ZZZZZ", J$6:J$20, 1)]
2. Now, we need to figure out what the actual value is in the 11th row of our range. To do this, we use the OFFSET function. The starting cell of our range is J6, and we want to go to the 11th row of the range, which is 10 rows down from the starting cell. So, we have to subtract 1 from the result provided by the MATCH function in step 1 to get the number of rows that we want to offset from the base cell. This gives us:
=OFFSET(J$6, MATCH(1E+300, J$6:J$20, 1)-1, 0)
3. The first number in the range is a little trickier. Basically, we can find the last cell in the range through the function in step 1. If there are no blank spaces in the cells (other than before the first cell of data and after the last cell of data), then each cell will contain a number. This function will tell us how many numbers there were in the range:
In my example, there are numbers in rows 8 through 16. That's a total of 9 numbers, so this formula will return 9.
4. If the last entry in the range is in the 11th row, and there are 9 numbers listed consecutively, then the first entry must be in the 3rd row of the range. Since our base cell for our offset is the first row of the range, we need to go 2 rows down from the base cell. This is calculated as the value in step 1 (11) minus the value in step 3 (9). So, our offset formula becomes:
=OFFSET(J$6, MATCH(1E+300, J$6:J$20, 1)-COUNT(J$6:J$20), 0)