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.
Got a tip for us?
Let us know
Become a MacRumors Supporter for $50/year with no ads, ability to filter front page stories, and private forums.
Formula for last entry in a column not zero
- Thread starter JWmacuser
- Start date
- Sort by reaction score
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.
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.
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.
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.
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.
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.
