I have a several columns of numbers, and I want to calculate the last cell
in the column divided by the first cell in each case (= B300/B1).
However, some columns do not extend all the way back to cell 1. For example,
column F might contain values only from F65 to F300 and so I want to
calculate = F300/F65 in this case. The cells always contain continuous
values when they start.

I want to write a formula to say "F300/highest cell with a value"?

Can anyone suggest a way?

Thanks,

Steve

Assuming the last cell is *always* on row 300:

=IF(COUNT(B1:B300),B300/INDEX(B1:B300,MATCH(TRUE,INDEX(B1:B300<"",,1),0)) ,"")

Copy across as needed.

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
I have a several columns of numbers, and I want to calculate the last cell
in the column divided by the first cell in each case (= B300/B1).
However, some columns do not extend all the way back to cell 1. For
example, column F might contain values only from F65 to F300 and so I want
to calculate = F300/F65 in this case. The cells always contain continuous
values when they start.

I want to write a formula to say "F300/highest cell with a value"?

Can anyone suggest a way?

Thanks,

Steve

Slightly shorter, but volatile:

=IF(COUNT(B1:B300),B300/OFFSET(B1,COUNTBLANK(B1:B300),),"")

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Assuming the last cell is *always* on row 300:

=IF(COUNT(B1:B300),B300/INDEX(B1:B300,MATCH(TRUE,INDEX(B1:B300<"",,1),0)) ,"")

Copy across as needed.

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
I have a several columns of numbers, and I want to calculate the last cell
in the column divided by the first cell in each case (= B300/B1).
However, some columns do not extend all the way back to cell 1. For
example, column F might contain values only from F65 to F300 and so I
want to calculate = F300/F65 in this case. The cells always contain
continuous values when they start.

I want to write a formula to say "F300/highest cell with a value"?

Can anyone suggest a way?

Thanks,

Steve

Ok, this IS my final answer! <g

=IF(COUNT(B1:B300),B300/INDEX(B1:B300,COUNTBLANK(B1:B300)+1),"")

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Slightly shorter, but volatile:

=IF(COUNT(B1:B300),B300/OFFSET(B1,COUNTBLANK(B1:B300),),"")

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Assuming the last cell is *always* on row 300:

=IF(COUNT(B1:B300),B300/INDEX(B1:B300,MATCH(TRUE,INDEX(B1:B300<"",,1),0)) ,"")

Copy across as needed.

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
I have a several columns of numbers, and I want to calculate the last
cell in the column divided by the first cell in each case (= B300/B1).
However, some columns do not extend all the way back to cell 1. For
example, column F might contain values only from F65 to F300 and so I
want to calculate = F300/F65 in this case. The cells always contain
continuous values when they start.

I want to write a formula to say "F300/highest cell with a value"?

Can anyone suggest a way?

Thanks,

Steve

On Sat, 26 Jan 2008 17:55:07 -0000, "Steve" wrote:

I have a several columns of numbers, and I want to calculate the last cell
in the column divided by the first cell in each case (= B300/B1).
However, some columns do not extend all the way back to cell 1. For example,
column F might contain values only from F65 to F300 and so I want to
calculate = F300/F65 in this case. The cells always contain continuous
values when they start.

I want to write a formula to say "F300/highest cell with a value"?

Can anyone suggest a way?

Thanks,

Steve



So long as the only data in the columns are the number entries, then:

=F300/OFFSET(F300,1-COUNT(F1:F300),0)
--ron

If it is possible that your last filled cell can be something other than
300, then this volatile formula will calculate the value in the filled cell
with the highest row number divided by the filled cell with the lowest row
number...

=LOOKUP(9999999999,B1:B300)/INDIRECT("B"&SUMPRODUCT(MIN(9999999999-(B1:B300<"")*(9999999999-ROW(B1:B300)))))

The three occurrences of 9999999999 can be replaced by any number guaranteed
to be larger than the maximum value that can occur in the range B1:B300.

Rick


"Steve" wrote in message
...
I have a several columns of numbers, and I want to calculate the last cell
in the column divided by the first cell in each case (= B300/B1).
However, some columns do not extend all the way back to cell 1. For
example, column F might contain values only from F65 to F300 and so I want
to calculate = F300/F65 in this case. The cells always contain continuous
values when they start.

I want to write a formula to say "F300/highest cell with a value"?

Can anyone suggest a way?

Thanks,

Steve

By the way, I meant to mention... the formula I posted is not dependent on
the range between the lowest filled cell and the highest filled cell being
contiguously filled (there can be gaps in the data and the formula will
still work).

Rick

"Rick Rothstein (MVP - VB)" wrote in
message ...
If it is possible that your last filled cell can be something other than
300, then this volatile formula will calculate the value in the filled
cell with the highest row number divided by the filled cell with the
lowest row number...

=LOOKUP(9999999999,B1:B300)/INDIRECT("B"&SUMPRODUCT(MIN(9999999999-(B1:B300<"")*(9999999999-ROW(B1:B300)))))

The three occurrences of 9999999999 can be replaced by any number
guaranteed to be larger than the maximum value that can occur in the range
B1:B300.

Rick


"Steve" wrote in message
...
I have a several columns of numbers, and I want to calculate the last cell
in the column divided by the first cell in each case (= B300/B1).
However, some columns do not extend all the way back to cell 1. For
example, column F might contain values only from F65 to F300 and so I
want to calculate = F300/F65 in this case. The cells always contain
continuous values when they start.

I want to write a formula to say "F300/highest cell with a value"?

Can anyone suggest a way?

Thanks,

Steve