Hi, I am designing a hydrology sheet where you put in average discharge values from time series and then get the results from different parameters, I am not very experienced in excel though. The time series shows discharge values for every day in ten years.
Now, I want to calculate average minimum value for every MONTH in the time series.
Ideal would be a formula that picks out the minimum value for all january MONTHS for all YEARS in the time series and then calculate the average value out of those.
However I am uncertain how to do this, and since I have a lot of columns in the sheet I would like to avoid adding too many columns, the ideal would be if the formula could just use the existing columns without the need to create new columns with data of minimum values for every single month or similar.
Now, I want to calculate average minimum value for the month of january in the time series.
Currently, my formula looks like this:
=MIN(FILTER($E$78:$E$4095;COUNTIF(C63;$I$78:$I$4095)))
Where
E = Discharge values from time series
C = The actual month I want to know the minimum value of
I = Month for corresponding discharge value in the time series
so...
=MIN(FILTER($"values from time series";COUNTIF("month of interest";"corresponding months in time series")))
Besides the mentioned columns/variables; I also have a column with the current year in the time series.
The problem with my formula is that it picks out of the minimum value for ALL january months together in the time series, when I want to know the AVERAGE minimum value for the month of january.
Can I solve this problem by just changing my formula, without creating a lot of columns with new data?
Simplified excel example sheet for modification is attached. (the formula should handle much longer data series and be used for all months in the actual sheet)
I also add a picture of my actual excel sheet to provide information about how much data is actually in the sheet if it helps but I think the description above probably describes my problem better.
excelproblem.jpg
Bookmarks