+ Reply to Thread
Results 1 to 5 of 5

Formula needed for comparing variance to target over time

  1. #1
    Registered User
    Join Date
    09-25-2014
    Location
    St. Louis, MO
    MS-Off Ver
    2010
    Posts
    5

    Formula needed for comparing variance to target over time

    I'm running a forecast model that requires tracking week over week. Column A is this week's projection, Column B is last week's projection, and Column C is the target. Unfortunately, I can have wild swings in my trends which is causing me headaches. It only matters that I'm getting closer to the target, so -50 is better than +75 if the target is 0, following number line math. Ultimately, if I can figure out a solid formula, what I'm aiming for is an if statement that tosses out a "Y" for improvement or an "N" for no improvement. However, because my numbers swing so wildly from week to week, I'm having trouble pinpointing a formula that works in all instances. Any ideas? Thanks in advance.

    -Q
    Attached Files Attached Files
    Last edited by RavenousQbert; 10-21-2015 at 11:06 AM.

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,274

    Re: Formula needed for comparing variance to target over time

    It is better to help i you could upload a sample worksheet.
    Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

  3. #3
    Registered User
    Join Date
    09-25-2014
    Location
    St. Louis, MO
    MS-Off Ver
    2010
    Posts
    5

    Re: Formula needed for comparing variance to target over time

    Thanks, Quang. I just attached it to my original post.

    -Q

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,274

    Re: Formula needed for comparing variance to target over time

    Can you explain E5, E6,E7 = Y,N,N ?

  5. #5
    Registered User
    Join Date
    09-25-2014
    Location
    St. Louis, MO
    MS-Off Ver
    2010
    Posts
    5

    Re: Formula needed for comparing variance to target over time

    I didn't catch the error in the sheet I attached when I copied over some of the data, so I apologize for that. I've fixed and reattached, which should make more sense now. The Y,N,N etc. is if there is improvement week over week in the projections getting closer to the target. Column B represents this week, Column C represents last week, D is the target, and E is whether we moved closer to the target or not. Hope this make sense. Again, sorry for the confusion with the previous error.

    -Q

+ 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. Formula for number of perfect ordered needed to achieve certain target.
    By kokoli84 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-11-2014, 08:06 PM
  2. [SOLVED] Find the date of a variance amount over a limit and count the days since the variance.
    By avidcat in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 06-21-2013, 12:00 PM
  3. Formula Help Needed Please!!Comparing a cell to a column
    By jgkinder in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-28-2013, 06:15 PM
  4. Replies: 5
    Last Post: 01-22-2013, 11:38 AM
  5. [SOLVED] Need alternate syntax 'Select Case' on Target.Column and Target.Row at the same time.
    By 111StepsAhead in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-18-2012, 11:19 AM
  6. Worksheet_CHange (ByVal Target as Range) when target is formula cell
    By coasterman in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-09-2012, 07:00 PM
  7. Formula to say Yes or No based on variance in time cells
    By KingCreole in forum Excel General
    Replies: 1
    Last Post: 05-25-2005, 06:03 AM

Tags for this Thread

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