How to find the times of the daily cycle maximum and minimum in excel

Summary

Use average hourly ozone concentrations to find out during which hour of the day the ozone concentrations are highest and during which hour of the day the ozone concentrations are lowest using an INDEX MATCH formula.

Step by Step guide

1. I’m using a table that I made in a previous post. The data comes from the UK-AIR DEFRA website and is hourly atmospheric ozone concentrations at Weybourne Atmospheric Observatory in the UK 2010-2014.

2. Find the maximum value

=MAX(C3:C26)

3. Find the minimum value

=MIN(C3:C26)

4. Do maximum minus minimum to get the range

=C28-C30

5. Use the following INDEX MATCH formula to find when the maximum occurs.

=INDEX($B$3:$B$26,MATCH(C28,C3:C26,0))

=INDEX(array, row_num)
The array is the first column with the hours in it. Press F4 to insert the dollar signs to turn this into an absolute cell reference so it doesn’t change when the formula is dragged across.
The row_num is the MATCH function
=MATCH(lookup_value, lookup_array, [match_type])
The lookup_value is the value we want to find, which is the maximum value
The lookup_array is the range we want to find the lookup_value in, which is the second column in the table.
The match_type has to be zero for an Exact Match.
The MATCH function gives a number based on the position of the lookup_value in the lookup_array.
This then becomes the row_num in the INDEX function.

6. Repeat Step 5. but this time use the following INDEX MATCH formula to find when the minimum occurs.

=INDEX($B$3:$B$26,MATCH(C30,C3:C26,0))

7. Use Ctrl to select both the max and min hour and then press Ctrl + 1 to open the Format Cells box, and change the number format to the custom number format, hh:mm

8. Find the amount of time between the max and min by doing the max hour minus the min hour

=C29-C31

9. Press Ctrl + 1 again to format the Range Hour, and change the custom number format to “h” so it only shows the number of hours between when the maximum occurs and when the minimum occurs