Excel formula to calculate diff b/w 1st value and last value in a column (Excel)?

Question

I have the following columns : (Using Excel Formula)

     A            B
  ------------------------
1 |  Date     |   Value  |
  ------------------------
2 | 8/20/2011 |   92.8   |
3 | 8/21/2011 |   92.4   |
4 | 8/22/2011 |   91.8   |
5 | 8/23/2011 |   90.7   |
6 | 8/24/2011 |   91.1   |
7 | 8/25/2011 |          |
8 | 8/26/2011 |          |
9 | 8/27/2011 |          |
  ------------------------

I want to calculate the difference between 1st Value (B2) and last Value (last populated row in column B)

Edited :

Using formula : =B2-B6 is not what's required. (I want diff in Cell C2)

I want when the user enters the value in B7 it automatically shows the difference between B2 and B7, when he populates B8 then it shows the diff between B2 and B8 and so on..

I don't want some direct method to do this in Excel and not by iterating all values to check the last value. Please suggest the best method to this in Excel.

This is more of a logic problem !

A Pure Excel solution is welcome, I can do that manually.

Edited: Using Excel formula !

I hope I explained my problem properly..

Answer 1

If the headers are in A1 and B1, and your table is A2:B9 for instance, use the formula:

=$B$2-OFFSET($B$2,COUNTA($B$2:$B$9)-1,0,1,1)