Search code examples
google-sheetsmingoogle-query-language

Google Sheets - Query Minimum value for each month

I am trying to fetch the minimum value for each month in a dataset like this:

1/3/2000 15:30:00   1592.2
1/4/2000 15:30:00   1638.7
1/5/2000 15:30:00   1595.8
1/6/2000 15:30:00   1617.6
1/7/2000 15:30:00   1613.3
1/10/2000 15:30:00  1632.95
1/11/2000 15:30:00  1572.5
1/12/2000 15:30:00  1624.8
1/13/2000 15:30:00  1621.4
1/14/2000 15:30:00  1622.75
1/17/2000 15:30:00  1611.6
1/18/2000 15:30:00  1606.7
1/19/2000 15:30:00  1634.85
1/20/2000 15:30:00  1601.1
1/21/2000 15:30:00  1620.6
1/24/2000 15:30:00  1613.6
1/25/2000 15:30:00  1586.4
1/27/2000 15:30:00  1603.9
1/28/2000 15:30:00  1599.1
1/31/2000 15:30:00  1546.2
2/1/2000 15:30:00   1549.5
2/2/2000 15:30:00   1588
2/3/2000 15:30:00   1597.9
2/4/2000 15:30:00   1599.75
2/7/2000 15:30:00   1636.6
2/8/2000 15:30:00   1662.75
2/9/2000 15:30:00   1689.65
2/10/2000 15:30:00  1711.2
2/11/2000 15:30:00  1756
2/14/2000 15:30:00  1744.5
2/15/2000 15:30:00  1702.55
2/16/2000 15:30:00  1711.1
2/17/2000 15:30:00  1742.1
2/18/2000 15:30:00  1717.8
2/21/2000 15:30:00  1753.5
2/22/2000 15:30:00  1739.05
2/23/2000 15:30:00  1696.4
2/24/2000 15:30:00  1732
2/25/2000 15:30:00  1710.45
2/28/2000 15:30:00  1722.55
2/29/2000 15:30:00  1654.8

I have the following query which can get the minimum value for each month, but I have trouble getting the day of the month.Any help appreciated.

=QUERY( {D:E}, 
    "select year(Col1), month(Col1)+1, min(Col2) 
     where Col1 is not null 
     group by year(Col1), month(Col1)+1", 
    1 
  )
Solution

  • I would approach it a different way, using 'FILTER' rather than 'QUERY'. Assuming that your dates and amounts begin in D2 and E2 respectively, with headers in D1 and E1, try the following formula:

    =ArrayFormula({"YEAR","MONTH","DAY","MONTHLY MIN"; FILTER({YEAR(D2:D),MONTH(D2:D),DAY(D2:D),E2:E},VLOOKUP(DATE(YEAR(D2:D),MONTH(D2:D),1),SORT(FILTER({DATE(YEAR(D2:D),MONTH(D2:D),1),E2:E},D2:D<>""),2,1),2,FALSE)=E2:E)})

    First, headers are generated (which can be changed within the formula as desired).

    This — SORT(FILTER({DATE(YEAR(D2:D),MONTH(D2:D),1),E2:E},D2:D<>""),2,1) — will convert all dates to the first of that month and then sort by amount in ascending order, leaving the minimum amounts near the top and, therefore, the first to be found by the VLOOKUP.

    FILTER then returns the YEAR, MONTH, DAY and amount only for rows where looking up the date-converted-to-first-of-month for that row in the SORT array returns the same amount as is listed in Col E for that row.