|
PureBytes Links
Trading Reference Links
|
You may have your answer already, but.... using the following formula
entered as an "array formula" you could have nulls interdispersed throughout
the column of numbers.
assuming A1:A900 have either nulls or numbers, the formula in B1 (or
elsewhere) is
=OFFSET(A1,MAX(ROW(A1:A900)*(A1:A900<>""))-1,0)
to enter as an array formula, use ALT-Enter instead of just enter. The {}
marks will go around the fomula if you did it correctly.
I put 2,3,4,30,35 in A1:A5 and put 89 in A21 with the cells in between empty
and it returned 89 as it should.
Regards,
Chris
----- Original Message -----
From: "cwest" <cwest@xxxxxxxxxxxx>
To: "omegalist" <omega-list@xxxxxxxxxx>
Sent: Wednesday, June 25, 2003 10:20 PM
Subject: Re: Excel question
> Clearly I could have been clearer :-) Sorry. I hope this is better.
>
> I wish to use the last value in a column as a divisor in a formula. I have
a
> column of 900 cells. Not all of them always contain values, and the values
> change (given another event), although there aren't any nulls between
> values. For example:
>
> A1 value1
> A2 value2
> A3 null
> A... null
> A900 null
>
> My formula is x/A2. How do I discover that A2 contains the last value in
the
> column?
>
> Another example:
>
> A1 value1
> A2 value2
> A3 value3
> A4 null
> A... null
> A900 null
>
> How do I discover that A3 contains the last value in the column?
>
> I looked at Kim's and Alex's suggestions, but couldn't get them to work.
> Also couldn't find reference to some of the components in help.
>
> Thanks
> Colin
>
>
>
>
|