Formula to determine average wait time for a number to re-appear

D_Spark

Board Regular
Joined
Feb 4, 2007
Messages
232
I need assistance in creating a formula to determine the average wait time for numbers greater than or equal to 200000 to appear in a series.

I have 100 lines of data (BJ100:BJ200)

The wait time will be determine by the row number,eg

BJ100=12
BJ101=200000
BJ102=15000
BJ103=200050
BJ104=9
BJ105=8
BJ106=5555
BJ107=300000
BJ108=0
BJ109=5000000
BJ110=250
BJ111=18
BJ112=0
BJ113=5555
BJ114=300000
BJ115=0
BJ116=250
BJ117=250
BJ118=18
BJ119=5000000




So for the above the expected return will be: the average of (2,4,2,5,5)
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,159
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
try

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">BJ100</td><td style="text-align: right;;">12</td><td style="text-align: right;;">0</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">BJ101</td><td style="text-align: right;;">200000</td><td style="text-align: right;;">2</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">BJ102</td><td style="text-align: right;;">15000</td><td style="text-align: right;;">0</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">BJ103</td><td style="text-align: right;;">200050</td><td style="text-align: right;;">4</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">BJ104</td><td style="text-align: right;;">9</td><td style="text-align: right;;">0</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">BJ105</td><td style="text-align: right;;">8</td><td style="text-align: right;;">0</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">BJ106</td><td style="text-align: right;;">5555</td><td style="text-align: right;;">0</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">BJ107</td><td style="text-align: right;;">300000</td><td style="text-align: right;;">8</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">BJ108</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">BJ109</td><td style="text-align: right;;">5000000</td><td style="text-align: right;;">10</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">BJ110</td><td style="text-align: right;;">250</td><td style="text-align: right;;">0</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">BJ111</td><td style="text-align: right;;">18</td><td style="text-align: right;;">0</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">BJ112</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">BJ113</td><td style="text-align: right;;">5555</td><td style="text-align: right;;">0</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">BJ114</td><td style="text-align: right;;">300000</td><td style="text-align: right;;">15</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style=";">BJ115</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">BJ116</td><td style="text-align: right;;">250</td><td style="text-align: right;;">0</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style=";">BJ117</td><td style="text-align: right;;">250</td><td style="text-align: right;;">0</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style=";">BJ118</td><td style="text-align: right;;">18</td><td style="text-align: right;;">0</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style=";">BJ119</td><td style="text-align: right;;">5000000</td><td style="text-align: right;;">20</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">3.6</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=IF(<font color="Blue">B2>=200000,ROW(<font color="Red">A2</font>)-ROW(<font color="Red">$A$2</font>)+1,0</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=IFERROR(<font color="Blue">IF(<font color="Red">C2>0,C2-LOOKUP(<font color="Green">2,1/(<font color="Purple">$C1:C$2>0</font>),$C1:C$2</font>),""</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D23</th><td style="text-align:left">=AVERAGE(<font color="Blue">D2:D21</font>)</td></tr></tbody></table></td></tr></table><br />
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,159
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
you're welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,108,816
Messages
5,525,064
Members
409,617
Latest member
Lenaf

This Week's Hot Topics

Top