# Averages per Day

#### DRSteele

##### Well-known Member

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?

### 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
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
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
This code is for SQL Server it might run on Access but you will have to try
Sergio

Replies
4
Views
849
Replies
3
Views
886
Replies
0
Views
666
Replies
1
Views
563
Replies
5
Views
1K

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.

### Which adblocker are you using?

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

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