Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a table that has 5 columns. The rows after some point will have a value of zero. I need to get the value of the previous row of column 5.
For example; here after 4th row, all the value become zero. I then need the value 89 to appear in some designated Cell outside this table.

Year    Amt1    Amt 2   Amt 3    Amt 5
2012         5       6      17      48
2013        15       0      72      83
2014        25      63       0      12
2015         0      56      75      89
   0         0       0       0       0
   0         0       0       0       0
   0         0       0       0       0
share|improve this question
In the title of your question you write about the 3rd column and in the text "the value of the previous row of column 5"? – duDE Aug 13 '13 at 8:32
Fixed the typo error – Dheer Aug 13 '13 at 8:39
Can there be zeroes in Column 5 before 89? Or in Year perhaps? – Jerry Aug 13 '13 at 8:53
There will be no Zeros in either column 1[Year] or in Column 5 [Amt 5]. However there can be zeros in other columns Amt1, 2, 3. So essentially I need the last non zero value in column 5. – Dheer Aug 13 '13 at 9:01
up vote 3 down vote accepted

A formula that would suit what you're looking for would be this:


E2 is the first value in column 5 (48)

OFFSET with the cell at the beginning of the column and moves down by MATCH(0,E:E,0)-3.

MATCH(0,E:E,0) will return the row in which it can find the first occurrence of 0 (The parameters are: look for 0 into column E:E and use 0 to mean exact match).

The -3 is to account for the header, the first cell and move up one cell because MATCH will return the position of 0, not the one of the cell just above it.

The last parameter in OFFSET is by how many columns the range should move, and since we're already in column E, there's no movement in this respect.

EDIT: Since the column Year are perfect zeroes (the column 5 contains very small numbers rounded to 1), you can use this instead:


Understanding OFFSET

Say you use the formula:

=OFFSET(A1, 1, 1)

OFFSET will return the cell which is found 1 row below and 1 column after the cell A1, meaning cell B2.

Similarly, =OFFSET(A1, 3, 1) will give the cell B4.

There are actually more options you can have in OFFSET, namely the height and the width of the resulting range.

=OFFSET(A1, 1, 1, 2, 3)

OFFSET will return the cell which is found 1 row below and 1 column after the cell A1, meaning cell B2. Then, the height of the result will be 2, and the width 3, meaning it would return the range B2:D3 (if you select this range with your mouse, you see it'll be 2 cells high and 3 cells wide). However, since this is an array, you will get an #N/A with this formula. You can still carry out operations with this array, such as VLOOKUP or INDEX, SUM, etc.

share|improve this answer
Thanks. I had figured out the MATCH portion, but was not able to figure out the Ofset function. The value in Column 5 although shows zero [computed field] is really some small decimal ... 5 zeros after decimal :). So changed the MATCH to -1 and its giving me the desired result. If we can find something based on Year column that is zero it would be perfect. – Dheer Aug 13 '13 at 9:23
Sorry figured out the offset much better now =OFFSET(E2,MATCH(0,A2:A10,0)-3,0) – Dheer Aug 13 '13 at 9:28
@Dheer I included a more indepth description about OFFSET in my answer, just in case ^^. Also, if you start at A2 in your match, you need to remove 2 instead of 3! – Jerry Aug 13 '13 at 9:38
Yes Indeed, got a hold of the function. Thanks. – Dheer Aug 13 '13 at 9:43

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .