Date of Max Value in Date Range

[wobble12]

I have a Table with over 7000 rows of flow data (daily totals), and four columns.

Columns are:
Date
Flow from River
Flow to River
Bio In

I have used maxif to find the max value for a given date range (either quarterly or yearly), but I would like to return the date that the max value occurred.

I need a formula for the date for "Flow to River" max in a given date range please.

Does anyone have a formula to acheive this please?

All data is in date order.

 

[wobble12]

I should add that if a blank value occurs, it should ignore it.

 

[arthurbr]

With dates in col A (a1:a8) and values in col B I built a formula like

=FILTER(A1:A8,B1:B8=MAX(FILTER(B1:B8,(A1:A8>A2)*(B1:B8<A7),"")),"")

It works, but it's probably overkill (BTA it's the first time I use the FILTER function which is part of O365)
So you should add your XL version to your profile to avoid losing time eventually

 

[wobble12]

Thank you!
Good point about the version. We are running Excel365, so have spill functions, and maxifs etc.

 

[wobble12]

This partially works, and the way it doesn't work is weird.
When I set the date range for Q1 & Q2, it works, but Q3 & Q4, it wants to do spills of dates for some reason.

And when I try to do column C (Flow to River (m3) , and also wants to spill to three dates.
So this works:
=FILTER(TableFlow[Date],TableFlow[Flow from River (m3)]=MAX(FILTER(TableFlow[Flow from River (m3)],(TableFlow[Date]>DATE($L$20,1,1))*(TableFlow[Date]<DATE($L$20,4,1)),"")),"")

But this doesn't:
=FILTER(TableFlow[Date],TableFlow[Flow from River (m3)]=MAX(FILTER(TableFlow[Flow from River (m3)],(TableFlow[Date]>DATE($L$20,10,1))*(TableFlow[Date]<DATE($L$20+1,1,1)),"")),"")

And this doesn't work either (non-contiguous columns, but at the same rows):
=FILTER(TableFlow[Date],TableFlow[Flow to River (m3)]=MAX(FILTER(TableFlow[Flow to River (m3)],(TableFlow[Date]>DATE($L$20,1,1))*(TableFlow[Date]<DATE($L$20,4,1)),"")),"")

The ones that don't work seem to spill, and also not display the correct dates

 

[Fluff]

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[arthurbr]

And of course there is the MAXIFS function...

 

[wobble12]

And of course there is the MAXIFS function...

I used maxifs to find the actual max values for each quarter and year, but I can't seem to use it to get the date of the max value.

Its a function that I was most pleased when it came out.

 

[Fluff]

Please see post#6

 

[wobble12]

Unfortunately, our IT guys aren't in today, so unable to install anything. I'll try on Monday.

Thank you again.