excelfilter

May 21, 2006 10:10 AM

I need help with an Excel formula. Warning: this may venture into the realms of calculus. Maybe.

We have an Excel sheet at my retail work that we use to keep track of our hourly sales, transactions, traffic, etc. We have a daily budget goal from head officeand have been tracking our past hourly sales to give ourselves hourly sales goals. We've copy the hourly sales into a seperate worksheet and then use that to figure that, for example, for the past six months Mondays from 10-11am has been 11% of our daily budget, so if this monday's budget is $5000, we should do $550 from 10-11. What we're using now just finds the average percentage of sales. We have excel add up everything in the monday 10-11 row, divide that by the total of all of mondays' sales, and then go into our "monday" worksheet and have it use that percentage on our daily budget to caluculate the hourly goal.

What we'd like to do have the more recent sales be weighted more that older sales. I could do this algebraicly (i think) if the worksheet stayed static, but every week we add the latest week's number to a new righthand column. Is there a way to find an "average" where the most recent week is weighted more than the one before it, repeated to the end of the data set? Considering that I'm adding my new data to the right of the existing data, will this throw things off? Will I ahve to turn everything around? If so is there a way to insert columns in excel where I only have say 20 columns and if I add a column of data to the leftmost it pushes all of the other columns over one to the right and deletes the rightmost column?

posted by thecjm to computers & internet (7 comments total)

If you have a constant number of weeks you want to look at it should be very easy. Just chose a constant weight, and then multiply each column by that columns weight, and then divide the whole thing by the sum of the weights, rather then the number of columns.
posted by delmoi at 10:41 AM on May 21, 2006

example.

The cells are filled with random values that change all the time, but you get the idea. Each column has a weight associated with it in row 4. and the sum is calculated in row 24 using the SUM function. Then a weighted sum is calculated in row 25.

The 'normal' average is in row 26, and the 'weighed' average is in row 27.
posted by delmoi at 10:52 AM on May 21, 2006

Actually, I think you are trying to do a moving average. It's got nothing to do with calculus. No need to bring in a cannon to kill a mouse (LOL).

I believe that excel has some kind of moving average tool, though I've never used it. You may also want to weight things in that as delmoi suggested, but I think you want to do some searching on the term "moving average" first.

If you google on excel and "moving average" you'll get plenty of "hits" too.

Good luck!
posted by bim at 11:00 AM on May 21, 2006

Ironically, there was a question about moving averages and excel just three posts down from this one :P
posted by delmoi at 11:39 AM on May 21, 2006

Actually, a moving average is going to weight several observations evenly (say, the last 5). A better match would be exponential smoothing. You pick an alpha to denote what weight you want to give to the most recent observation. So with an alpha of .4, for instance, the last observation makes up 40% of the average and the other 60% is made up of all of the previous observations, also weighted for recency.

It's a recursive process and decays smoothly, so it gives you a fairly stable average that still adjusts reasonably quickly. The lower the alpha, the more stable the average, the higher the alpha, the more responsive. For this exercise, a single smoothing factor should work. Here are some instructions for implementing it in Excel.
posted by toothless joe at 12:51 PM on May 21, 2006

Try posting this exact question at the mrexcel message board. There you will find excel geeks who will show you exactly how to do this (ie. provide you with equations that you can cut and paste into your own spreadsheet), and often they will even offer you a sample spreadsheet. Also, they are great about answering follow up questions as you implement the solution.
posted by limitedpie at 4:29 PM on May 21, 2006

toothless joe has it.

The most recent "smoothed average" will be:

((smoothing factor) * (latest observation)) + ((1 - smoothing factor) * (last week's smoothed average))

Higher smoothing factors mean the latest observation has more impact on the numbers, but it also means more volatile numbers. Play with it (obviously the number has to be >0 and <1) to find a factor you're comfortable with.
posted by Opposite George at 9:17 PM on May 21, 2006