DRSteele
Well-known Member
- Joined
- Mar 31, 2015
- Messages
- 2,652
- 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]
[/FONT]
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]
Book1 | |||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |||
5 | This source data comes from a query | ||||||||||||||||
6 | |||||||||||||||||
7 | date | high | low | month | day | year | mean high | mean low | wtn high | wtn low | streak high | streak low | sh end | sl end | |||
8 | 1/01/00 | -14.4 | -23.9 | 1 | 1 | 1900 | -3.23 | 0 | -1 | 0 | |||||||
9 | 1/02/00 | -11.7 | -21.1 | 1 | 2 | 1900 | -2.67 | 0 | -2 | 0 | |||||||
10 | 1/03/00 | -12.2 | -21.7 | 1 | 3 | 1900 | -2.89 | 0 | -3 | 0 | |||||||
11 | 1/04/00 | -12.2 | -26.1 | 1 | 4 | 1900 | -3.43 | 0 | -4 | 0 | |||||||
12 | 1/05/00 | -6.7 | -14.4 | 1 | 5 | 1900 | -3.82 | 0 | -5 | -5 | |||||||
13 | 1/06/00 | 4.4 | -13.3 | 1 | 6 | 1900 | -3.07 | 1 | 1 | 1 | |||||||
14 | 1/07/00 | -6.7 | -10 | 1 | 7 | 1900 | -2.68 | 0 | -1 | -1 | |||||||
15 | 1/08/00 | -3.3 | -13.3 | 1 | 8 | 1900 | -3.58 | 1 | 1 | 0 | |||||||
16 | 1/09/00 | 3.3 | -15.6 | 1 | 9 | 1900 | -3.14 | 1 | 2 | 0 | |||||||
17 | 1/10/00 | 3.9 | -3.3 | 1 | 10 | 1900 | -3.78 | 1 | 3 | 0 | |||||||
18 | 1/11/00 | 3.3 | -6.7 | 1 | 11 | 1900 | -4.15 | 1 | 4 | 0 | |||||||
19 | 1/12/00 | 3.3 | -4.4 | 1 | 12 | 1900 | -3.09 | 1 | 5 | 0 | |||||||
20 | 1/13/00 | 2.2 | -6.7 | 1 | 13 | 1900 | -3.04 | 1 | 6 | 0 | |||||||
21 | 1/14/00 | 2.8 | -11.1 | 1 | 14 | 1900 | -4.04 | 1 | 7 | 0 | |||||||
22 | 1/15/00 | -0.6 | -10 | 1 | 15 | 1900 | -3.43 | 1 | 8 | 0 | |||||||
23 | 1/16/00 | 5.6 | -7.8 | 1 | 16 | 1900 | -2.74 | 1 | 9 | 0 | |||||||
24 | 1/17/00 | 6.1 | -6.7 | 1 | 17 | 1900 | -2.84 | 1 | 10 | 0 | |||||||
25 | 1/18/00 | 10 | -1.1 | 1 | 18 | 1900 | -2.42 | 1 | 11 | 0 | |||||||
26 | 1/19/00 | 6.7 | -2.8 | 1 | 19 | 1900 | -3.27 | 1 | 12 | 0 | |||||||
27 | 1/20/00 | 7.8 | -9.4 | 1 | 20 | 1900 | -2.38 | 1 | 13 | 0 | |||||||
28 | 1/21/00 | 10 | -0.6 | 1 | 21 | 1900 | -2.57 | 1 | 14 | 0 | |||||||
29 | 1/22/00 | -0.6 | -3.9 | 1 | 22 | 1900 | -3.00 | 1 | 15 | 0 | |||||||
30 | 1/23/00 | -1.1 | -5.6 | 1 | 23 | 1900 | -3.04 | 1 | 16 | 0 | |||||||
31 | 1/24/00 | -2.8 | -13.3 | 1 | 24 | 1900 | -3.83 | 1 | 17 | 0 | |||||||
32 | 1/25/00 | -2.8 | -15.6 | 1 | 25 | 1900 | -4.18 | 1 | 18 | 18 | |||||||
33 | 1/26/00 | -7.8 | -13.3 | 1 | 26 | 1900 | -3.47 | 0 | -1 | ||||||||
34 | 1/27/00 | -12.2 | -21.7 | 1 | 27 | 1900 | |||||||||||
35 | 1/28/00 | 7.2 | -20.6 | 1 | 28 | 1900 | |||||||||||
36 | 1/29/00 | 1.7 | -3.9 | 1 | 29 | 1900 | |||||||||||
Sheet2 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
N8 | =MIN(ABS(IF(OFFSET([@[streak high]],1,)<>[@[streak high]]+1,[@[streak high]],0)),IF(OFFSET([@[streak high]],1,)<>[@[streak high]]-1,[@[streak high]],0)) | |
H8 | =AVERAGEIFS(weatherstats[high],weatherstats[month],MONTH(weatherstats[@date]),weatherstats[day],DAY(weatherstats[@date])) | |
J8 | =--(weatherstats[@high]>=[@[mean high]]) | |
L8 | =IF([@[wtn high]],1,-1)*IF(COUNTIF($J$8:J8,$J$8)=COUNTA($J$8:J8),COUNTA($J$8:J8),MAX(ROW($J$8:J8))-MAX(($J$8:J8<>INDEX($J$8:J8,ROWS($J$8:J8)))*($J$8:J8<>"")*ROW($J$8:J8))) |