Finding Minimum Value for each Year in Large River Flow Database

[The Venerable Heed]

Hi,

I'm a geology student and I've been set an assignment based around data from the National River Flow Archive (http://www.ceh.ac.uk/data/nrfa/index.html). The data for each station gives the average river flow for each day since records began (for the five different rivers I'm looking at this is between 1957 and 1976) up to the end of 2010. It is presented in the form of two columns, the left gives the date and the right gives the flow. What I need is the minimum flow value for each year, for all five rivers. I could do this manually, but as I'm sure you can imagine there is a lot of data here and I was hoping there might be a more efficient way of going about it. If I could use the sort function to sort by year then by flow that'd be fine, but it seems I can only sort by date. Any suggestions would be hugely appreciated and would save me hours of tedium!

[Melvinrobb]

You would find your answer by using the the Sumproduct function.
I would suggest attaching the data in excel form to get assistance with constructing the formula.

[BiIntel]

Hey TVH
Can you send file across?

Rgds
BI.

[biocidej]

Perhaps this will not be the 'best' solution, but it would certainly get you what you are looking for.

With Dates in cells A3:A1000 and Flows in cells B3:B1000 this formula will tell you the minimum for a given date in this example 1979.
=MIN(IF(YEAR($A3:$A1000)=1979,$B3:$B1000,1E+99))

If there are no dates in the given year then this formula returns 1E+99 (that's scientific notation for a really huge number which will never be the MIN)
This is an ARRAY formula so you will need to confirm it by pressing CTRL+SHIFT+ENTER after typing it.
If done correctly, Excel will put { } around the formula.

[K m]

Your link only shows the web site. If you are asking for help, you should have extracted at least a subset of the data so we could see what it looks like
=========================
You indicated that you had two columns: the left gives the date and the right gives the flow

Where are the rivers indicated?

=============================
You may need to add some columns

Assuming you "extract" the data so that you have a column with the year
Assuming you "extract" the data so that you have a column with only the river, ie River "A"
You could filter the data so you could show only specific data (Example, River "A" for 2011)
Using Subtotal function you could show the minimum value for a given river for a given year

Then refilter the data for the other time periods and other rivers

[The Venerable Heed]

@biocidej that worked for me. I made a list of all the relevant years in the C column, entered the formula in the D column next to the first year with the box containing the year in place of the year itself as in your example (so =MIN(IF(YEAR($A3:$A1000)=C3,$B3:$B1000,1E+99)) instead of =MIN(IF(YEAR($A3:$A1000)=1979,$B3:$B1000,1E+99))) then copied it for every year. Took five minutes instead of many boring hours - thanks a lot.

[biocidej]

That is what I intended, but I figured it was more clear to use the actual date than another arbitrary cell reference.
Glad it is working for you.

[Cutter]

@ The Venerable Heed

Welcome to the forum.

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.