Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Monthly Percent Increase
Say I have the data listed below:
January - 250 February -275 March - 350 April - 400 May - 500 June - 650 July - 1000 August -1250 September -1600 October - 2000 November - 2600 December - 2750 Here is the formula I used: =(February/January)-1 Once all the months are done then average all the percents together to get a monthly percent increase average... I want to get monthly percent increase then get an average monthly percent increase of all months. Please advise the easiest way to do this and what the formula would be. Thanks, nolanstern |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Monthly Percent Increase
Please note that there are two kinds of averages that you might have
in mind, both equally valid depending on your purpose. If you are interested in the "typical" monthly rate of change, you would simply use AVERAGE(A2:A12), where A2 is the Feb/Jan-1 rate, A3 is Mar/Feb-1 rate, etc. Of course, that does not account for other factors, such as seasonal influences. If you are interested in the average compounded rate -- the average rate that gets you from the Jan amount to the Dec amount when compounded -- there are many ways to do that. Perhaps the simplest way is: (Dec/Jan)^(1/11) - 1. Another way is the following array formulas (commit with ctrl-shift- Enter instead of just Enter): GEOMEAN(A2:A12+1) - 1. But beware that GEOMEAN and PRODUCT have poor numerical properties if there are a large number of months. Arguably, it is better to do those computations using logarithms (gulp!). For example, with this regular formula (commit with Enter as usual): 10^(SUMPRODUCT(LOG(A2:A12+1)) / 11) - 1 Note that the "11" is the number of items in the range A2:A12, whereas the "10" is constant. ----- original posting ----- On Nov 17, 12:03*pm, pivot table wrote: Say I have the data listed below: January - 250 February -275 March - 350 April - 400 May - 500 June - 650 July - 1000 August -1250 September -1600 October - 2000 November - 2600 December - 2750 Here is the formula I used: =(February/January)-1 Once all the months are done then average all the percents together to get a monthly percent increase average... I want to get monthly percent increase then get an average monthly percent increase of all months. Please advise the easiest way to do this and what the formula would be. Thanks, nolanstern |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CHART HELP Percent of Increase | Excel Discussion (Misc queries) | |||
Percent of increase each year | Charts and Charting in Excel | |||
Percent increase | Excel Discussion (Misc queries) | |||
Percent increase | Excel Worksheet Functions | |||
Percent Increase | Excel Discussion (Misc queries) |