numeric moving averages

[vanclute]

OK I may be asking Excel to do something it's really not designed for, but here goes anyway...

I have a workbook consisting of 5 worksheets:

A) Advertising Expenses
B) Revenue tied to specific ad campaigns
C) Pivot tables summarizing a & b
D) Line item detail (table calculating information about each ad campaign)
E) Daily breakdown (pivot tables) with chart

I have a lovely chart on sheet E that shows me the current month's trend, and I can easily plot a 5 day moving average to see how we're trending over the near term.

However, I'd also like to be able to show what the CURRENT 5-day MA is, next to each of the items on D, so that I can cancel or extend a specific ad campaign based on its current trend.

In other words, if I see a campaign has been running for several weeks but today its revenue has fallen below the 5-day MA, I want to end that ad campaign.

I can already see this visually on the chart, but I have to fiddle with the pivot tables too much on sheet E. I'd like to just see at a glance and maybe use conditional formatting to show me red & green next to each line item on sheet D.

This is probably better suited to a database application, but I'm working in Excel so... it is what it is.

Suggestions on how to accomplish this?

I can get a 5-day MA directly on sheets A & B using an array formula, but I think I need to somehow marry that to a VLOOKUP from sheet D in order to reference the calculation on an entire column of data from sheet A, to the single item on sheet D.

Does this make sense to anyone but me?

Thanks all!

Jonathan

 

[Peter_SSs]

Does this make sense to anyone but me?

I only have a vague idea. However, it strikes me that it would be a lot clearer if you could post some small sample screen shots. Particularly sheets E, A & B. If you can idicate on the relevant one(s) of those, this additional information you want (just entered manually), then it is more likely somebody will be able to offer advice.

You can post small screen shots with:

Excel jeanie
or
Colo's HTML Maker

 

[vanclute]

OK, here are some screen caps... I've hidden as much data as I could that wasn't pertinent to this discussion, so as not to confuse things.

Sheet A : http://screencast.com/t/045Xa2kelTy

Sheet B: http://screencast.com/t/p5tUto7hmBa

Sheet C: http://screencast.com/t/FrhulwM7Zd

Sheet D: http://screencast.com/t/CuFiiHfvDPe

Sheet E: http://screencast.com/t/xtnky5zlDMN

Hopefully that helps... feel free to ask for any more clarification and thank you so much for being willing to help!

Jonathan