Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.

Get a Professional Answer

Via email, text message, or notification as you wait on our site. Ask follow up questions if you need to.

100% Satisfaction Guarantee

Rate the answer you receive.

Ask The-PC-Guy Your Own Question

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.

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.

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?

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).

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