iferror when calculating historical average of row, return an average of similair row.

So, calculating a very simple forecast, being an average of historicals.

Problem is, some products have no history, so the history is all null.

In this case, Id like to pull an average of other products produced at the same location.

something like this. assume its the 1st of march, and jan and feb are actual data, forecasting march.

Please Login or Register  to view this content.

What I want it to do is say, ok, so no data for B, iferror takes care of that.

Then we will take an average of other products from Chicago, return "7.5"

I tried just doing an averageif, but the problem is, it find itself, so it creates a circular reference... I could manually exclude its own row, but thats tedious and will surely break down the road.

Is there a better way? maybe averageifs and include (=plant, <>product)?

edit: tried my own suggestion, looks like I may need to rethink the logic. even if I exclude itself, when there are 2 products with no data, they look to each other to find an average based on the other. No bueno.

Edit 2- Instead of averaging the column of averages, I am attempting to average the source data that should not circle back.

Please Login or Register  to view this content.

Tried that, where D6:K37 is the previous data, A6 is product, B6 is Plant. Their respective criteria range are the labels.

It returns #Value though.