first and last value of a dynamic Range

[djzabala]

hello people!!!

I will ask for your savy help, in this:

I have a Defined dynamic range in a workbook i'm workin on, It consist
on a formula applied to values from another column, and as these value
are added, the formula is applied and the value displayed. if no dat
is presnt in the input column, nothing is displayed...I think that thi
is not so relevant with what I want, anyways.

What I intent to do is to have a pair of cells with formulas locatin
the first and last value (something like the Head and Tail of the list
of the dynamic range respectively.The dynamic range is defined a
follows:

=OFFSET(Data!$D$25,0,0,MATCH(9.99999999999999E+307,Data!$D$25:$D$65536),1)

The "match" part is for locating the calculated values.(I saw this on
thread in this site)

Is there a formula to locate the first and last values?? (a formula fo
first and another for the last)

Thanx a lot in advance

 

[Frank Kabel]

Hi
what kind of values do you have in your data range?
- only numeric or only text entries
- mixed values

 

[djzabala]

Hi!.

Yes, I have only numeric values.more specifically, The numbers are no
sorted.but it's numeric data only.

thanx

 

[Aladin Akyurek]

=OFFSET(Data!$D$25,0,0,MATCH(9.99999999999999E+307,Data!$D$25:$D$65536),1)

can be rewritten as:

=Data!$D$25:INDEX(Data!$D:$D,MATCH(9.99999999999999E+307,$D:$D))

or

=Data!$D$25:INDEX(Data!$D$25:$D$65536,MATCH(9.99999999999999E+307,Data!$D$25:$D$65536))

Let Drange be the name so defined.

First numeric value:

=INDEX(Drange,MATCH(TRUE,ISNUMBER(Drange),0))

which must be confirmed with control+shift+enter instead of just with enter.

Last numeric value:

=LOOKUP(9.99999999999999E+307,Drange)

to be confirmed with just enter.

or equally:

=INDEX(Drange,ROWS(Drange))

by the definition of Drange. Again, confirm with just enter.