Calculate moving average with inconsistent data.

JoeBananas

New Member
Joined
Mar 24, 2014
Messages
3
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.

Client3 Month Avg PurchaseJan-14Feb-14Mar-14Apr-14May-14Jun-14Jul-14
Client 110,0005,0006,0007,0008,0009,00010,00011,000
Client 210,66715,00010,00012,00020,000
Client 33,0005,0009,000
Client 47,5007,0008,000
Client 513,00013,000

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

rihabr

New Member
Joined
Jul 16, 2014
Messages
10
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,128,092
Messages
5,628,623
Members
416,329
Latest member
phxdan79

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?

Disable AdBlock

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
Top