Using "Non-Empty Cell"

[Steve]

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

 

[T. Valko]

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.

 

[T. Valko]

Slightly shorter, but volatile:

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

 

[T. Valko]

Ok, this IS my final answer! <g>

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

 

[Ron Rosenfeld]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

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