For a given X Value Have Excel Tell me the Min/Max/Avg of the Corresponding Y Value

[robalan88]

I have 3 columns of values that are collected observed data from a streamflow gage. The data was collected on a daily basis for 40+ years. The data collected is streamflow and specific conductance. Specific conductance values can vasry for a given value of streamflow during the 40+ years of daily data collected. I would like to generate a summary that tells me the minimum/maximum/average value of specific conductance for a given value of streamflow. A sample of my data is below:


Date Streamflow Specific Conductance
----------------------------------------------------------------
01/23/1980 2000 231
01/21/1988 2010 223
02/11/1971 2010 239
03/02/1992 2010 254
01/16/1972 2030 220
02/19/1989 2030 240

What I want generated is something like this:

Streamflow Count Min Max Avg Date1 Date2 Date3
----------------------------------------------------------------------------------------------------------------------
2000 1 231 231 231.00 01/23/1980
2010 3 223 254 238.67 01/21/1988 02/11/1971 03/02/1992
2030 2 220 240 230.00 01/16/1972 02/19/1989


The summary of date(s) collected is optional but it might be useful to me down the road during my data analysis.


Thanks

Rob

 

[AD_Taylor]

Assuming the data is on Sheet1 and the summary is on another sheet this will work:

Excel 2010
	A	B	C	D
1	Streamflow	Min SC	Max SC	Avg SC
2	2000	231	231	231
3	2010	223	254	238.66667
4	2030	220	240	230
Summary

Cell Formulas
Range	Formula
B2	{=MIN(IF(Sheet1!$B$2:$B$7=$A2, Sheet1!$C$2:$C$7, ""))}
C2	{=MAX(IF(Sheet1!$B$2:$B$7=$A2, Sheet1!$C$2:$C$7, ""))}
D2	{=AVERAGE(IF(Sheet1!$B$2:$B$7=$A2, Sheet1!$C$2:$C$7, ""))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Not sure about the dates unfortunately. As you have a large amount of them it could be difficult to pull out reliably.

 

[MickG]

Try this:-
Results start "E1"
NB:- Fomat cells accordingly !!

[COLOR="Navy"]Sub[/COLOR] MG26Mar57
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Q
[COLOR="Navy"]Dim[/COLOR] K
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Dt [COLOR="Navy"]As[/COLOR] Range
Application.ScreenUpdating = False
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, Dn
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]Set[/COLOR] .Item(Dn.Value) = Union(.Item(Dn.Value), Dn)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
c = 1
Range("E1").Resize(, 5) = Array("Streamflow", "Count", "Min", "Max", "Avg")
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
    c = c + 1
    Cells(c, "E") = K
    Cells(c, "F") = .Item(K).Count
    Cells(c, "G") = Application.Min(.Item(K).Offset(, 1))
    Cells(c, "H") = Application.Max(.Item(K).Offset(, 1))
    Cells(c, "I") = Format(Application.Average(.Item(K).Offset(, 1)), "0.00")
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dt [COLOR="Navy"]In[/COLOR] .Item(K)
        n = n + 1
        Cells(c, "I").Offset(, n) = Dt.Offset(, -1)
        Cells(1, "I").Offset(, n) = "Date" & n
    [COLOR="Navy"]Next[/COLOR] Dt
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] With
Application.ScreenUpdating = True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick