• Ask an Expert
• Get a Professional Answer
• 100% Satisfaction Guarantee
The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 1997
Experience:  20 years experience providing remote computer support
62934938
Type Your Microsoft Office Question Here...
The-PC-Guy is online now

# I need to calculate the 3-month rolling geometric mean (GM)

### Customer Question

I need to calculate the 3-month rolling geometric mean (GM) and 3-month rolling arithmetic mean (AM) for any given sample. The 3-month rolling portion being either the GM or AM of samples in the current month and the prior two months (i.e. January 2003 and November and December 2002). The other condition is it has to be the same stream name.
Sample Date (MONTH to be evaluated) - Column D
Reported Value (Total Phosphorus for GM/AM calculations) - Column I
Source_2 (Stream Name) - Column L
I need to calculate a 3-month rolling geometric mean for water quality data with conditions in the Excel equation checking for that month and the prior two months in the date (MM/DD/YYYY) column and the stream name column (text - there are twelve different stream names in one column) must match. I have the file if you need to see what I am talking about.
Submitted: 1 year ago.
Category: Microsoft Office
Expert:  Pete replied 1 year ago.

Hi there,

Please attach the file here using the paperclip icon so I may see...

Customer: replied 1 year ago.
Here is the file.Here is the formula I have using the two columns for the dates as a range describing the 90 days.=GEOMEAN(IF(AND(D2=P2),I2))Then, I am increasing the range (I2) as it goes further down the spreadsheet but it adds the values even if they are not in the 90-day range. I should be able to safely assume no more than 50 data points will be taken for any given stream in 90 days is my thought to do a work-around.=GEOMEAN(IF(AND(D3=P3),I2:I3))
=GEOMEAN(IF(AND(D4=P4),I2:I4))However, it is still taking the GEOMEAN of Results which don't meet the 90-day range from that data point.
Expert:  The-PC-Guy replied 1 year ago.

i have the sheet.

You want to get the average of values from column I that fall with in - 3 months of all of the values for the dates in column D? So if you have 5 dates that fall with march,april,may, you want the average of just those five values?

Am I correctly following?

Customer: replied 1 year ago.
I want the geometric mean of all values for one stream (condition - if statement) if they fall within the preceding 90-days from the date of the sample (row).
Expert:  The-PC-Guy replied 1 year ago.

what is a geometric mean?

Customer: replied 1 year ago.
https://support.office.com/en-za/article/GEOMEAN-function-db1ac48d-25a5-40a0-ab83-0b38980e40d5To find the geometric mean of a set of n numbers, multiply the numbers and then take the nth root of the product.http://www.mathwords.com/g/geometric_mean.htm
Expert:  The-PC-Guy replied 1 year ago.

doesn't really help me,

but lets look at your criteria, and assume excel calculates the correct value if you plug in the correct numbers.

You want to look at the values in column I for a 3 month period prior to the date in a given row for column D?

Expert:  The-PC-Guy replied 1 year ago.

so you no longer want help on this?