Finding the latest value in a range by formula

[Ralph Heidecke]

I have a work book with several sheets. One sheet sums data over several
time periods and calculates average. A second sheet reports the averages and
the most recent data;

they look something like this:

Sum sheet by period
Date A B
3-Apr 182.40 9.67
17-Apr 189.81 14.94
1-May 179.67 11.45
15-May 174.08 8.64
29-May 196.04 10.90
12-Jun 196.24 11.85
26-Jun 196.43 17.91
10-Jul 203.65 23.75
24-Jul 160.07 27.26
7-Aug 194.80 24.15
21-Aug 190.61 20.62
4-Sep 138.15 16.44
18-Sep 180.79 17.76
2-Oct 189.75 11.75

16-Oct
..
..empty columns
..
31-Mar
YTD Average 183.75 16.22



Averages Sheet

YTD Last Per
A 183.75 189.75
B 16.22 11.75

As I do this now I have to enter the most recent period from the SumSheet to
the Averages sheet I can get the YTD averages by pointing but the most
recent period always moves.

Is there a formula I can enter in the "LastPer" cell of the Averages sheet
that would find the last value?

I am using Excel 2002.

 

[duane]

=offset(sumsheet!b1,counta(sumsheet!b1:b1000)-1,0,0,0)) should give you
the last entry in column b, change the b's to c's for column c

 

[Frank Kabel]

Hi
see:
http://www.xldynamic.com/source/xld.LastValue.html

for several formulas