Successive Occurrences in one range versus another

[SaraT83]

Hi,

I have what seems a relatively simple problem (don't they all!), but I can't come to a solution that doesn't involve a lengthy IF based formula.

I have two sets of Data, the first is forecast & the second is actual sales. They are arranged in columns, and I need a results column that tells me how many successive times the last 6 week's sales have been over or under the forecast. In the example I've attached the result would be 3 (the last 3 week's sales are higher than forecast).

If the result could be -3 if the example had three week's sales under forecast that would be even better.

As a final caveat, the data is dynamic, so next week, we'd have week 7 sales added to compare with the forecast. I have some Sum/Offset formulas that I'm using to generate totals for the last few week's sales, but comparing the numbers in this way is beyond me I'm afraid.

I hope the query is clear. Any help would be grandly appreciated (it doesn't seem so simple now I've written the problem down!).

thank you

Sara

[NeedForExcel]

Here!

Try this -

Please Login or Register  to view this content.

One Query. if your data will keep on adding, how will you update the new data? Column wise like it currently is, or row wise?

You might want to arrange it this way. See the file attached.

[SaraT83]

Hi,

thank you for trying, and sorry for the slow reply, been snowed under!

It's not quite what I'm looking for I'm afraid. I am after successive, or consecutive weeks over/under forecast, with the most recent week always as the starting point. In the example you attached back, the answer would be 1 as the last week was over forecast, while the week before was under. if week 8 comes in over forecast, then the result would be 2, while if it comes in under then it'll stay at 1. I did want to only have this rolling for 6 weeks, but that isn't strictly necessary, so long as it always picks up the most recent weeks sales as the starting point (thought it might speed up the formula if it's looking within a limited range).

I've tried to illustrate a little better what I mean in the attached, which represents a little more closely how my actual sheet is set out. In my real sheet I have around 8,000 skus with forecast & sales, and I'd ultimately like to pick out ones which have been over/under forecast the longest.

The attached shows how the sheet would look over a three week period, with the previous week's sales added each time, giving a different result. The result formula would sit somewhere to the right of all the forecast/sales data for each sku.

. I apologise if my explaining isn't brilliant, it's my first time posting on an excel forum, but I know it's difficult if the question isn't clear. So thank you again.

Sara