Hi -

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

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

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

Thanks for any help!