+ Reply to Thread
Results 1 to 5 of 5

Formula to compare previous year to current year

  1. #1
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    1,000

    Formula to compare previous year to current year

    Looking for a formula to compare current year values to previous year values. For example, if the current year has values for the month of January through March (100, 100 and 150), current year value will be 350 and the previous year value will be 975 (i.e. 300+275+400). The aim here is to make the previous year months summation equal to the present (or current) values. As new values are entered for the current year, the previous year's values will have to change to reflect the new month's value entered for the current year.

    Month 2012 2013
    Jan 300 100
    Feb 275 100
    Mar 400 150
    April 650
    May 454
    June 800
    July 500
    Aug 375
    Sep 525
    Oct. 300
    Nov 410
    Dec 510
    Sample file is attached for your convenience.

    Thanks for the assistance.
    Attached Files Attached Files

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Formula to compare previous year to current year

    In E2 copy this formula and drag it to the right


    =SUMPRODUCT(($A$3:$A$14={"Jan","Feb","Mar"})*(B$3:B$14))
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    1,000

    Re: Formula to compare previous year to current year

    Alkey: Thanks for the response. If you had a new value for the month of April, 2013, the proposed value should give the total of 450 (January through April 2013) and 1625 for January through April for 2012. However, your suggested formula is not expanding as new values are added to the current year (months).

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Formula to compare previous year to current year

    Try this in E3 and drag to F3 then down

    =IF(B3="","",SUM(IF($A$3:$A$14=$A$3:$A3,B$3:B3)))

  5. #5
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Formula to compare previous year to current year

    Or this in E2 and drag it to F2

    =SUM(INDIRECT(CHAR(65+COLUMN(A1))&"3:"&CHAR(65+COLUMN(A1))&COUNTA($C$1:$C$14)))

    This will change the subtotal in E3 and F3 as a new data added in column C.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Need help with Calendar formula for previous year and next year
    By dcoates in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-25-2013, 11:58 AM
  2. Replies: 3
    Last Post: 09-04-2013, 10:49 AM
  3. [SOLVED] Keeping a date as current year, even though formula determines previous year
    By dropanddrive03 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-28-2013, 08:31 PM
  4. Replies: 0
    Last Post: 04-18-2006, 02:10 AM
  5. How to compare current year to prior year in bar chart?
    By substring in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 05-12-2005, 02:06 PM

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