Dynamic formula

[VegasNath]

Hi.

I need a little help with a formula, that I have explained in the attached.

Any help would be appreciated! Thanks

[HansV]

I assume that the top cell will always be in row 5. If you leave a few blank cells between the bottom cell of the data and the cell with the formula, and insert a new row if you need to add data, you can use this formula in C30:

=INDEX(C5:C29,MATCH(9.99999999999999E+307,C5:C29))-C5

When you insert a new row, the formula will adjust itself automatically. The formula can be copied to F30, I30 etc.

[VegasNath]

Great Hans, Thanks.

Rows 5 & 6 will always be zero, so I am looking for the difference between the last row and row 7. Therefore, based upon your formula, the following appears to work for me:

=INDEX(C7:C29,MATCH(9.99999999999999E+307,C7:C29))-C7

Would you mind explaining this type of formula for me? Thanks!

[HansV]

9.99999999999999E+307 is the largest number that can be entered in a cell in Excel. It is extremely unlikely that you will actually encounter this value.
If you don't specify 0 as third argument for the MATCH function, it will try to find the first value that is greater than or equal to the lookup value. Since it can't find 9.99999999999E+307, it returns the index of the last non-blank cell in the range. INDEX returns the value of that cell.

[VegasNath]

Cool, Thanks!