+ Reply to Thread
Results 1 to 2 of 2

Compare 2 columns on different sheets & return current % difference as year progresses

Hybrid View

  1. #1
    Forum Contributor Big.Moe's Avatar
    Join Date
    03-15-2012
    Location
    Corea, Maine, USA
    MS-Off Ver
    Excel 2010
    Posts
    262

    Compare 2 columns on different sheets & return current % difference as year progresses

    Greetings,

    I need some help with the following problem:

    Workbook Layout
    I created an Excel 2003 workbook that tracks various categories for my small business, including mileage, parts, labor, tax, invoice total, profit, etc. I set it up to have each month of the year be a separate sheet with an annual summary sheet after the 12th month. Each category has it's own column with a "totals" row at the bottom (see pic).

    Annual Summary Sheet.jpg

    Formula Needed
    Everything is set-up and working great, but I would like to add a row under the "Totals" row of each years summary sheet that shows the percentage difference of each category between the current year and the previous year. The catch is that I would like the formula to calculate the percentage difference as each month progresses. Meaning I cannot simply divide the totals from one annual summary sheet with the other because it will give me a negative/inaccurate value until the current year is over. Rather, I would like to compare whatever amount of months that have passed in the current year with the corresponding months of the previous year.

    My first post so I hope I have clearly described my issue.
    Last edited by Big.Moe; 05-30-2013 at 05:15 PM.

  2. #2
    Forum Contributor Big.Moe's Avatar
    Join Date
    03-15-2012
    Location
    Corea, Maine, USA
    MS-Off Ver
    Excel 2010
    Posts
    262

    Lightbulb Re: Compare 2 columns on different sheets & return current % difference as year progresses

    Well, it took some doing & I probably didn't accomplish it in the most conventional manner, but it's done & it seems to work...OOF!!

    Ended-up creating this formula using COUNTIF to figure out what month it is at any given time, and LOOKUP to go find the corresponding percentage values:
    =IF(12-COUNTIF($D$9:$D$20,"-")=0,0,LOOKUP(12-COUNTIF($D$9:$D$20,"-"),V9:V20,W9:W20))
    Then I used various forms of this formula in the array to calculate the running percentages as each month progressed between 2 sequential years:
    =IF('2011'!F18=0,SUM(F$11:F18)/100,(SUM(F$11:F18)/SUM('2011'!F$11:'2011'!F18))-1)
    Since I'm a bit of a self-taught hacker, I would love any feedback on ways I could of done it different/simpler. Although, I'm not sure if I'm going back to make major changes. ;o)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1