MeraWindows Forums - Get your Computer Problems Fixed

Hi all,

I have received the most helpful advice in this Excel forum... Thank you all.

I have run into another problem.. I would like to calculate the geometric means for a group of numbers (which are all-positive but includes some zero values). I was thinking about a formula that is like {=GEOMEAN(IF(B9:B22>0,B9:B22))}, which seems to work on my sample data. However, I need Excel to find the geometric mean for every 14 rows, which correlates to a 2 week period.

Presently, my data is set up as:
Column A  ................................B   

DATE
29/07/2010	0
28/07/2010	0
27/07/2010	1.00281
26/07/2010	1.007653
25/07/2010	0
24/07/2010	0
23/07/2010	1.019655
22/07/2010	0.990496
21/07/2010	1.002984
20/07/2010	1.012535
19/07/2010	0.981744
18/07/2010	0
17/07/2010	0
16/07/2010	0.996296
15/07/2010	0.995958
14/07/2010	1.020353
13/07/2010	0.991995
12/07/2010	1.002649
11/07/2010	0
10/07/2010	0
9/07/2010	1.009585
8/07/2010	1.023358
7/07/2010	0.996383
6/07/2010	1.01354
5/07/2010	0.99442
4/07/2010	0
3/07/2010	0
2/07/2010	0.999654
1/07/2010	0.984236
30/06/2010	0.98794
29/06/2010	0.990021
28/06/2010	0.9937

etc etc..

I would like Excel to use the dates in column A to work out two weeks (ie. a fortnight = 14 rows) and do the GEOMEAN function above and output the answer in the last date of the two weeks (eg. in my example formula above, I would like Excel to output the answer into cell C9.)

I am unsure how I could modify the formula to fit this new requirement and drag down or whether I would need to use a macro to automise this process because I have 3800+ rows of dates and multiple samples to perform this on.


I cannot say how much I would like to thank everyone for their help.

Hi Ashish,

Thank you for your assistance again. It gives me an incorrect answer but I have found a workaround; so wanted to share with you too!

I have uploaded a part of my data here (http://www.megaupload.com/?d=KVX3Y1OS); where in column E, I have had to insert an extra column to get rid of those annoying zeros; so I am left with nulls instead. Column F is using your formula (incorrect values), column G uses an OFFSET function instead which gives me the same values as if I manually selected blocks of 14 rows. Column I shows the difference between your formula and the values from manual selection.

I am wondering how I could modify the formula to calculate geometric means for each month; since if I change my offset formula to 30 instead of the 14 as of now; this would be incorrect as some months have 30 days whilst others have 31. Could I use the help of an another extra column, such Excel providing me the month from the date (ie. =month()); would this help?? Also, some of the data sets do not start exactly on the 1st of each month; so I might need to do GEOMEANS on less than 30 rows.  

Like before, I would like Excel to calculate the geometric mean for each month instead and then provide this number to me on the first date of the period it is calculating (eg. if it is calculating the GEOMEAN for the period 1/06/2006-30/06/2006, I would like it to give this number in the row with 1/06/2006.)

Thank you again. 

P.S. I have closed/resolved some of the other threads. Thank you for reminding me....sometimes you want to try these new formulas and macros straight away on your own data... and then you get carried away with the analysis that you forget. Sorry.

Hi,

For calculating geometric mean you may use macro given hereunder.

Please run the macro after selecting range containg dates, I presumethat vlue is in next column to date.

You will be asked to enter a date, please enter a date of month for which you want to calculate, if it is July just enter 7/7/2010.

Macro will calculate the geometricmean for the month through messagebox.

If no matching month is found macro will give error message.

Please check and revert.

Sub CheckGmean()
Dim Mymonth, Mydate
Dim xcell As Range
Dim counter As Long
Dim Mysum As Double
Dim Dtstring As String
Dim Gmean As Double

Dtstring = Application.InputBox(prompt:="Enter a dat in format 1/1/2010")
Mydate = CDate(Dtstring)

Mymonth = month(Mydate)
counter = 0
Mysum = 0


For Each xcell In Selection
If month(xcell) = Mymonth Then
xcell.Offset(0, 1).Select
If ActiveCell.Value > 0 Then
counter = counter + 1
Mysum = Mysum + ActiveCell.Value
End If
End If
Next xcell




Gmean = (Mysum) ^ 1 / counter

MsgBox prompt:="Geometric Mean For the month is " & Gmean

End Sub

Thanks & regards,
Rajesh