Differrent results with similar functions Anyone know why?

[Jebrowsky]

I am getting different result using the the following formulas when I believe they should be the same. =(SUMPRODUCT(GEOMEAN(OFFSET(C5,0,1,,20)+1))-1)*4 and =(((F40)^0.2)-1)*100 where F40 in the last equation is the product of the same 20 data points used in the first formuala with a 1 added to get a positive number.

[Norie]

Can you upload an example workbook with sample data?

Click on GO ADVANCED and use the paperclip icon to open the upload window.

[Jebrowsky]

Data.xlsx

I just attached it. Thanks.

[Norie]

What are you trying to calculate?

Which part of the second fomula is meant to be calculating the geometric mean?

Is part of it meant to be calculating that?

Also, why SUMPRODUCT in the first formula?

[Jebrowsky]

In the second formula F40 represents the product of the data points (the period returns). Geometric mean is simply taking the product of those data points to the power represented by the number of periods reported (5 years). 1/5 = .20

Regarding the sumproduct, I don't know why this is being used. I was given this advice years ago from the community when I first put it together. I was hoping you could help. As I write this E-mail, I am thinking the difference might be that in the first formula each period is being treated as a quarter of the year and then mulitiplied by 4 to get to a full year. In the second formula the geometric mean is being calculated based on five annual periods. When I change the ^ value to .05 (1/20 quarters and multiply by four at the end versus the current 1/5 years), I get the same results as the first formula. So the question is which one is right? Any thoughts?

[Norie]

I thought geometric mean took the product to the power 1/n, where n is the no of data points, which in your case would be 1/20.

If I apply that to your worksheet with this formula, the result is 0.980052965.

Please Login or Register  to view this content.

I also get a, well kind of, similar result with this.

Please Login or Register  to view this content.

The result for that is 1.00980052965.

I think this might be something to do with the +1 you've added.

Actually if I change the first formula to this then the result tallies with the GEOMEAN formula I posted above.

Please Login or Register  to view this content.

[Jebrowsky]

I agree with your first statement in the last message in that the number of periods is 20, but we want to show an annualized return and the data points only represent quaters. So the need to multiply by 4, but this might not be right.

[Norie]

So what is it you are trying to calculate?

Is there a way to eliminate the negative values from the original data?