column formula

[sdc83]

hi, I'm trying to figure out a formula to show the last entry from K5:K300 in cell K301, the range of cells will contain a numerical value. this range of cells also have SUM formulas and all variants of formulas I've tried returns 0 in cell K301 which I presume is due to the formulas which must be there. how can I have cell K301 show the last number which has been calculated and ignore the empty cells that the SUM formula has not calculated anything yet.

 

[jtakw]

Hi,

For last Numeric value in Column use one of these:

=LOOKUP(2,1/K5:K300,K5:K300)

=LOOKUP(9.99999999999999E+307,K5:K300)

Edit: Typo in first formula, changed 1 to 2

 

[sdc83]

Hi,

For last Numeric value in Column use one of these:

=LOOKUP(1,1/K5:K300,K5:K300)

=LOOKUP(9.99999999999999E+307,K5:K300)

thats worked perfect, thank you very much.

 

[jtakw]

You're welcome, thanks for the feedback.

I had a typo in the first formula, changed the 1 to 2.

 

[Peter_SSs]

thats worked perfect, thank you very much.

Not sure which one you chose, but I would definitely recommend the second one. I don't know if it would be possible with your data but the first formula would give incorrect results if the last number in the column happened to be anywhere in the range 0 <= number < 1
Example

21 02 21.xlsm
	K	L	M
5	3		5
6	5		0.4
7	0.4
8
Last num

Cell Formulas
Range	Formula
M5	M5	=LOOKUP(2,1/K1:K300,K1:K300)
M6	M6	=LOOKUP(9.99999999999999E+307,K1:K300)

 

[jtakw]

@Peter_SSs Thanks for pointing that out Peter