# Formula needed for comparing variance to target over time

1. ## 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

2. ## 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. ## Re: Formula needed for comparing variance to target over time

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

-Q

4. ## Re: Formula needed for comparing variance to target over time

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

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

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