Search list and count

[mm57]

Hi, I have a problem that has been driving me crazy -
I have a list of values in a column that were recorded every two minutes.

I need to calculate the time since each particular value in the list was last equaled or exceeded.

I can do this by counting the number of cells since the value was exceeded or equaled but as I'm dealing with thousands of values in the list I need a formula that can do it.

So I want my output to look like this
<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding:0px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} --> </style> <table style="border-collapse: collapse;width:195pt" border="0" cellpadding="0" cellspacing="0" width="195"> <col style="width:65pt" span="3" width="65"> <tbody><tr style="height:15.0pt" height="15"> <td style="height:15.0pt;width:65pt" height="15" width="65">
</td> <td style="width:65pt" width="65"> A (value)
</td> <td style="width:65pt" width="65"> B (time since)
</td> </tr> <tr style="height:15.0pt" height="15"> <td style="height:15.0pt" align="right" height="15">
</td> <td align="right">9</td> <td>
</td> </tr> <tr style="height:15.0pt" height="15"> <td style="height:15.0pt" align="right" height="15">
</td> <td align="right">8</td> <td>
</td> </tr> <tr style="height:15.0pt" height="15"> <td style="height:15.0pt" align="right" height="15">
</td> <td align="right">7</td> <td>
</td> </tr> <tr style="height:15.0pt" height="15"> <td style="height:15.0pt" align="right" height="15">
</td> <td align="right">6</td> <td>
</td> </tr> <tr style="height:15.0pt" height="15"> <td style="height:15.0pt" align="right" height="15">
</td> <td align="right">5</td> <td>
</td> </tr> <tr style="height:15.0pt" height="15"> <td style="height:15.0pt" align="right" height="15">
</td> <td align="right">4</td> <td>
</td> </tr> <tr style="height:15.0pt" height="15"> <td style="height:15.0pt" align="right" height="15">
</td> <td align="right">5</td> <td align="right">4</td> </tr> <tr style="height:15.0pt" height="15"> <td style="height:15.0pt" align="right" height="15">
</td> <td align="right">6</td> <td align="right">8</td> </tr> <tr style="height:15.0pt" height="15"> <td style="height:15.0pt" align="right" height="15">
</td> <td align="right">7</td> <td align="right">12</td> </tr> <tr style="height:15.0pt" height="15"> <td style="height:15.0pt" align="right" height="15">
</td> <td align="right">8</td> <td align="right">16</td> </tr> </tbody></table> Any body have any ideas to help?

Thanks
<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding:0px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} --> </style> <table style="border-collapse: collapse; width: 195pt;" border="0" cellpadding="0" cellspacing="0" width="195"> <col style="width:65pt" span="3" width="65"> <tbody><tr style="height:15.0pt" height="15"> <td style="height:15.0pt;width:65pt" height="15" width="65">
</td> <td style="width:65pt" width="65">
</td> <td style="width:65pt" width="65">
</td> </tr> <tr style="height:15.0pt" height="15"> <td style="height:15.0pt" align="right" height="15">
</td> <td align="right">
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="15"> <td style="height:15.0pt" align="right" height="15">
</td> <td align="right">
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="15"> <td style="height:15.0pt" align="right" height="15">
</td> <td align="right">
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="15"> <td style="height:15.0pt" align="right" height="15">
</td> <td align="right">
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="15"> <td style="height:15.0pt" align="right" height="15">
</td> <td align="right">
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="15"> <td style="height:15.0pt" align="right" height="15">
</td> <td align="right">
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="15"> <td style="height:15.0pt" align="right" height="15">
</td> <td align="right">
</td> <td align="right">
</td> </tr> <tr style="height:15.0pt" height="15"> <td style="height:15.0pt" align="right" height="15">
</td> <td align="right">
</td> <td align="right">
</td> </tr> <tr style="height:15.0pt" height="15"> <td style="height:15.0pt" align="right" height="15">
</td> <td align="right">
</td> <td align="right">
</td> </tr> <tr style="height:15.0pt" height="15"> <td style="height:15.0pt" align="right" height="15">
</td> <td align="right">
</td> <td align="right">
</td> </tr> </tbody></table>

 

[Aladin Akyurek]

Hi, I have a problem that has been driving me crazy -
I have a list of values in a column that were recorded every two minutes.

I need to calculate the time since each particular value in the list was last equaled or exceeded.

I can do this by counting the number of cells since the value was exceeded or
equaled but as I'm dealing with thousands of values in the list I need a
formula that can do it.

So I want my output to look like this
<STYLE> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding:0px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} --> </STYLE><TABLE style="WIDTH: 195pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=195><COLGROUP><COL style="WIDTH: 65pt" span=3 width=65><TBODY><TR style="HEIGHT: 15pt" height=15><TD style="WIDTH: 65pt; HEIGHT: 15pt" height=15 width=65>

</TD><TD style="WIDTH: 65pt" width=65>A (value)

</TD><TD style="WIDTH: 65pt" width=65>B (time since)

</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt" height=15 align=right>

</TD><TD align=right>9</TD><TD>

</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt" height=15 align=right>

</TD><TD align=right>8</TD><TD>

</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt" height=15 align=right>

</TD><TD align=right>7</TD><TD>

</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt" height=15 align=right>

</TD><TD align=right>6</TD><TD>

</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt" height=15 align=right>

</TD><TD align=right>5</TD><TD>

</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt" height=15 align=right>

</TD><TD align=right>4</TD><TD>

</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt" height=15 align=right>

</TD><TD align=right>5</TD><TD align=right>4</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt" height=15 align=right>

</TD><TD align=right>6</TD><TD align=right>8</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt" height=15 align=right>

</TD><TD align=right>7</TD><TD align=right>12</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt" height=15 align=right>

</TD><TD align=right>8</TD><TD align=right>16</TD></TR></TBODY></TABLE>Any body have any ideas to help?

Thanks
<STYLE> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding:0px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} --> </STYLE>

Let A2:A11 the sample you posted.

Let A12 also house a 8. What result do you want to see in B12?

 

[njimack]

Welcome to the board.
Assuming your data starts from row 1, enter the following into B2 (using ctrl+shift+enter) and copy down as far as necessary:
=IF(COUNTIF($A$1:A1,A2),(ROW()-MAX(IF($A$1:A1=A2,ROW($A$1:A1))))*2,"")

 

[Peter_SSs]

My interpretation (with some slightly changed data). Formula in B3 copied down.

Excel Workbook
	A	B
1	Value	Time Since
2	5
3	4	2
4	7
5	6	2
6	5	2
7	4	2
8	5	4
9	6	8
10	7	12
11	8
12	6	2
Time Since