Averages per Day

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,382
Office Version
  1. 365
Platform
  1. Windows
Can someone please help me?

I have a Query Table of climate data from 1/1/1900 to today which I update regularly.

I wish to calulate the mean High for each of the days (e.g., month 2 day 19) and compare it to the High for each date (e.g. all of the Feb 19s), subsequently indicating whether it is above normal or below normal (or 'wtn', warmer than normal). Then I would like to identify streaks of above and below normal. Then I would like to identify the end date of each streak (or 'sh end', streak high end).

The problem is that even the day-mean formulas are crippling Excel when they are invoked. The streaks formulas crash Excel entirely. You'll find this slow with just 26 records.

So I think I might need a DAX formula in the Query Table to calculate the day-means. Here is what I have, with just a sample of the offending formulas. Can someone help make my project work well?

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]<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 /><col /><col /><col /><col /><col /><col /><col /><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><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="background-color: #A9D08E;;">This source data comes from a query</td><td style="text-align: right;background-color: #A9D08E;;"></td><td style="text-align: right;background-color: #A9D08E;;"></td><td style="text-align: right;background-color: #A9D08E;;"></td><td style="text-align: right;background-color: #A9D08E;;"></td><td style="text-align: right;background-color: #A9D08E;;"></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><td style="text-align: right;;"></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;">6</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><td style="text-align: right;;"></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><td style="text-align: right;;"></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><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">date</td><td style=";">high</td><td style=";">low</td><td style=";">month</td><td style=";">day</td><td style=";">year</td><td style="text-align: right;;"></td><td style=";">mean high</td><td style=";">mean low</td><td style=";">wtn high</td><td style=";">wtn low</td><td style=";">streak high</td><td style=";">streak low</td><td style=";">sh end</td><td style=";">sl end</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">1/01/00</td><td style="text-align: right;;">-14.4</td><td style="text-align: right;;">-23.9</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1900</td><td style="text-align: right;;"></td><td style="text-align: right;;">-3.23</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;">-1</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">1/02/00</td><td style="text-align: right;;">-11.7</td><td style="text-align: right;;">-21.1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1900</td><td style="text-align: right;;"></td><td style="text-align: right;;">-2.67</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;">-2</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">1/03/00</td><td style="text-align: right;;">-12.2</td><td style="text-align: right;;">-21.7</td><td style="text-align: right;;">1</td><td style="text-align: right;;">3</td><td style="text-align: right;;">1900</td><td style="text-align: right;;"></td><td style="text-align: right;;">-2.89</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;">-3</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">1/04/00</td><td style="text-align: right;;">-12.2</td><td style="text-align: right;;">-26.1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">4</td><td style="text-align: right;;">1900</td><td style="text-align: right;;"></td><td style="text-align: right;;">-3.43</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;">-4</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">1/05/00</td><td style="text-align: right;;">-6.7</td><td style="text-align: right;;">-14.4</td><td style="text-align: right;;">1</td><td style="text-align: right;;">5</td><td style="text-align: right;;">1900</td><td style="text-align: right;;"></td><td style="text-align: right;;">-3.82</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;">-5</td><td style="text-align: right;;"></td><td style="text-align: right;;">-5</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;">1/06/00</td><td style="text-align: right;;">4.4</td><td style="text-align: right;;">-13.3</td><td style="text-align: right;;">1</td><td style="text-align: right;;">6</td><td style="text-align: right;;">1900</td><td style="text-align: right;;"></td><td style="text-align: right;;">-3.07</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;">1/07/00</td><td style="text-align: right;;">-6.7</td><td style="text-align: right;;">-10</td><td style="text-align: right;;">1</td><td style="text-align: right;;">7</td><td style="text-align: right;;">1900</td><td style="text-align: right;;"></td><td style="text-align: right;;">-2.68</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;">-1</td><td style="text-align: right;;"></td><td style="text-align: right;;">-1</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;">1/08/00</td><td style="text-align: right;;">-3.3</td><td style="text-align: right;;">-13.3</td><td style="text-align: right;;">1</td><td style="text-align: right;;">8</td><td style="text-align: right;;">1900</td><td style="text-align: right;;"></td><td style="text-align: right;;">-3.58</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;">1/09/00</td><td style="text-align: right;;">3.3</td><td style="text-align: right;;">-15.6</td><td style="text-align: right;;">1</td><td style="text-align: right;;">9</td><td style="text-align: right;;">1900</td><td style="text-align: right;;"></td><td style="text-align: right;;">-3.14</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: right;;">1/10/00</td><td style="text-align: right;;">3.9</td><td style="text-align: right;;">-3.3</td><td style="text-align: right;;">1</td><td style="text-align: right;;">10</td><td style="text-align: right;;">1900</td><td style="text-align: right;;"></td><td style="text-align: right;;">-3.78</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style="text-align: right;;">1/11/00</td><td style="text-align: right;;">3.3</td><td style="text-align: right;;">-6.7</td><td style="text-align: right;;">1</td><td style="text-align: right;;">11</td><td style="text-align: right;;">1900</td><td style="text-align: right;;"></td><td style="text-align: right;;">-4.15</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style="text-align: right;;">1/12/00</td><td style="text-align: right;;">3.3</td><td style="text-align: right;;">-4.4</td><td style="text-align: right;;">1</td><td style="text-align: right;;">12</td><td style="text-align: right;;">1900</td><td style="text-align: right;;"></td><td style="text-align: right;;">-3.09</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style="text-align: right;;">1/13/00</td><td style="text-align: right;;">2.2</td><td style="text-align: right;;">-6.7</td><td style="text-align: right;;">1</td><td style="text-align: right;;">13</td><td style="text-align: right;;">1900</td><td style="text-align: right;;"></td><td style="text-align: right;;">-3.04</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style="text-align: right;;">1/14/00</td><td style="text-align: right;;">2.8</td><td style="text-align: right;;">-11.1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">14</td><td style="text-align: right;;">1900</td><td style="text-align: right;;"></td><td style="text-align: right;;">-4.04</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style="text-align: right;;">1/15/00</td><td style="text-align: right;;">-0.6</td><td style="text-align: right;;">-10</td><td style="text-align: right;;">1</td><td style="text-align: right;;">15</td><td style="text-align: right;;">1900</td><td style="text-align: right;;"></td><td style="text-align: right;;">-3.43</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</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;;">1/16/00</td><td style="text-align: right;;">5.6</td><td style="text-align: right;;">-7.8</td><td style="text-align: right;;">1</td><td style="text-align: right;;">16</td><td style="text-align: right;;">1900</td><td style="text-align: right;;"></td><td style="text-align: right;;">-2.74</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">24</td><td style="text-align: right;;">1/17/00</td><td style="text-align: right;;">6.1</td><td style="text-align: right;;">-6.7</td><td style="text-align: right;;">1</td><td style="text-align: right;;">17</td><td style="text-align: right;;">1900</td><td style="text-align: right;;"></td><td style="text-align: right;;">-2.84</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">25</td><td style="text-align: right;;">1/18/00</td><td style="text-align: right;;">10</td><td style="text-align: right;;">-1.1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">18</td><td style="text-align: right;;">1900</td><td style="text-align: right;;"></td><td style="text-align: right;;">-2.42</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">26</td><td style="text-align: right;;">1/19/00</td><td style="text-align: right;;">6.7</td><td style="text-align: right;;">-2.8</td><td style="text-align: right;;">1</td><td style="text-align: right;;">19</td><td style="text-align: right;;">1900</td><td style="text-align: right;;"></td><td style="text-align: right;;">-3.27</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">27</td><td style="text-align: right;;">1/20/00</td><td style="text-align: right;;">7.8</td><td style="text-align: right;;">-9.4</td><td style="text-align: right;;">1</td><td style="text-align: right;;">20</td><td style="text-align: right;;">1900</td><td style="text-align: right;;"></td><td style="text-align: right;;">-2.38</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">28</td><td style="text-align: right;;">1/21/00</td><td style="text-align: right;;">10</td><td style="text-align: right;;">-0.6</td><td style="text-align: right;;">1</td><td style="text-align: right;;">21</td><td style="text-align: right;;">1900</td><td style="text-align: right;;"></td><td style="text-align: right;;">-2.57</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">29</td><td style="text-align: right;;">1/22/00</td><td style="text-align: right;;">-0.6</td><td style="text-align: right;;">-3.9</td><td style="text-align: right;;">1</td><td style="text-align: right;;">22</td><td style="text-align: right;;">1900</td><td style="text-align: right;;"></td><td style="text-align: right;;">-3.00</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">30</td><td style="text-align: right;;">1/23/00</td><td style="text-align: right;;">-1.1</td><td style="text-align: right;;">-5.6</td><td style="text-align: right;;">1</td><td style="text-align: right;;">23</td><td style="text-align: right;;">1900</td><td style="text-align: right;;"></td><td style="text-align: right;;">-3.04</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">31</td><td style="text-align: right;;">1/24/00</td><td style="text-align: right;;">-2.8</td><td style="text-align: right;;">-13.3</td><td style="text-align: right;;">1</td><td style="text-align: right;;">24</td><td style="text-align: right;;">1900</td><td style="text-align: right;;"></td><td style="text-align: right;;">-3.83</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">17</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">32</td><td style="text-align: right;;">1/25/00</td><td style="text-align: right;;">-2.8</td><td style="text-align: right;;">-15.6</td><td style="text-align: right;;">1</td><td style="text-align: right;;">25</td><td style="text-align: right;;">1900</td><td style="text-align: right;;"></td><td style="text-align: right;;">-4.18</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">18</td><td style="text-align: right;;"></td><td style="text-align: right;;">18</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">33</td><td style="text-align: right;;">1/26/00</td><td style="text-align: right;;">-7.8</td><td style="text-align: right;;">-13.3</td><td style="text-align: right;;">1</td><td style="text-align: right;;">26</td><td style="text-align: right;;">1900</td><td style="text-align: right;;"></td><td style="text-align: right;;">-3.47</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style="text-align: right;;">-1</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;">34</td><td style="text-align: right;;">1/27/00</td><td style="text-align: right;;">-12.2</td><td style="text-align: right;;">-21.7</td><td style="text-align: right;;">1</td><td style="text-align: right;;">27</td><td style="text-align: right;;">1900</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><td style="text-align: right;;"></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;">35</td><td style="text-align: right;;">1/28/00</td><td style="text-align: right;;">7.2</td><td style="text-align: right;;">-20.6</td><td style="text-align: right;;">1</td><td style="text-align: right;;">28</td><td style="text-align: right;;">1900</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><td style="text-align: right;;"></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;">36</td><td style="text-align: right;;">1/29/00</td><td style="text-align: right;;">1.7</td><td style="text-align: right;;">-3.9</td><td style="text-align: right;;">1</td><td style="text-align: right;;">29</td><td style="text-align: right;;">1900</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><td style="text-align: right;;"></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></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)">Sheet2</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)">N8</th><td style="text-align:left">=MIN(<font color="Blue">ABS(<font color="Red">IF(<font color="Green">OFFSET(<font color="Purple">[@[streak high]],1,</font>)<>[@[streak high]]+1,[@[streak high]],0</font>)</font>),IF(<font color="Red">OFFSET(<font color="Green">[@[streak high]],1,</font>)<>[@[streak high]]-1,[@[streak high]],0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H8</th><td style="text-align:left">=AVERAGEIFS(<font color="Blue">weatherstats[high],weatherstats[month],MONTH(<font color="Red">weatherstats[@date]</font>),weatherstats[day],DAY(<font color="Red">weatherstats[@date]</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">J8</th><td style="text-align:left">=--(<font color="Blue">weatherstats[@high]>=[@[mean high]]</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">L8</th><td style="text-align:left">=IF(<font color="Blue">[@[wtn high]],1,-1</font>)*IF(<font color="Blue">COUNTIF(<font color="Red">$J$8:J8,$J$8</font>)=COUNTA(<font color="Red">$J$8:J8</font>),COUNTA(<font color="Red">$J$8:J8</font>),MAX(<font color="Red">ROW(<font color="Green">$J$8:J8</font>)</font>)-MAX(<font color="Red">(<font color="Green">$J$8:J8<>INDEX(<font color="Purple">$J$8:J8,ROWS(<font color="Teal">$J$8:J8</font>)</font>)</font>)*(<font color="Green">$J$8:J8<>""</font>)*ROW(<font color="Green">$J$8:J8</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />[/FONT]
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

sergioMabres

Well-known Member
Joined
Feb 24, 2013
Messages
946
Hi Steele,
You formulas seem a bit heavy so here is an idea: Why don't you do the calculations in the QUERY,, in the Data Base, is case you are using SQL Server you could run this QUERY to get the columns you need:
Code:
CREATE TABLE [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=T1"]#T1[/URL]  
(
[DATE]  DATE,
[HIGH]  FLOAT,
[LOW]   FLOAT,
[DAY]   INT,
[MONTH] INT,
[YEAR]  INT,
[mean high]   FLOAT,
[wtn high]    INT,
[streak high] INT NULL,
[sh end]      INT NULL,
)
TRUNCATE TABLE [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=T1"]#T1[/URL]  
INSERT INTO [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=T1"]#T1[/URL]  
SELECT HL1.[DATE]
      ,HL1.[HIGH]
      ,HL1.[LOW]
      ,datepart(MONTH,HL1.[date]) as [MONTH]
      ,datepart(DAY,  HL1.[date]) as [DAY]
      ,datepart(YEAR, HL1.[date]) as [YEAR]
      ,ROUND((SELECT AVG(HL2.[HIGH]) FROM HL HL2 WHERE datepart(MONTH,HL1.[date]) = datepart(MONTH,HL2.[date]) AND datepart(DAY,  HL1.[date])=datepart(DAY,  HL2.[date])),2) as [mean high]
      ,CASE WHEN HL1.[high]>=(SELECT AVG(HL2.[HIGH]) FROM HL HL2 WHERE datepart(MONTH,HL1.[date]) = datepart(MONTH,HL2.[date]) AND datepart(DAY,  HL1.[date])=datepart(DAY,  HL2.[date]))
            THEN 1 ELSE -1 END AS [wtn high]
      ,NULL,NULL
  FROM hl HL1


DECLARE @cnt INT = 0;


WHILE @cnt < 100
BEGIN
   UPDATE T1 SET 
    T1.[streak high]=
    CASE WHEN ISNULL((SELECT TOP 1 T2.[wtn high] FROM [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=T1"]#T1[/URL]   T2 WHERE T2.[DATE]=DATEADD(DAY,-1,T1.[DATE])),-T1.[wtn high])=T1.[wtn high]
         THEN (SELECT TOP 1 T2.[streak high] FROM [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=T1"]#T1[/URL]   T2 WHERE T2.[DATE]=DATEADD(DAY,-1,T1.[DATE]))+T1.[wtn high]
         ELSE T1.[wtn high] END
    FROM [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=T1"]#T1[/URL]   T1
   SET @cnt = @cnt + 1;
END;


UPDATE T1 SET 
T1.[sh end]=
CASE WHEN ISNULL((SELECT TOP 1 T2.[wtn high] FROM [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=T1"]#T1[/URL]   T2 WHERE T2.[DATE]=DATEADD(DAY,+1,T1.[DATE])),-T1.[wtn high])=T1.[wtn high]
        THEN 0
        ELSE T1.[streak high] END
FROM [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=T1"]#T1[/URL]   T1


SELECT * FROM [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=T1"]#T1[/URL]  


DROP TABLE [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=T1"]#T1[/URL]

With this you will get some thing like this
Code:
DATE    HIGH    LOW    DAY    MONTH    YEAR    mean high    wtn high    streak high    sh end
2000-01-01    -14,4    -23,9    1    1    2000    -4,25    -1    -1    0
2000-01-02    -11,7    -21,1    1    2    2000    -0,3    -1    -2    0
2000-01-03    -12,2    -21,7    1    3    2000    -1,25    -1    -3    0
2000-01-04    -12,2    -26,1    1    4    2000    -1,4    -1    -4    0
2000-01-05    -6,7    -14,4    1    5    2000    -5,6    -1    -5    0
2000-01-06    -4,4    -13,3    1    6    2000    0,65    -1    -6    0
2000-01-07    -6,7    -10    1    7    2000    -6,2    -1    -7    -7
2000-01-08    -3,3    -13,3    1    8    2000    -4,8    1    1    0
2000-01-09    3,3    -15,6    1    9    2000    -0,6    1    2    2
2000-01-10    3,9    -3,3    1    10    2000    6,85    -1    -1    0
2000-01-11    3,3    -6,7    1    11    2000    5,65    -1    -2    0
2000-01-12    3,3    -4,4    1    12    2000    6,45    -1    -3    0
2000-01-13    2,2    -6,7    1    13    2000    6    -1    -4    0
2000-01-14    2,8    -11,1    1    14    2000    6,35    -1    -5    -5
2000-01-15    -0,6    -10    1    15    2000    -1,7    1    1    0
2000-01-16    5,6    -7,8    1    16    2000    0,1    1    2    0
2000-01-17    6,1    -6,7    1    17    2000    -0,95    1    3    0
2000-01-18    10    -1,1    1    18    2000    -1,85    1    4    0
2000-01-19    6,7    -2,8    1    19    2000    5,8    1    5    0
2000-01-20    7,8    -9,4    1    20    2000    5,15    1    6    0
2000-01-21    10    -0,6    1    21    2000    4,75    1    7    7
2000-01-22    -0,6    -3,9    1    22    2000    1,75    -1    -1    0
2000-01-23    -1,1    -5,6    1    23    2000    0,35    -1    -2    0
2000-01-24    -2,8    -13,3    1    24    2000    -0,65    -1    -3    0
2000-01-25    -2,8    -15,6    1    25    2000    0,45    -1    -4    0
2000-01-26    -7,8    -13,3    1    26    2000    2,95    -1    -5    0
2000-01-27    -12,2    -21,7    1    27    2000    -0,4    -1    -6    -6
2000-01-28    7,2    -20,6    1    28    2000    2,95    1    1    0
2000-01-29    1,7    -3,9    1    29    2000    0,25    1    2    2
2019-02-01    -2,7    -16,7    2    1    2019    -7,7    1    3    0
2019-02-02    -16,5    -22,1    2    2    2019    -17,2    1    4    4
2019-02-03    -22,1    -27,8    2    3    2019    -20,4    -1    -1    0
2019-02-04    -24,8    -27,6    2    4    2019    -16,5    -1    -2    0
2019-02-05    -21,2    -28,4    2    5    2019    -16,45    -1    -3    0
2019-02-06    -16    -26,2    2    6    2019    -13,45    -1    -4    0
2019-02-07    -11    -25,5    2    7    2019    -9,85    -1    -5    -5
2019-02-08    -13,2    -23,9    2    8    2019    -14,15    1    1    1
2019-02-09    -23,9    -28,1    2    9    2019    -17,65    -1    -1    0
2019-02-10    -24    -29,2    2    10    2019    -13,85    -1    -2    0
2019-02-11    -20,9    -27,2    2    11    2019    -14    -1    -3    0
2019-02-12    -17,8    -29,6    2    12    2019    -7,45    -1    -4    0
2019-02-13    -17,1    -23,9    2    13    2019    -3,7    -1    -5    0
2019-02-14    -10,4    -22,5    2    14    2019    -2,8    -1    -6    0
2019-02-15    -16,7    -22,4    2    15    2019    -9,8    -1    -7    0
2019-02-16    -14,9    -18,1    2    16    2019    -7,95    -1    -8    0
2019-02-17    -16,4    -19,2    2    17    2019    -12,75    -1    -9    0
2019-02-18    -14,3    -23,3    2    18    2019    -14    -1    -10    -10
2019-01-01    5,9    -11,6    1    1    2019    -4,25    1    1    0
2019-01-02    11,1    4,9    1    2    2019    -0,3    1    2    0
2019-01-03    9,7    -0,6    1    3    2019    -1,25    1    3    0
2019-01-04    9,4    -6,2    1    4    2019    -1,4    1    4    0
2019-01-05    -4,5    -9,5    1    5    2019    -5,6    1    5    0
2019-01-06    5,7    -7,1    1    6    2019    0,65    1    6    0
2019-01-07    -5,7    -11,9    1    7    2019    -6,2    1    7    7
2019-01-08    -6,3    -14,2    1    8    2019    -4,8    -1    -1    0
2019-01-09    -4,5    -10,7    1    9    2019    -0,6    -1    -2    -2
2019-01-10    9,8    -10,8    1    10    2019    6,85    1    1    0
2019-01-11    8    -10    1    11    2019    5,65    1    2    0
2019-01-12    9,6    -3,6    1    12    2019    6,45    1    3    0
2019-01-13    9,8    -5,6    1    13    2019    6    1    4    0
2019-01-14    9,9    -6,9    1    14    2019    6,35    1    5    5
2019-01-15    -2,8    -9,4    1    15    2019    -1,7    -1    -1    0
2019-01-16    -5,4    -8    1    16    2019    0,1    -1    -2    0
2019-01-17    -8    -16,7    1    17    2019    -0,95    -1    -3    0
2019-01-18    -13,7    -17,4    1    18    2019    -1,85    -1    -4    0
2019-01-19    4,9    -18,3    1    19    2019    5,8    -1    -5    0
2019-01-20    2,5    -10,1    1    20    2019    5,15    -1    -6    0
2019-01-21    -0,5    -9,2    1    21    2019    4,75    -1    -7    -7
2019-01-22    4,1    -10,8    1    22    2019    1,75    1    1    0
2019-01-23    1,8    -15,9    1    23    2019    0,35    1    2    0
2019-01-24    1,5    -17,7    1    24    2019    -0,65    1    3    0
2019-01-25    3,7    -7    1    25    2019    0,45    1    4    0
2019-01-26    13,7    -6    1    26    2019    2,95    1    5    0
2019-01-27    11,4    -5,5    1    27    2019    -0,4    1    6    6
2019-01-28    -1,3    -8,6    1    28    2019    2,95    -1    -1    0
2019-01-29    -1,2    -13    1    29    2019    0,25    -1    -2    -2
2019-01-30    6,7    -9,7    1    30    2019    6,7    1    1    0
2019-01-31    9,1    -4,9    1    31    2019    9,1    1    2    0
2018-02-01    -12,7    -19,1    2    1    2018    -7,7    -1    -1    0
2018-02-02    -17,9    -20,9    2    2    2018    -17,2    -1    -2    -2
2018-02-03    -18,7    -20,1    2    3    2018    -20,4    1    1    0
2018-02-04    -8,2    -24,5    2    4    2018    -16,5    1    2    0
2018-02-05    -11,7    -20,6    2    5    2018    -16,45    1    3    0
2018-02-06    -10,9    -20,2    2    6    2018    -13,45    1    4    0
2018-02-07    -8,7    -15,3    2    7    2018    -9,85    1    5    5
2018-02-08    -15,1    -27,5    2    8    2018    -14,15    -1    -1    -1
2018-02-09    -11,4    -30,6    2    9    2018    -17,65    1    1    0
2018-02-10    -3,7    -21,4    2    10    2018    -13,85    1    2    0
2018-02-11    -7,1    -22,6    2    11    2018    -14    1    3    0
2018-02-12    2,9    -29,2    2    12    2018    -7,45    1    4    0
2018-02-13    9,7    1,9    2    13    2018    -3,7    1    5    0
2018-02-14    4,8    -14,6    2    14    2018    -2,8    1    6    0
2018-02-15    -2,9    -18,1    2    15    2018    -9,8    1    7    0
2018-02-16    -1    -9,2    2    16    2018    -7,95    1    8    0
2018-02-17    -9,1    -14,1    2    17    2018    -12,75    1    9    0
2018-02-18    -13,7    -20,6    2    18    2018    -14    1    10    0
2018-02-19    -12,9    -21,8    2    19    2018    -12,9    1    11    0
2018-02-20    -8,9    -20,9    2    20    2018    -8,9    1    12    0
2018-02-21    -7    -23,2    2    21    2018    -7    1    13    0
2018-02-22    -7,3    -15,6    2    22    2018    -7,3    1    14    0
2018-02-23    1,1    -21,3    2    23    2018    1,1    1    15    0
2018-02-24    0,1    -6,1    2    24    2018    0,1    1    16    0
2018-02-25    2    -10,6    2    25    2018    2    1    17    0
2018-02-26    -0,3    -14    2    26    2018    -0,3    1    18    0
2018-02-27    2,2    -9,8    2    27    2018    2,2    1    19    0
2018-02-28    1,9    -12    2    28    2018    1,9    1    20    20

Cheers
Sergio
 
Last edited:

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,382
Office Version
  1. 365
Platform
  1. Windows
Sergio, thanks for your reply and your effort. I wonder if this can be done in PowerQuery somehow, or even PowerPivot.
 

sergioMabres

Well-known Member
Joined
Feb 24, 2013
Messages
946
This code is for SQL Server it might run on Access but you will have to try
Sergio
 

Watch MrExcel Video

Forum statistics

Threads
1,130,083
Messages
5,639,980
Members
417,121
Latest member
DallyDally

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
Top