How To: Vlookup with a moving average formula

[bxiong]

Hi Everyone,

New to the forums, so apologies if this is a repeat or not structured well but I was unable to locate something similar. This one has stumped me so I could use others expertise.

Brief: I'm attempting to build a sales forecast utilizing 3 sheets. I've also created a Key column that would simplify the formulas in sheet 2 and 3 for if formulas. eg. "Content 1 Type 1"

Assuming this was my data:

Sheet 1
Group, Transaction, Projection
A, Type 1, $25 (formula)

Sheet 2
Group, Content(s), Transaction, Average Sales, Key
A, content 01, Type 1, $25 (moving average formula), content 01 Type 1 (Key)
A, content 02, Type 1, $30, content 02 Type 1
A, content 01, Type 2, $20, content 01 Type 2
A, content 02, Type 2, $25, content 02 Type 2
B, content 02, Type 1, $10, content 02 Type 1
B, content 07, Type 1, $10, content 07 Type 1

Sheet 3
Date, Content, Transaction, Actual Sales, Key
1/1/15, content 01, Type 1, $20, content 01 Type 1
1/1/15, content 02, Type 2, $30, content 02 Type 2
2/1/15, content 01, Type 2, $20, content 01 Type 2
2/1/15, content 01, Type 1, $30, content 01 Type 1
etc

I've written an array formula on the 2nd sheet that compares the Key from Sheet 3 and the Key from Sheet 2 and then provides a total average. However, rather than rely on outdated information, how can I revise the formula to take the latest 3 or 5 entries instead of its entire history (bottom is latest entry)? Here's the formula I'm currently utilizing for the total average:

=AVERAGE(IF('SHEET 3'!E:E='SHEET 2'!E2,'SHEET 3'!D:D))

How can I convert this to a latest 5 entries? I have a formula that can perform the moving average function but I am not sure how to add a if/vlookup formula to it. For reference:

=AVERAGE(SUBTOTAL(9,OFFSET(D1:D10000,LARGE(IF(D1:D10000>0,ROW(D1:D10000)-MIN(ROW(D1:D10000))),ROW(INDIRECT("1:5"))),0,1)))

I've yet to work on the Sheet 1 formula, that would come next.

Hopefully this makes some sense. If I am looking at this completely wrong, please let me know. First timer attempting this.

[Richard Buttrey]

Hello and welcome to the forum.

Would you upload the workbook so that we can see the request in context. Manually add the results you expect to see and in some notes explain how you have calculated the results.

[bxiong]

Attached is a simplified version. On the second tab is where I intend to utilize the formula. Obviously if my history was more detailed, the forecast would be more accurate. If there is an easier way to accomplish the same results, let me know! Thanks!

[lode devos]

See attachment. Note that the file needs to be sorted by date.

[bxiong]

I see the workaround you performed - didn't quite think of it that way! Hopefully this method will function well with a few thousand lines of history. Thank you!

[FDibbins]

See attachment. Note that the file needs to be sorted by date.

Sorry for off-topic interjection:

Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

Thanks again for all your hard work here!