Averages per Day
Results 1 to 4 of 4

Thread: Averages per Day

  1. #1
    Board Regular DRSteele's Avatar
    Join Date
    Mar 2015
    Location
    Calgary
    Posts
    1,972
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Averages per Day

    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?

    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)))

    Windows10, Excel 365 Insider
    If you don't declare what version of Excel, we will assume
    Windows10, Excel 365.
    Formulas here are always in
    Green background
    You can get the HTML Maker: https://www.mrexcel.com/forum/excel-...ins-links.html.

  2. #2
    Board Regular sergioMabres's Avatar
    Join Date
    Feb 2013
    Location
    Córdoba, Argentina
    Posts
    920
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    Last edited by sergioMabres; Feb 20th, 2019 at 10:20 AM.

  3. #3
    Board Regular DRSteele's Avatar
    Join Date
    Mar 2015
    Location
    Calgary
    Posts
    1,972
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default 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.
    Windows10, Excel 365 Insider
    If you don't declare what version of Excel, we will assume
    Windows10, Excel 365.
    Formulas here are always in
    Green background
    You can get the HTML Maker: https://www.mrexcel.com/forum/excel-...ins-links.html.

  4. #4
    Board Regular sergioMabres's Avatar
    Join Date
    Feb 2013
    Location
    Córdoba, Argentina
    Posts
    920
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Averages per Day

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •