Finding Last non zero value in a column

[ballan]

I am stumped on this one and it may be quite simple. I have a column (lets say "B") of numbers that have non zero values followed by zero values. In a cell (lets say "C17") where "B17" and a variable number of cells above "B17" are zeros I would like to look upwards in Column "B" starting at "B17" to find the first non zero entry.
Any help would be appreciated.

 

[Cbrine]

Are you looking for VBA or excel functions?

 

[ballan]

Preferably Excel but VBA would be fine.

 

[DRJ]

Once you find the last non zero cell what do you want to do?

 

[Seti]

Is this what you were looking for?

Book12
	A	B	C	D
1		Data
2		4
3		3
4		54
5		2
6		5
7		34
8		435
9		20
10		21
11		22
12		0
13		0
14		0
15		0
16		0
17		0	22
Sheet1

 

[ballan]

I want to put it in cell "C17".

 

[DRJ]

If you want the value then Seti's solution should work for you. Or do you want something else?

 

[Aladin Akyurek]

It looks like you want the last non-zero value from a range. If so...

Book3
	A	B	C	D
1
2	9		-4
3	8
4	8
5	-1
6	0
7	9
8	8
9	-4
10	0
11
Sheet1

The formula in C2 is:

=INDEX(A2:A10,MAX((ABS(A2:A10)>0)*ROW(A2:A10))-(CELL("Row",A2)-1))

which must be confirmed with control+shift+enter instead of just with enter.

 

[ballan]

Yes that is exactly what I was looking for. Now how did you do it? Thanks in advance.

 

[ballan]

It works great! I was trying to figure out why it works, and I can't get a grip on it. Would you mind explaining the f(x)? Here's the formula that works:

=INDEX(A2:A10,MAX((ABS(A2:A10)>0)*ROW(A2:A10))-(CELL("Row",A2)-1))

TIA