Arithmetic and Geometric Return:

[Raphael Excel]

I would like to calculate arithmetic and geometric return without calculating intermediate HPR returns.

ie: data set
200
300
400
500

I cannot compute intermediate HPR values (300/200)-1, (400/300)-1 etc.

I need to calculate geometric mean in only one cell and arithmetic mean in only one cell.

I think there's a way using an array function control-shift enter but not sure how.

 

[kweaver]

Have you tried GEOMEAN function?

 

[Raphael Excel]

Have you tried GEOMEAN function?

I did but as I understand it I still have to calculate HPR for each period and then do the GEOMEAN of the HPR. I don't think if I select my values directly it will give me performance results?

 

[joeu2004]

With your example data in A1:A4, the arithmetic mean return is:

=SUMPRODUCT(A2:A4 / A1:A3) / 3 - 1

And the geometric mean returns is:

=(A4 / A1) ^ (1 / 3) - 1
or
=GEOMEAN(A2:A4 / A1:A3) - 1

The last formula is array-entered: press ctrl+shift+Enter instead of just Enter. Excel displays the curly braces in the Formula Bar. Do not type the curly braces manually.

Of course, you can replace 3 with COUNT(A2:A4).

If you want something that is more generalized, you need to tell us more about the data. For example, do you want to calculate the number of data?

 

[Raphael Excel]

With your example data in A1:A4, the arithmetic mean return is:

=SUMPRODUCT(A2:A4 / A1:A3) / 3 - 1

And the geometric mean returns is:

=(A4 / A1) ^ (1 / 3) - 1
or
=GEOMEAN(A2:A4 / A1:A3) - 1

The last formula is array-entered: press ctrl+shift+Enter instead of just Enter. Excel displays the curly braces in the Formula Bar. Do not type the curly braces manually.

Of course, you can replace 3 with COUNT(A2:A4).

If you want something that is more generalized, you need to tell us more about the data. For example, do you want to calculate the number of data?

Thank you. This is exactly what I needed.

I also posted the same question but for Standard Deviation: Standard Deviation without calculating HPR

Not sure if there is a way as well. Thank you