Formula using values in a non-predictable number of cells above

[excel71]

Hello All,

I need help entering a formula that uses a value that is a certain (non consistent) number of cells above the cell where I need to enter the formula, and also is closest to that cell. For example, let's say that I have this in a column:

2
10
""
""
= formula

where "" means that those cells are empty. The formula will use the value 10 (which is the closest cell to the cell that contains the formula). How can I do this (using only formulas, no scripts)?

Thanks!!!

 

[T. Valko]

Hello All,

I need help entering a formula that uses a value that is a certain (non consistent) number of cells above the cell where I need to enter the formula, and also is closest to that cell. For example, let's say that I have this in a column:

2
10
""
""
= formula

where "" means that those cells are empty. The formula will use the value 10 (which is the closest cell to the cell that contains the formula). How can I do this (using only formulas, no scripts)?

Thanks!!!

If you want the last (bottom-most) numeric value in a range of cells:

Book1
	A
2	93
3	17
4	70
5	_
6	_
7	_
8	_
9	_
10	70
Sheet1

Formula entered in A10:

=LOOKUP(1E100,A2:A8)

 

[excel71]

Thanks T. Valko, the formula that I used includes your solution, an if, and an offset formulas too... and it works perfectly!!!

Thanks (again)!!!

 

[T. Valko]

Thanks T. Valko, the formula that I used includes your solution, an if, and an offset formulas too... and it works perfectly!!!

Thanks (again)!!!

You're welcome. Thanks for the feedback!