Forum Discussion
Extracting Max data from hourly electricity data
Thank youmtarler
I am looking to breakdown this information by the hour though not day
so I need the max number for all hour 1 entries within a month then hour 2 etc etc
(If you have the month and hour-of-day values stored in separate columns, my solution would be trivially different, but I will assume you do not have those columns. I will assume you have date-time values in column A.)
I believe you will need a column of grouping data; let's assume you can put that into column F. The formula to put in column F (row 4 shown here) might be:
="M" & TEXT(MONTH(A4),"00") & "h" & TEXT(HOUR(A4),"00")
(The "M" and "h" literals are not required, but aid the reader in interpreting the values. Using the TEXT function allows the generated values to be sortable in chronological order.)
With that column populated, you could sort the data on it and use the Excel Subtotals feature to create new rows that contain the maxima, but that's messy, and you likely do not want that complication.
Or, with the electrical power data in column E, a formula (for each data row) similar to what you want is:
=MAXIFS( E4:E99, F4:F99, F4 )
This selection criterion is an equality match (by default) on the value in F4, comparing values through the range F4:F99. And if you just copy that formula down, Excel's automatic cell reference changes will work acceptably if the data is (at least approximately) sorted by date.
Note that with the F-column values for grouping, you don't have to be concerned about the exact size of your computational "window" (criteria range, in Microsoft terminology; 31 rows in your example), and you don't even have to have the data in any particular order, unless you have more than a year of data in the worksheet or are greatly concerned about recalculation performance. This "window" can cover all of the data rows (and likely the rows immediately beneath them). (If you had a maximum number of data rows in the worksheet (say, 8784, =24*366), you might consider using fixed ranges such as F$4:F$8787.)
But there is a design problem here: A varying (non-fixed) MAXIFS "window" (criterion range) will by default look forward only, so unless you can use a range where the top row is an absolute reference, the calculated values are reliable for the first occurrence only for any particular value in the F column. Also, there are unnecessarily many cells performing the calculation.
Better design: Either in a separate worksheet, or in a separate area of this worksheet, populate a range of cells (probably one cell in each of multiple rows, although one cell in each of multiple columns is just as doable, as is a row x column array) with the month-hour identifier values that you know can be created. Then populate other cells (right/down) with a formula similar to the last, such as looking for the power maximum whose month-hour identifier is in M7:
=MAXIFS( <Your_Data_Wksht_Name>!E$4:E$8787, <Your_Data_Wksht_Name>!F$4:F$8787, M7 )
...if you are using a separate worksheet, or:
=MAXIFS( E$4:E$8787, F$4:F$8787, M7 )
...if you are using a separate area on the same worksheet.
One advantage of this technique is that Excel has similar SUMIFS and AVERAGEIFS (and MINIFS...) functions with the same syntax, so you can get sums (if it makes sense, such as energy values rather than power values) and averages by copying the text of a formula and pasting it into another cell, changing only the function name.
Edit: corrected the last set of formulas; somehow a reference to column J was included
- I think what is needed is to use MAX(FILTER()) combination. So let "name" the date-time date to be DT and name the corresponding value data as V then:
=MAX(FILTER(V, (MONTH(DT)=[month])*(HOUR(DT)=[hour]), 0))
the [month] and [hour] inputs could be based on ROW() since each month would have 24 rows but since you probably want to know what each row is for, you can/should create a column indicating each hour for each month and then you could use MONTH() and HOUR() of that corresponding column. If this sounds right but you have trouble doing it I could create a sample sheet.
