oops. to find the last number in b and subtract a1

=INDEX(B:B,MATCH(9999999,B:B))-A1

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"Don Guillett" <dguillett@gmail.com> wrote in message news:...
> Try this withOUT needing "xx"
> =MAX(B:B)-A1
>
> To sum col B
> =SUM(B2:OFFSET(B2,COUNTA($B:$B),0))
>
> -- 
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguillett@gmail.com
> "Arto K" <arto.koivisto@noadd.fi.inv> wrote in message 
> news:uz9Pn.17155$if1.13173@uutiset.elisa.fi...
>>I have several cells in one column, what include numbers (example B2-B4). 
>>After few empty cells, I have a cell (example B7), what include formula 
>>B4-A1.
>>
>> A1  B1
>> 1
>>       2
>>       3
>>       4
>> 5
>> 6
>> xx    =B4-A1
>>
>> Sometimes I add new numbers after cell B4 (example B5, B6 etc..) and 
>> sometimes I have to add new lines between last used cell, what include 
>> numbers and cells, what include the formula (example B7). When I add 
>> example three new line before B7, formula moves in cell B10. On the A7 is 
>> value xx. If B7 moves B10, also A7 moves A10.
>>
>> After changes example look like this:
>>
>> A1  B1
>> 1
>>       2
>>       3
>>       4
>>       5
>>       6
>>       7
>>       8
>>
>> xx    =B8-A1
>>
>> How to do macro, what find column A, cell what include text "xx" (example 
>> A10) and then it find last used cell in column B (example B8) and after 
>> that, the macro put formula in cell B10. The formula should be "last used 
>> cell in column B"-A1. Example in this case B8-A1.
>>
>> Ps. I use Excel 2007.
>>
>>
>>
>>
>>
>