RE: Excel question

["cwest" <cwest@xxxxxxxxxxxx>]

Thanks to everyone who helped me with my spreadsheet. Gotta crown Kim as
the Excel guru on the list ;)

Colin

-----Original Message-----
From: Kim [mailto:krf01@xxxxxxxxxxxxxxx] 
Sent: Thursday, June 26, 2003 1:14 AM
To: cwest; omegalist
Subject: Re: Excel question

Colin,

O.K. that's more clear.

Assuming the values in the column will always contain numeric values,

=ROW(OFFSET(A1,COUNT(A:A)-1,0))

will return the value of the last non-blank row in column A.
So your formula X/A2 will be =X / ROW(OFFSET(A1,COUNT(A:A)-1,0))

Rgds,

Kim

----- Original Message ----- 
From: "cwest" <cwest@xxxxxxxxxxxx>
To: "omegalist" <omega-list@xxxxxxxxxx>
Sent: Wednesday, June 25, 2003 11: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
>
>
>
>