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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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

D
  • Solved
How To Reference Last Row With Data In A Column?
Replies
8
Views
402
Dhira
D
W
  • Question Question
copy columns to other work sheet for variable number of rows
Replies
1
Views
396
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
686
Alex Blakenburg
A
Dazzawm
  • Solved
Macro To Find Latest Date To Corresponding Data In Column B And Add To Sheet 2
Replies
7
Views
317
Fuji
F
1
  • Question Question
Calculate on every third Friday of the month and show results between positive & negative weeks
2
Replies
14
Views
947
15minoffame
1

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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