Calculate the Average Difference or Average of Differences

[beancounter]

Thanks for your patience!

I have a row with amounts from the previous 10 years of budget amounts (i.e., c1 = 100 d1 = 200 ...) I also have the same years actual amounts below them, (i.e., c2 = 90 d2 = 150 ...) and the next row calculates the difference, (i.e., c3 = c1-c2) and a percentage change calculated for each two years i.e., d4 = (c1-d1)/c1, d5 = (d1-e1)/d1 ...

that part is simple but, I want to get an average of the percentage changes over the ten years. I figured out two ways of doing it and not surprisingly come up with two different answers. Which is correct:

=AVERAGE(d4:m14)

OR

=((c3-m14)/c3)/10

Any help is appreciated

 

[plettieri]

Re: Calculate the Average Difference or Average of Differenc

Hi:

Seems as though you are averaging two different results...What other data is in columns e thru m

It might be a bit more helpful to see some more of your sample data....to get a better understanding

You can use the colo utility found under the text message box

plettieri

 

[beancounter]

Re: Calculate the Average Difference or Average of Differenc

Plettieri,

I figured it out using a third method to smooth the averages. Thanks for taking the time to respond. I've learned more from this message board than all the training classes I've taken combined!

Thanks again

 

[plettieri]

Re: Calculate the Average Difference or Average of Differenc

Hi:
Care to share your answer...Being a "beancounter" myself, I am curious to know....

plettieri

 

[beancounter]

Re: Calculate the Average Difference or Average of Differenc

Plettieri,

It is quite simple. Say cell B5 has fiscal year (fy) 93/94 info & cell L5 has fy 03/04 info then the formula is:

=(L5/B5)^(1/10)-1

You will get an average over the 10 years