# Calculate moving average with inconsistent data.

#### JoeBananas

##### New Member
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>

### Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

#### rihabr

##### New Member
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...

Replies
3
Views
180
Replies
2
Views
82
Replies
8
Views
147
Replies
5
Views
66
Replies
14
Views
263

1,127,217
Messages
5,623,453
Members
415,970
Latest member
ZorroOP

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back