Missing data when using TREND function
 

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!

Just in case anybody has this problem. Apparently the FORECAST function works perfectly, as pointed out by somebody on the Microsoft forums.

http://answers.microsoft.com/en-us/o...=1357756758491