Averages per Day

DRSteele

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,640
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]
Book1
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
Cell Formulas
RangeFormula
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)))
[/FONT]
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
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:
Upvote 0
Sergio, thanks for your reply and your effort. I wonder if this can be done in PowerQuery somehow, or even PowerPivot.
 
Upvote 0
You must log in or register to reply here.

Similar threads

PS_Richard
  • Solved
Calculate Time Difference Per Day Based On Criteria
Replies
5
Views
573
Anthony47
A
J
  • Question
Need help with scheduling formulas
Replies
0
Views
462
jordan89m
J
M
  • Solved
Improving my formulas/approach for date/time analysis
Replies
9
Views
904
mediumrare
M
L
  • Question
Formula to check number of days productive and non Productive
Replies
2
Views
379
FMHasan
FMHasan
A
  • Question
Exclusion formula
Replies
1
Views
542
andrewmurray86
A

Forum statistics

Threads
1,214,386
Messages
6,119,216
Members
448,876
Latest member
Solitario

Share this page

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
Back
Top