[sciencer]

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.

[Bignose]

surely this program has help files?

[sciencer]

Bignose, on 9 March 2011 - 03:50 AM, said:

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.

This post has been edited by sciencer: 9 March 2011 - 10:14 AM

[the tree]

sciencer, on 9 March 2011 - 10:11 AM, said:

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.

[sciencer]

the tree, on 9 March 2011 - 03:02 PM, said:

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?

[the tree]

A geometric mean would be



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.

[sciencer]

the tree, on 9 March 2011 - 03:02 PM, said:

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.

[AndrewSr]

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.