Complex Forecasting Formula Needed?

[maverickballa24]

Hi all,

Thanks for reading and helping with me this problem. I am in need to write a formula that can distinguish two different logical situations and output a formula based on which one it is.
The first being any center that has greater than 24 months of data. The other being any center with less than 24 months of data.
Please see the attachment for the actual file and description.

If I left anything out please ask me to clarify.
Thanks

Scottie

[RobertMika]

Can you provide how your desire result shoudl look like?

[maverickballa24]

I have attached a new version of how the page should look like. Check the formuals for specefics regarding what I am looking for it to do.

Scottie

[egglampbasket]

This entered as an array formula into BG2 on the inbound data page will work for over 24 months of data, but not under. You would need to use a helper column to perform that since you can't iterate through the rows to only select columns with enough data just through a one cell formula. Would you be fine using a helper column?

=IF(COUNT($C2:BF2)-MATCH(TRUE,$C2:BF2>0,0)>=24,SUM(IF(MONTH(OFFSET($C$1,0,MATCH(TRUE,$C2:BF2>0,0),1,COUNT($C2:BF2)-MATCH(TRUE,$C2:BF2>0,0)))=MONTH(BG$1),OFFSET($C2,0,MATCH(TRUE,$C2:BF2>0,0),1,COUNT($C2:BF2)-MATCH(TRUE,$C2:BF2>0,0))))/INT((COUNT($C2:BF2)-MATCH(TRUE,$C2:BF2>0,0))/12),"Under 24 Months of Data")

[maverickballa24]

I am working on the formula you provided now. I am completely fine using a helper column. What would be the best way to determine greater than and less than 24 months? What is the best method to use the helper column in this case?

Thanks for your help!

Scottie

[egglampbasket]

Forecasting Methods in Excel With Formula.xlsx

I put two helper columns in. The first mainly for the conditional formatting and the second to help sum the centers with only over 24 months of data. The only limitation on it now is that the first column only goes out to BH right now. You'll have to change this to however far out your data goes. There is a way to make this automatic but I'm on my mac right now and have had enough of mac excel. The actual sum formula will drag to the right and downwards fine. Let me know if you have any questions.

[maverickballa24]

This is 100% what I am looking for. I am working through how you did it right now and will let you know if I get hung up on certain part of it. It looks fantastic. Thanks so much!!

Scottie