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

R

RAS710

New Member
Joined
Nov 5, 2019
Messages
3
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>[TABLE="width: 0"]
<colgroup><col style="width: 100px"></colgroup><tbody>[TR]
[TD="align: right"]4.10[/TD]
[/TR]
[TR]
[TD="align: right"]4.10[/TD]
[/TR]
[TR]
[TD="align: right"]4.00[/TD]
[/TR]
[TR]
[TD="align: right"]3.90[/TD]
[/TR]
[TR]
[TD="align: right"]3.80[/TD]
[/TR]
[TR]
[TD="align: right"]4.00[/TD]
[/TR]
[TR]
[TD="align: right"]3.90[/TD]
[/TR]
[TR]
[TD="align: right"]3.80[/TD]
[/TR]
[TR]
[TD="align: right"]3.70[/TD]
[/TR]
[TR]
[TD="align: right"]3.80[/TD]
[/TR]
[TR]
[TD="align: right"]3.70[/TD]
[/TR]
[TR]
[TD="align: right"]3.90[/TD]
[/TR]
[TR]
[TD="align: right"]4.00[/TD]
[/TR]
[TR]
[TD="align: right"]3.80[/TD]
[/TR]
[TR]
[TD="align: right"]3.80[/TD]
[/TR]
[TR]
[TD="align: right"]3.60[/TD]
[/TR]
[TR]
[TD="align: right"]3.60[/TD]
[/TR]
[TR]
[TD="align: right"]3.70[/TD]
[/TR]
[TR]
[TD="align: right"]3.70[/TD]
[/TR]
[TR]
[TD="align: right"]3.70[/TD]
[/TR]
[TR]
[TD="align: right"]3.50[/TD]
[/TR]
[TR]
[TD="align: right"]3.60[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
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.
 
Upvote 0
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:

Code: 
=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.
 
Upvote 0
RAS710 said:
I would like to to obtain the last 12 positive values in the column,
Click to expand...
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>
 
Upvote 0
Peter_SSs said:
Try this, copied down 12 rows.

Last 12

VW

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:56px;"><col style="width:56px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="align: right"]4.10[/TD]
[TD="align: right"]3.70[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]4.10[/TD]
[TD="align: right"]3.90[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]4.00[/TD]
[TD="align: right"]4.00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: right"]3.90[/TD]
[TD="align: right"]3.80[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: right"]3.80[/TD]
[TD="align: right"]3.80[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: right"]4.00[/TD]
[TD="align: right"]3.60[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: right"]3.90[/TD]
[TD="align: right"]3.60[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: right"]3.80[/TD]
[TD="align: right"]3.70[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="align: right"]3.70[/TD]
[TD="align: right"]3.70[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]
[TD="align: right"]3.80[/TD]
[TD="align: right"]3.70[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]
[TD="align: right"]3.70[/TD]
[TD="align: right"]3.50[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]
[TD="align: right"]3.90[/TD]
[TD="align: right"]3.60[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]13[/TD]
[TD="align: right"]4.00[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]14[/TD]
[TD="align: right"]3.80[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]15[/TD]
[TD="align: right"]3.80[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]16[/TD]
[TD="align: right"]3.60[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]17[/TD]
[TD="align: right"]3.60[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]18[/TD]
[TD="align: right"]3.70[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]19[/TD]
[TD="align: right"]3.70[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]20[/TD]
[TD="align: right"]3.70[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]21[/TD]
[TD="align: right"]3.50[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]22[/TD]
[TD="align: right"]3.60[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]23[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]24[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
W1=INDEX(V$1:V$24,COUNT(V$1:V$24)-12+ROWS(W$1:W1))

<tbody>
</tbody>

<tbody>
</tbody>
Click to expand...
Peter- Thanks very much! That worked perfectly!
 
Upvote 0
RAS710 said:
Peter- Thanks very much! That worked perfectly!
Click to expand...
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Autonumbering in cell based on last row in column
Replies
2
Views
84
abdo meghari
A
D
  • Solved
How To Reference Last Row With Data In A Column?
Replies
8
Views
426
Dhira
D
A
  • Solved
rename sheet based on number format in last row for each sheet
2
Replies
17
Views
187
Peter_SSs
Peter_SSs
W
  • Question Question
copy columns to other work sheet for variable number of rows
Replies
1
Views
399
Watersource
W
M
  • Solved
Add new sheet per unique value in column A and copy data (along with header) to respective new sheets
Replies
7
Views
724
Alex Blakenburg
A

Forum statistics

Threads
1,224,817
Messages
6,181,149
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top