Missing data when using TREND function

[Jeremy Bowyer]

Hi -

I'm trying to use Excel's TREND function to predict a y value given
range of x,y and a given x value. I'm doing this hundreds of times in
large spreadsheet and the ranges for both x and y change somewha
frequently.

I've basically already gone through the leg work of tailoring the rang
for each specific case, but it has lead to several human errors on m
part, a lot of wasted time and now it's causing a problem I'm not sur
how to get around. Within the trend function we're using an INDEX an
MATCH function to find the new x in each case. When we copy this formul
over, our old friend the varying TREND range problem rears its ugly hea
once again.

It seems to me that there must be a way to simply have the TREN
function ignore gaps in data so I can simply have one uniform range fo
all of the trend functions. I've looked into the FORECAST function an
as I understand it, it will only ignore gaps in data when the data i
missing for both x and y, and that's not always the case in m
situation. Is this at all possible?

Thanks for any help