Averages per Day

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,236
Office Version
365
Platform
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]
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

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,236
Office Version
365
Platform
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,099,465
Messages
5,468,791
Members
406,607
Latest member
mario antonio

This Week's Hot Topics

Top