cameron.beyers
New Member
- Joined
- May 30, 2012
- Messages
- 2
I have a spreadsheet with Data in Columns A-H. Column B is an ID value that will repeat an unknown amount of times. For each Value in Column B I need to calculate the Median, Mean, and GeoMean for the corresponding range of "G_:H_"
Ex.
<tbody>
</tbody>
For Column B = 2
I need to Calculate for the range "G2:H4"
I need the Median, Mean, and Geo Mean values to paste in Columns N-P for each different Station Index. My code only calculates for the first Station ID
Here is the code for what I have so far
Thanks in advance!!
Ex.
Column B | Column G | Column H |
2 | 10 | 5 |
2 | 13 | 9 |
2 | 9 | 2 |
3 | 8 | 2 |
3 | 7 | 3 |
<tbody>
</tbody>
For Column B = 2
I need to Calculate for the range "G2:H4"
I need the Median, Mean, and Geo Mean values to paste in Columns N-P for each different Station Index. My code only calculates for the first Station ID
Here is the code for what I have so far
Code:
Sub Median()
Dim r As Long
Dim stndx As String
Dim i As Long
Dim x As Integer
Application.Calculation = xlCalculationManual
r = Cells(Rows.Count, "A").End(xlUp).Row
x = 2
stndx = Cells(r, 2).Value[ATTACH]47030[/ATTACH]
For i = r To 8 Step -1
If Cells(i, 2).Value <> stndx Then
Cells(x + 1, 11).Value = Cells(i, 1).Value
Cells(x + 1, 12).Value = Cells(i, 2).Value
Cells(x + 1, 13).Value = Cells(i, 4).Value
Cells(x + 1, 14).Value = Application.Median(Range("G2:H" & i))
Cells(x + 1, 15).Value = Application.Average(Range("G2:H" & i))
End If
Next i
Application.Calculation = xlCalculationAutomatic
End Sub
Thanks in advance!!