Calculate moving average with inconsistent data.

[JoeBananas]

I’m trying to come up with a formula to get a moving average for a growing client list.

With the way my file is set up now, I have a list of clients in column A, in Column B is the last 3 months average, and then in Column C starts my monthly data that grows by a column every month.

What I have setup right now is an offset function, so I can get an average of the last 3 months’ worth of sales to the client. So using =Average(Offset(c2,0,6,1,-3)), I get the 3 month average for May, June, July. (The 6 in the cols section of my offset formula is actually a formula that updates every month so the offset moves the proper amount, but don’t need help with that so I left it out for ease of explaining).

So I have the 3 month moving average down, but my problem is we get a lot of new clients who haven’t been with us for 3 months yet. So if they’ve only been with us for 2 months, I want to take an average of the last 2 months instead, and if they’ve only been with us for 1 month, then show what they did for that 1 month.

I figured I could use some kind function to count cells with no value or greater than 0, but then my problem is we have inconsistent data, also there are formulas that pull in the data in columns C and on already, so I can’t use formulas like isblank or istext to calculate some kind of offset. For instance Client 3 who we started doing business with in April, did no business with us in May or June, then did business again in July. For client’s like this, I would want the moving 3 month average to include the 0 months of May and June. But for clients like Client 4 and Client 5, I want the average to be adjusted for only how long they’ve been doing business with us, in their case es2 months and 1 month.

Does anybody have any ideas of a formula I can use to get the 3 month moving average to 1) adjust for Clients who haven’t been with us for 3 months, 2) while not adjusting for clients who don’t do consist business with us?

See table below for an idea of what the results in Column B should look like if I can get the right formula.

Client	3 Month Avg Purchase	Jan-14	Feb-14	Mar-14	Apr-14	May-14	Jun-14	Jul-14
Client 1	10,000	5,000	6,000	7,000	8,000	9,000	10,000	11,000
Client 2	10,667			15,000	10,000	12,000		20,000
Client 3	3,000				5,000			9,000
Client 4	7,500						7,000	8,000
Client 5	13,000							13,000

<tbody>
</tbody>

 

[rihabr]

I know this isn't a direct solution and may not be what you want.. but perhaps you could change the way the data is presented. For months in which the clients weren't yet with you, leave blank. For the months in which they were with you but did not purchase anything, place a zero. Then perhaps you can do some sort of test on each of the cells to see wether they're >=0. If so, include it in the moving avg. If not, just calculate moving avg with months after...