# Formula for last entry in a column not zero

Discussion in 'Mac Basics and Help' started by JWmacuser, Aug 6, 2010.

1. ### JWmacuser macrumors newbie

Joined:
Aug 6, 2010
#1
Working on a spreadsheet in NUMBERS. Want to write a logic statement/formula to pick up the most recent non-zero entry in a column. Something like "IF C31=0, then C30" but then if C30 equals zero go to C29, then C28 if C29 is zero and so on.

Joined:
Jan 15, 2006
Location:
The Kop
#2
Ideally some sort of context of how you would want to use this would be best as there may be a more suitable method for doing this but the following formula reports the row position in a range where 0 first appears.

The first 0 is the search term, the B refers to column B, the final 0 refers to which value it picks up.

So in the following range

1
2
3
0
9
2
2
1
0

The formula reports 4. Note it ignores the later 0.

If you want it to pick up the last 0 then change the formula to:

On the above range this formula reports 9.

3. ### JWmacuser thread starter macrumors newbie

Joined:
Aug 6, 2010
#3
context is: At the end of every day I post a sold units number in column F (rows 1 through 31 for each day). In F32, I would like the most recent sold units amount to show.

so in column F the entries are
row 1 value = 4
row 2 value = 5
row 3 value = 7
row 4 value = 9
row 5 value = 14
row 6 value = 0
.
.
.
.
through
row 31 value = 0

I would like row 32 to pick up row 5 value of 14 because it is the latest non-zero entry.

Joined:
Jan 15, 2006
Location:
The Kop
#4
Give this a go:

If there aren't any "0"'s then it will just give a red triangle. I had a little look at the match function and it doesn't seem possible to give it parameters to output if it can't match.

If you want error checking I'll have another look in the morning.

Joined:
Aug 6, 2010