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

#### robalan88

##### New Member
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

Last edited:

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

##### Well-known Member
Assuming the data is on Sheet1 and the summary is on another sheet this will work:

Excel 2010
ABCD
1StreamflowMin SCMax SCAvg SC
22000231231231
32010223254238.66667
42030220240230
Summary
Cell Formulas
RangeFormula
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

##### MrExcel MVP
Try this:-
Results start "E1"
NB:- Fomat cells accordingly !!
Code:
``````[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]
[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

Replies
3
Views
597
Replies
3
Views
1K
Replies
29
Views
10K

1,196,027
Messages
6,012,947
Members
441,740
Latest member
abaz21

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back