In-cell function that finds last value in another column

j3rseyBoy · Mar 9th, 2002, 09:04 PM

Since the subject header sounds too convoluted, here's what I'd like to do, using an in-cell function. In column A is a date; in column B is data that I have entered; and in column C will be a formula that performs a calcultion on the previous two columns:

A B C
1/1/01 10
1/8/01
1/15/01 38 2

The formula in C would find the last data point in column B, and say (Bx-B1)/(Ax-A1). In this case, x=3. I can do this using a macro with out a problem, however, I'd like to be able to use an in-cell formula if possible. The time period between data points varies, as does the number blank lines.

Any help would be appreciated. Thanks!

[ This Message was edited by: j3rseyBoy on 2002-03-09 20:04 ]

Dave Hawley · Mar 9th, 2002, 09:10 PM

Hi

If you entries are numeric use:

=INDEX($B$1:$B$1000,MATCH(-9E+306,$B$1:$B$1000,-1),1)

To return the value of last cell (will look past blanks)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 9th, 2002, 09:04 PM	#1
j3rseyBoy New Member Join Date: Mar 2002 Posts: 7	Since the subject header sounds too convoluted, here's what I'd like to do, using an in-cell function. In column A is a date; in column B is data that I have entered; and in column C will be a formula that performs a calcultion on the previous two columns: A B C 1/1/01 10 1/8/01 1/15/01 38 2 The formula in C would find the last data point in column B, and say (Bx-B1)/(Ax-A1). In this case, x=3. I can do this using a macro with out a problem, however, I'd like to be able to use an in-cell formula if possible. The time period between data points varies, as does the number blank lines. Any help would be appreciated. Thanks! [ This Message was edited by: j3rseyBoy on 2002-03-09 20:04 ]

Mar 9th, 2002, 09:10 PM	#2
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi If you entries are numeric use: =INDEX($B$1:$B$1000,MATCH(-9E+306,$B$1:$B$1000,-1),1) To return the value of last cell (will look past blanks)