Formula to get the first cell with a numerical value from a selection?

1
votes

In Microsoft Excel 2013:

I have a spreadsheet (exported monthly from another program). My goal is to take the last row of data and subtract it from the first row of data (all values in the spreadsheet are numeric). The number of rows changes each month.

I was able to get the last line of data by using the following to select the last cell in the column with a numerical value in it using : =INDEX(B:B,MATCH(9.99999999999999E+307,B:B))

What I need is another equation to get the first cell with a numerical value from the columns, so that I can then write a formula to subtract the last values from the first ones.

1
excelindexing

1 Answers

2
votes

You can simplify the last number formula by using LOOKUP, i.e.

=LOOKUP(9.99999999999999E+307,B:B)

and for the first number try this

=INDEX(B:B,MATCH(1,INDEX(ISNUMBER(B:B)+0,0),0))