sciencer 12 Posted March 7, 2011 Share Posted March 7, 2011 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. Link to post Share on other sites

Bignose 946 Posted March 9, 2011 Share Posted March 9, 2011 surely this program has help files? Link to post Share on other sites

sciencer 12 Posted March 9, 2011 Author Share Posted March 9, 2011 (edited) 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, 2011 by sciencer Link to post Share on other sites

the tree 222 Posted March 9, 2011 Share Posted March 9, 2011 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. Link to post Share on other sites

sciencer 12 Posted March 9, 2011 Author Share Posted March 9, 2011 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? Link to post Share on other sites

the tree 222 Posted March 11, 2011 Share Posted March 11, 2011 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. Link to post Share on other sites

sciencer 12 Posted March 17, 2011 Author Share Posted March 17, 2011 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. Link to post Share on other sites

AndrewSr 0 Posted August 10, 2011 Share Posted August 10, 2011 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. Link to post Share on other sites

## Recommended Posts

## Create an account or sign in to comment

You need to be a member in order to leave a comment

## Create an account

Sign up for a new account in our community. It's easy!

Register a new account## Sign in

Already have an account? Sign in here.

Sign In Now