Oct 11 2022 11:20 AM
Oct 11 2022 11:20 AM
Currently I am using =MAX(E10:E41)
And so on
Is there a way to =MAX(E10 PLUS 30 MORE ENTRIES..
Then the next line would know to start after that
I am basically aggregating hourly electrical data in graphs for each month so I can see the demand curve and change in consump
Oct 11 2022 11:57 AM
Oct 11 2022 12:22 PM
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
Oct 12 2022 07:22 AM - edited Oct 12 2022 07:27 AM
(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
Oct 12 2022 07:52 AM