Formula too long

[excel help]

I need to write a formula but excel shows an error message "Formula Too Long." There are two long strings of the formula that I repeat several times. Is there any way to put these strings in other cells and reference them within the formula? Each of the strings looks very similar to this:

Please Login or Register  to view this content.

When I've tried even simpler formulas with this string, I get #N/A.

Thanks for your help.

[NBVC]

Are you looking to match a number in A17:A46316...because COLUMN()-4 returns a number?

Can you elaborate by way of attached sheet what it is you are trying to do?

[ChemistB]

You can certainly put your individual INDEX formulas inside other cells and reference them (unless you're working within an ARRAYED formula). If you're receiving an #NA error, perhaps there is something else askew. Can you post an example workbook?

[excel help]

Attached is a sample of what I'm working with. I'm trying to integrate linear interpolation into a formula that currently gives me the largest value that is less than or equal to a particular cell in a specified array. This formula, used in Q17:Q142 and as many columns to right as is needed given the data set, is:

Please Login or Register  to view this content.

What I would like (but is too long for the cell) is:

Please Login or Register  to view this content.

I hope I ballanced all the parentheses.

[excel help]

You can certainly put your individual INDEX formulas inside other cells and reference them (unless you're working within an ARRAYED formula). If you're receiving an #NA error, perhaps there is something else askew. Can you post an example workbook?

So if I'm working with array formulas there's no way?

[NBVC]

I don't think you need to use the OFFSET() function to get the range bottoms...

you only need to -1 from the Match() functions within the Index() function for finding the Row.

So your working formula would now be:

Please Login or Register  to view this content.

but I don't really understand what you are doing... so you may have to elaborate by way of spelling it out in words and showing example expected results...

... maybe you will have to split the formula in two cells and then combine the result by dividing one by the other (or whatever) to get the final result in the third cell....

[excel help]

That does save some space, but not enough. I'll give it some thought and get back to you tomorrow.

[NBVC]

It not only saves space, it should make the formula non-volatile.. ie. it won't keep recalculating everytime a non-precedent cell is changed...

[shg]

I'm trying to integrate linear interpolation ...

I can provide a UDF to do linear interpolation, but I can't tell what you're trying to interpolate from your example.

[excel help]

In the attached file, column A contains the trial number, column D contains the effective x values, column K contains the effective y values and column P contains the x values for which a y value is desired. Columns B, G, H & L may be ignored. Each column to the right of P will contain a y value that corresponds to the x value in column P of the same row, given the existing (x,y) pairs in columns D and K. However, each column will consider only one trial at a time (beginning with 13) and increasing by 1 (this is the purpose of COLUMN()-4.

The linear interpolation comes in because the values in column P do not always correspond to values in column D. I would like the computer to a linear interpolation between the (D,K) coordinates whose value in column D is the greatest number that is less than or equal to the value in column P and the next largest value in column D.

If this is confusing, all of the arrays and references minus any interpolation can be found in the first formula of my second post yesterday. Just paste the formula in Q17 and fill to R142.

Thanks for your help.