OFFSET, COUNTA, Dynamic Range Formula

[truitt20]

I have one column:

Date
1/1/2007
1/2/2007
1/3/2007
1/4/2007
1/5/2007
1/6/2007
1/7/2007
1/8/2007

This data range will expand each day. I want a dynamic formula that subtracts the "last" date from the first date automatically. Is this possible in a formula? I do not want to use named ranges.

 

[AnotherHiggins]

You nearly have it right there in your thread title.

[tab][COLOR=blue white]=OFFSET($A$1, COUNTA(A:A) - 1, 0, 1, 1)[/color]
or, since your header row doesn't contain a number,
[tab][COLOR=blue white]=OFFSET($A$1, COUNT(A:A), 0, 1, 1)[/color]

BTW, if you want to do any charting of this data, you'd be crazy not to use named ranges.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Gavona]

If in column A then maybe
=Max(A:A)-A2

Gavin

 

[yogia]

Hi truitt20:

If the dates are not necessarily in Ascending order, then try ...

=LOOKUP(9.99999999999999E+307,A:A)-A2

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[AnotherHiggins]

I gotta say, I'm kind of embarrassed that MAX didn't occur to me. Forest and trees and all that....

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.