Search code examples
dateexcel-formulamin

How do I choose the minimum value from rows within certain hour range?

I have one row per hour for each day. For hours 06:00 to 21:00 I need to use the lowest values ( minimum value) found in Column Price of hours 22:00 (day before) to 05:00 and store it in the column Lowest on each row for hours 06:00 to 21:00. Else column lowes will show the same value as in Price.

How should the Excel formula in column Lowest look like to solve this?

How I need it to look (minimum value shown in Bold)

Date & hour DatePart Hour Price Lowest
2018-01-01 00:00 2018-01-01 00:00 258,86 258,86
2018-01-01 01:00 2018-01-01 01:00 259,85 259,85
2018-01-01 02:00 2018-01-01 02:00 256,6 256,6
2018-01-01 03:00 2018-01-01 03:00 242,84 242,84
2018-01-01 04:00 2018-01-01 04:00 243,23 243,23
2018-01-01 05:00 2018-01-01 05:00 177,07 177,07
2018-01-01 06:00 2018-01-01 06:00 174,8 177,07
2018-01-01 07:00 2018-01-01 07:00 175 177,07
2018-01-01 08:00 2018-01-01 08:00 194,27 177,07
2018-01-01 09:00 2018-01-01 09:00 203,81 177,07
2018-01-01 10:00 2018-01-01 10:00 243,43 177,07
2018-01-01 11:00 2018-01-01 11:00 252,47 177,07
2018-01-01 12:00 2018-01-01 12:00 236,84 177,07
2018-01-01 13:00 2018-01-01 13:00 245,89 177,07
2018-01-01 14:00 2018-01-01 14:00 253,75 177,07
2018-01-01 15:00 2018-01-01 15:00 260,14 177,07
2018-01-01 16:00 2018-01-01 16:00 265,75 177,07
2018-01-01 17:00 2018-01-01 17:00 269,68 177,07
2018-01-01 18:00 2018-01-01 18:00 268,3 177,07
2018-01-01 19:00 2018-01-01 19:00 265,06 177,07
2018-01-01 20:00 2018-01-01 20:00 262,5 177,07
2018-01-01 21:00 2018-01-01 21:00 260,24 177,07
2018-01-01 22:00 2018-01-01 22:00 256,5 256,5
2018-01-01 23:00 2018-01-01 23:00 244,61 244,61
2018-01-02 00:00 2018-01-02 00:00 248,54 248,54
2018-01-02 01:00 2018-01-02 01:00 227,7 227,7
2018-01-02 02:00 2018-01-02 02:00 243,62 243,62
2018-01-02 03:00 2018-01-02 03:00 246,08 246,08
2018-01-02 04:00 2018-01-02 04:00 252,96 252,96
2018-01-02 05:00 2018-01-02 05:00 263,88 263,88
2018-01-02 06:00 2018-01-02 06:00 273,32 227,7
2018-01-02 07:00 2018-01-02 07:00 299,86 227,7
2018-01-02 08:00 2018-01-02 08:00 313,92 227,7
2018-01-02 09:00 2018-01-02 09:00 329,65 227,7
2018-01-02 10:00 2018-01-02 10:00 344,5 227,7
2018-01-02 11:00 2018-01-02 11:00 346,27 227,7
2018-01-02 12:00 2018-01-02 12:00 339,78 227,7
2018-01-02 13:00 2018-01-02 13:00 335,25 227,7
2018-01-02 14:00 2018-01-02 14:00 353,74 227,7
2018-01-02 15:00 2018-01-02 15:00 374,09 227,7
2018-01-02 16:00 2018-01-02 16:00 409,68 227,7
2018-01-02 17:00 2018-01-02 17:00 416,76 227,7
2018-01-02 18:00 2018-01-02 18:00 371,53 227,7
2018-01-02 19:00 2018-01-02 19:00 331,32 227,7
2018-01-02 20:00 2018-01-02 20:00 303,6 227,7
2018-01-02 21:00 2018-01-02 21:00 283,64 227,7
2018-01-02 22:00 2018-01-02 22:00 275,18 275,18
2018-01-02 23:00 2018-01-02 23:00 271,35 271,35
Solution

  • First Method - volatile and lazy

    You could go with this formula in row 2 of your Lowest column (where Lowest is in column E) and copied down:

    =IF(C2=TIME(6,0,0), MIN(OFFSET(D2,-MIN(8,ROW()-1),0,MIN(8,ROW()-1),1)),
   IF( (C2 > TIME(6,0,0))*(C2 < TIME(22,0,0)), E1,
       D2) )

    I put mine on the side of yours and called it Low to test if the correct answer was reached, where you have your table starting in A1 like this:

    result

    In an Excel table (ctrl t) it is more readable like this:

    =IF([@Hour]=TIME(6,0,0), MIN(OFFSET([@Price],-MIN(8,ROW()-1),0,MIN(8,ROW()-1),1)),
   IF( ([@Hour] > TIME(6,0,0))*([@Hour] < TIME(22,0,0)), E1,
        [@Price]) )

    Craner Method - non-volatile and non-lazy

    This used INDEX instead of OFFSET as proposed by Scott Craner - should make the worksheet more responsive.

    =IF(C2=TIME(6,0,0), MIN(INDEX(D:D,MAX(1,ROW()-8)):INDEX(D:D,ROW()-1)),
   IF( (C2 > TIME(6,0,0))*(C2 < TIME(22,0,0)), F1,
        D2) )

    or in Excel Table:

    =IF([@Hour]=TIME(6,0,0), MIN(INDEX(D:D,MAX(1,ROW()-8)):INDEX(D:D,ROW()-1)),
   IF( ([@Hour] > TIME(6,0,0))*([@Hour] < TIME(22,0,0)), H1,
        [@Price]) )