# Thread: Averages per Day Thanks: 0 Likes:  1 Post #5229737 (1)

1. ## Averages per Day

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?

ABCDEFGHIJKLMNO
5This source data comes from a query
6
7datehighlowmonthdayyearmean highmean lowwtn highwtn lowstreak highstreak lowsh endsl end
81/01/00-14.4-23.9111900-3.230-10
91/02/00-11.7-21.1121900-2.670-20
101/03/00-12.2-21.7131900-2.890-30
111/04/00-12.2-26.1141900-3.430-40
121/05/00-6.7-14.4151900-3.820-5-5
131/06/004.4-13.3161900-3.07111
141/07/00-6.7-10171900-2.680-1-1
151/08/00-3.3-13.3181900-3.58110
161/09/003.3-15.6191900-3.14120
171/10/003.9-3.31101900-3.78130
181/11/003.3-6.71111900-4.15140
191/12/003.3-4.41121900-3.09150
201/13/002.2-6.71131900-3.04160
211/14/002.8-11.11141900-4.04170
221/15/00-0.6-101151900-3.43180
231/16/005.6-7.81161900-2.74190
241/17/006.1-6.71171900-2.841100
251/18/0010-1.11181900-2.421110
261/19/006.7-2.81191900-3.271120
271/20/007.8-9.41201900-2.381130
281/21/0010-0.61211900-2.571140
291/22/00-0.6-3.91221900-3.001150
301/23/00-1.1-5.61231900-3.041160
311/24/00-2.8-13.31241900-3.831170
321/25/00-2.8-15.61251900-4.1811818
331/26/00-7.8-13.31261900-3.470-1
341/27/00-12.2-21.71271900
351/28/007.2-20.61281900
361/29/001.7-3.91291900

Sheet2

Worksheet Formulas
CellFormula
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)))

2. ## Re: Averages per Day

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 #T1
(
[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 #T1
INSERT INTO #T1
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 #T1   T2 WHERE T2.[DATE]=DATEADD(DAY,-1,T1.[DATE])),-T1.[wtn high])=T1.[wtn high]
THEN (SELECT TOP 1 T2.[streak high] FROM #T1   T2 WHERE T2.[DATE]=DATEADD(DAY,-1,T1.[DATE]))+T1.[wtn high]
ELSE T1.[wtn high] END
FROM #T1   T1
SET @cnt = @cnt + 1;
END;

UPDATE T1 SET
T1.[sh end]=
CASE WHEN ISNULL((SELECT TOP 1 T2.[wtn high] FROM #T1   T2 WHERE T2.[DATE]=DATEADD(DAY,+1,T1.[DATE])),-T1.[wtn high])=T1.[wtn high]
THEN 0
ELSE T1.[streak high] END
FROM #T1   T1

SELECT * FROM #T1

DROP TABLE #T1```
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

3. ## Re: Averages per Day

Sergio, thanks for your reply and your effort. I wonder if this can be done in PowerQuery somehow, or even PowerPivot.

4. ## Re: Averages per Day

This code is for SQL Server it might run on Access but you will have to try
Sergio