How to find last 12 nonzero values in a column of data

[RAS710]

I have a column of data containing 24 rows. Rows 1-12 always contain positive numbers. Rows 13-24 can contain a positive number or are blank. All the blanks are at the bottom of the column, i.e., there are no blanks above a positive number. I would like to to obtain the last 12 positive values in the column, but I am stumped as to how to do it. An example of the data is as follows:

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

4.10
4.10
4.00
3.90
3.80
4.00
3.90
3.80
3.70
3.80
3.70
3.90
4.00
3.80
3.80
3.60
3.60
3.70
3.70
3.70
3.50
3.60

<colgroup><col style="width: 100px"></colgroup><tbody>
</tbody>

Thanks for any help. This is the last step to complete my project and it really has me scratching my head.

 

[RAS710]

I don't see how to edit my post or I'd do so, but the boxes I included didn't survive. If they had, you could have seen that there are two blank rows at the bottom of the column. Also, the data is in column v.
Thanks again for any help.

 

[william_man]

You can use COUNT to count the total number of numbers there are in the list, and OFFSET to return the respective number from the list. This should give you the last number in the list:

=OFFSET($V$1,COUNT($V:$V)[B]-1[/B],0)

Then you can change the -1 to -2 to return the second to last number, and -3 to return the third last number, and so on.

 

[Peter_SSs]

I would like to to obtain the last 12 positive values in the column,

Try this, copied down 12 rows.

<b>Last 12</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:56px;" /><col style="width:56px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >V</td><td >W</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; text-align:right; ">4.10</td><td style="font-size:10pt; text-align:right; ">3.70</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; text-align:right; ">4.10</td><td style="font-size:10pt; text-align:right; ">3.90</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; text-align:right; ">4.00</td><td style="font-size:10pt; text-align:right; ">4.00</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; text-align:right; ">3.90</td><td style="font-size:10pt; text-align:right; ">3.80</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; text-align:right; ">3.80</td><td style="font-size:10pt; text-align:right; ">3.80</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; text-align:right; ">4.00</td><td style="font-size:10pt; text-align:right; ">3.60</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:10pt; text-align:right; ">3.90</td><td style="font-size:10pt; text-align:right; ">3.60</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:10pt; text-align:right; ">3.80</td><td style="font-size:10pt; text-align:right; ">3.70</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:10pt; text-align:right; ">3.70</td><td style="font-size:10pt; text-align:right; ">3.70</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:10pt; text-align:right; ">3.80</td><td style="font-size:10pt; text-align:right; ">3.70</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-size:10pt; text-align:right; ">3.70</td><td style="font-size:10pt; text-align:right; ">3.50</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-size:10pt; text-align:right; ">3.90</td><td style="font-size:10pt; text-align:right; ">3.60</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="font-size:10pt; text-align:right; ">4.00</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="font-size:10pt; text-align:right; ">3.80</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="font-size:10pt; text-align:right; ">3.80</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="font-size:10pt; text-align:right; ">3.60</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="font-size:10pt; text-align:right; ">3.60</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="font-size:10pt; text-align:right; ">3.70</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="font-size:10pt; text-align:right; ">3.70</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="font-size:10pt; text-align:right; ">3.70</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td style="font-size:10pt; text-align:right; ">3.50</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td style="font-size:10pt; text-align:right; ">3.60</td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >W1</td><td >=INDEX(V$1:V$24,COUNT<span style=' color:008000; '>(V$1:V$24)</span>-12+ROWS<span style=' color:008000; '>(W$1:W1)</span>)</td></tr></table></td></tr></table>

 

[RAS710]

Try this, copied down 12 rows.

Last 12

	V	W
1	4.10	3.70
2	4.10	3.90
3	4.00	4.00
4	3.90	3.80
5	3.80	3.80
6	4.00	3.60
7	3.90	3.60
8	3.80	3.70
9	3.70	3.70
10	3.80	3.70
11	3.70	3.50
12	3.90	3.60
13	4.00
14	3.80
15	3.80
16	3.60
17	3.60
18	3.70
19	3.70
20	3.70
21	3.50
22	3.60
23
24

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:56px;"><col style="width:56px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula W1 =INDEX(V$1:V$24,COUNT(V$1:V$24)-12+ROWS(W$1:W1)) <tbody> </tbody>

<tbody>
</tbody>

Peter- Thanks very much! That worked perfectly!

 

[Peter_SSs]

Peter- Thanks very much! That worked perfectly!

You're welcome. Thanks for the follow-up.

BTW, ...

a) Welcome to the MrExcel board!

b) Best not to fully quote long posts as it makes the thread harder to read/navigate. If you want to quote, quote small, relevant parts only.