Use MAXIFS with a calcuated parameter

[art3mis]

Hi all,

I'd like to use MAXIFS to find the max energy demand between certain hours (16:00 to 21:00). Energy values are in column C and Date/Time is in column A.

My desired function: =MAXIFS(C3:C288,A3:A2882,AND(HOUR(__)>=16,HOUR(__)<21)) I'm stuck because the HOUR function only takes in a serial number and not an array.

I got it to work by using another column to calculate the hour and running MAXIFS on this, but I'd like to know if there's a way to accomplish this without making another column.

Thanks!

[63falcondude]

Hello and welcome to the forum. Give this a try:

=MAX(IF((HOUR(A3:A288)>=16)*(HOUR(A3:A288)<21),C3:C288) Ctrl Shift Enter

[FlameRetired]

Withdrawn by FR. Double posted.

[FlameRetired]

I'm stuck because the HOUR function only takes in a serial number and not an array.

You might be surprised. If you insert +before the range (sometimes N(+range) and array enter you can coerce ranges into acceptable arrays).

Also if you are not aware of it the IFS family of functions requires building strings for comparison criteria. i.e. something like "<="&16

Play with it. If you are still stuck upload a representative sample Excel workbook with data consistent with what you describe.


To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),

• be sure to desensitize the data
• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

The file name will appear at the bottom of your reply.

[art3mis]

Hi 63falcondude,

Thanks so much, that worked perfectly!

[63falcondude]

You're welcome. Happy to help.

Thanks for the rep!