Missing data when using TREND function

Question

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!

Answer 1

As far as I can see, as long as you have at least 2 rows of fully populated data (i.e. two rows with a number in both columns), FORECAST will work fine.

Cheers

Rich

Answer 2

As far as I can see, as long as you have at least 2 rows of fully populated data (i.e. two rows with a number in both columns), FORECAST will work fine.

 

Cheers

Rich

Rich -

This worked perfectly. Thanks so much!

I should have looked into the FORECAST function more deeply before asking for help. I was operating under the assumption that it would only ignore missing data in one column when it was accompanied by a corresponding lack of data in the other.