Using OFFSET to create the array inputs in FORECAST

[MJBVLC]

Hi,

My basic question is: Can the array inputs of the FORECAST function be read from an intermediate cell containing an OFFSET function?

I am using the FORECAST function to interpolate between 2 pairs of numbers. I have the following formula which is working fine:

=FORECAST(K10,AC10:AD10,Y10:Z10)

Where the values in AC10:AD10 and Y10:Z10 are found from a data table using MATCH and INDEX.

In order to reduce the amount of intermediate data I am collecting, I would like to replace these MATCH/INDEX values with an array calculated using the OFFSET function

So I created cells containing formulae in the form:
{=OFFSET(column,Q10-1,0,2,1)} (Q10 is the row where the array starts)
which give the correct array, but referencing these cells in the FORECAST function returns a #DIV/0! error. {=FORECAST(K10,U10,T10)}

Although U10 & T10 contain arrays, it seems that only the first value of each array is carried into the FORECAST function.

Putting the entire OFFSET formula into the FORECAST function works fine, but I would prefer to keep these as separate cell values in order to reduce the length of the formula (in these examples I have simplified the column reference which is itself an INDIRECT function in order to select the correct coulumn). Also, these array "pairs" will be used by lots of other formulae.

I can't use VBA code here because (a) the users of this spreadsheet will need to edit it after I'm gone and (b) I don't know how to (!)

If there is an entirely different way to approach this interpolation I am happy to use it. I have also tried using TREND but have had the same problems. Excel 2007.

Many thanks

 

[Andrew Poulsom]

Welcome to the Board.

Try inserting a Name, putting your OFFSET formula in the Refers to box and using that name in your FORECAST formula.

 

[MJBVLC]

Welcome to the Board.

Try inserting a Name, putting your OFFSET formula in the Refers to box and using that name in your FORECAST formula.

Andrew, that is amazing. Thank you! I have been trying to get this to work for days & you have come up with the answer in a flash.

I first read your answer to mean "name the cell you are referencing and then use that name in the formula". Then I realised that you were using a formula instead of a range in the Name Manager reference - something I've never seen before. Great that it uses relative references and I can just copy the formula to the other rows of the table.

Thanks again,
MJBVLC