A spreadsheet (e.g Libreoffice Calc) has a power function for the example scenario: annual change (%) is 3,4,5,6 for years 1 to 4 respectively. The power function is:

power= ((a4/a1),1/5)-1 where a1,a2,a3,a4 correspond to the values 3,4,5,6.

Can someone explain the theory behind this function please?

Thanks.

surely this program has help files?

surely this program has help files?

 

Yes but such software explains how to use the function and it is the theory that is not known. Why is it not possible to simply subtract the initial rate from the final rate and divide by the total time to obtain a simple average annual change, i.e. (6-3)/4=0.75? The power function result is approximately 0.15.

Edited March 9, 201114 yr by sciencer

Why is it not possible to simply subtract the initial rate from the final rate and divide by the total time to obtain a simple average annual change.

Well for one, that is a terible estimator for an average. Take the list [0,1,2,6,24] (for a horribly contrived example), the mean is 6.6 but the midpoint is 12 - that's quite a dramatic overestimate.

Well for one, that is a terible estimator for an average. Take the list [0,1,2,6,24] (for a horribly contrived example), the mean is 6.6 but the midpoint is 12 - that's quite a dramatic overestimate.

Understood, but that detracts from the original question: what is the theory behind this spreadsheet function? Seems like a sum of a geometric series but I want a 'mean', perhaps analogous to a geometric mean?

A geometric mean would be

 

[math](a_1 \cdot a_2 \cdot a_3 \cdot a_4 ) ^ {\frac{1}{4}}[/math]

 

But in terms of LibreOffice's notation that'd be along the lines of

 

=POWER( PRODUCT(A1:A4) ; (1/4) )

 

Or, more neatly

 

=GEOMEAN(A1:A4)

 

I honestly can't see where on Earth you managed to get "power= ((a4/a1),1/5)-1" from.

Well for one, that is a terible estimator for an average. Take the list [0,1,2,6,24] (for a horribly contrived example), the mean is 6.6 but the midpoint is 12 - that's quite a dramatic overestimate.

OK, so for your example your term 'midpoint' would be the median change to go from 0 to 24 over the period? The median function in the spreadsheet does give the expected answer.

Shouldnt the annual change of (percent) being 3.4.5.and 6 percent for years 1 thru 4 respectively be expressed differently.? Since the (percent) for years 1 thru 4 change from 3.to 4. To 5. to finally 6 (percent) through the time period (span) of four years. Looks like if this being true. It would affect your formula.... It would seem you would have to find something else to use besides the ((a4/a1)) in your formula... This may even clear up your seemingly slimpler view of the solution to the problem... Which I thought was pretty good reasoning... Cheers.