Results 1 to 5 of 5

Calculate the Average Difference or Average of Differences

This is a discussion on Calculate the Average Difference or Average of Differences within the Excel Questions forums, part of the Question Forums category; Thanks for your patience! I have a row with amounts from the previous 10 years of budget amounts (i.e., c1 ...

  1. #1
    Board Regular
    Join Date
    Oct 2002
    Posts
    110

    Default Calculate the Average Difference or Average of Differences

    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

  2. #2
    Board Regular
    Join Date
    Sep 2002
    Location
    Simsbury CT USA
    Posts
    1,542

    Default 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

  3. #3
    Board Regular
    Join Date
    Oct 2002
    Posts
    110

    Default 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

  4. #4
    Board Regular
    Join Date
    Sep 2002
    Location
    Simsbury CT USA
    Posts
    1,542

    Default 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

  5. #5
    Board Regular
    Join Date
    Oct 2002
    Posts
    110

    Default 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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com