Finding MAX per hour interval when Date is "DD/MM/YYYY HH:MM"

[bernborough]

Hello,

My attachment didn't seem to load but it was a very simple two columns of data.

Column A has dates in the format "DD/MM/YYYY HH:MM" (24hr clock in Half hour intervals). And column B has numerical data.

There's two things I want to be able to do:

What formula finds me the maximum figure for the month?

What formula both finds me the maximum figure for any Hour period Per Month AND also returns whatever the column A Date value is for that figure.

Your help would be greatly appreciated.

Thanks

Trent

[johnjohns]

Even if you cannot post the entire workbook, provide a sample one (may be a part of the original) that briefs your issue. To the extent I understood your problem, this is what I could suggest.....see the attachment

PS
for array formulas (in curly braces) please save them with Ctrl+Shft+Enter

rgds

johnjohns

[DonkeyOte]

I can't help thinking you would be better off using a Pivot Table...

In terms of the PT configuration

Col A as Row Field

Col B as Data Field - set to Max (rather than Sum)

Group Col A by Months, Years & Hour

Subtotal "Months" to get the Monthly Max values.

Once created it may be the case that you would prefer to have Months as a Column Field rather than as a Row Field
ie move the fields around until you get a layout you're happy with

See attached for proof of concept

[bernborough]

John Johns,

Those formulas you have offered are pretty much what I am after. Thanks alot.

Your formula for "Max Hr for Mar" returns 17 because it searches all hours that have been registered for March. Whereas the answer I would like it to return is 11, which would be the hour that corresponds to the "Max Value for"=Mar result. What formula would return that? I can only think of a VLOOKUP but I might be over complicating it?

Your help is very apreciated.
Thanks,

Trent

[bernborough]

Hi Donkeyote,

Your reply was very enlightening. Doing it this way also interests me?

But what were the steps you took to get the Pivot loking that way? Plus, are those values in the Pivot correct? I did some spot checks of the raw data and I couldn't find the values in the Pivot in the raw data.

Thanks,
Trent

[johnjohns]

Hi Bernborough,

I have attached the w.book with your requirement. But I would say, Donkeyote's approach is something that is advised by all the excel experts. I could not suggest you that as I am also not that good in pivoting, except for the basics. And I am learning it from people like him thru their posts in this forum. More over when it comes to larger amount of data, array formulas are not advisable also, as they consume huge memory

rgds

johnjohns

[DonkeyOte]

Plus, are those values in the Pivot correct? I did some spot checks of the raw data and I couldn't find the values in the Pivot in the raw data.

Curses... I used some RAND functions to quickly populate the source values and having set the Pivot up I must have recalculated before I pasted over as just values - and I did not refresh the PT - ie you should.

To confirm ... if you refresh the PT and drill into the Grand Total you should find the SUM of Column B in the drill through sheet matches that of Col B on Sheet2

But what were the steps you took to get the Pivot loking that way?

Pretty much per the notes in my earlier post.

Having placed Date in the Row Field and Value in the Data Field you can do the following (am assuming pre XL2007 here)

Right click on any date in the Pivot -> Group and Show Detail -> Group -> select Years, Months and Hours and click OK

Right click on any of the new "Year" values that have appeared -> Field Settings -> set Subtotals to Automatic and click OK

Repeat for Months

Right click on any value in the data field -> Field Settings -> select Max and click OK

Now at this stage you have a loooong table so visually this isn't great... to get a slightly better view we can drag the field title Date (ie hours) from Row Field to Column Field - simply click on "Date" and drag it to the box above "Total"

You should now find you have a matrix of Years/Months on the vertical axis and Hours on the horizontal axis with MAX values assigned to each intersecting point in the table below

In terms of modifying the layout of the PT - you can use the PT Wizard (ALT + D -> P) and click Layout on resulting dialog and work from there - some prefer this method.