Calculating the Median, Mean, and GeoMean of dynamic range

**cameron.beyers** · 07-10-2012, 06:09 PM

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.
Column B Column G Column H
2 10 5
2 13 9
2 9 2
3 8 2
3 7 3
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

Please Login or Register  to view this content.

Thanks in advance!!
Cameron

RedRiver.xlsm

**Richard Buttrey** · 07-10-2012, 06:13 PM

Hi,

Please upload the workbook and add some manually calculated results so that we can see the request in context.

Is it necessary to use a macro when there are standard Excel functions available?

**cameron.beyers** · 07-10-2012, 09:38 PM

Sorry I forgot to attach the workbook earlier.

Yes it would be helpful to use a macro because the workbook I'm working with has 1000's of rows of data and the Identifying value in Column B changes depending on how many readings were taken.

Thanks

**Richard Buttrey** · 07-11-2012, 07:04 AM

Hi,

I don't understand all your data. I accept the station number 10069 stats (using column H data). but don't see where you get the 10064 values.

You'll be better off using excel functions, even if you resort to building them with a macro.
The easiest way is to enter formulae in two helper columns to identify the first and last row of the station in question. (This assumes a sorted list) and then an INDIRECT function which uses these row numbers.

So.
Q4:
=MATCH(M4,D:D,FALSE)
R4:
=MATCH(M4,D:D)
N4:
=MEDIAN(INDIRECT("H"&MATCH(M4,D:D,FALSE)&":H"&MATCH(M4,D:D)))
O4:
AVERAGE(INDIRECT("H"&MATCH(M4,D:D,FALSE)&":H"&MATCH(M4,D:D)))
P4:
=GEOMEAN(INDIRECT("H"&Q4&":H"&R4))