Two parameters functions: slope, intercept, etc. do not work with indirect(address()) as one parameter

[erotavlas]

Hi,

I have a strange problem with libreoffice calc latest version 5.4.1.2 under ubuntu 16.04 64 bit (build 5.4.1~rc2-0ubuntu0.16.04.1~lo0).

I would like to use some functions that take two range parameters as input (slope, intercept, rsq, etc.).
These functions work well if the parameters range are defined as usual:
SLOPE(absolute_range1,absolute_range2), INTERCEPT(absolute_range1,absolute_range2) where absolute_rangeX is like this $A$10:$A$100.

However, I would like to able to change the range on which the functions work. For this purpose I use ADDRESS and MATCH function:
H1=ADDRESS(MATCH(value_to_search, range_where_to_search,1)+ROW(cell)-1,COLUMN(cell),1) and I correctly get the cell address, let say $C$10.

After, by using H2=INDIRECT(H1 &":$C$100") I can obtain the required range and I able to use it in functions that take one parameter as SUM(H2), SUMPRODUCT(H2).

Unfortunately, it does not work with SLOPE(H2,$A$10:$A$100), INTERCEPT(H2,$A$10:$A$100) and SUMPRODUCT(H2,$A$10:$A$100) too.

So is there any intrinsic limit about using INDIRECT(ADDRESS()) together with functions that take two range as parameters?

Thank you

P.S.
For curiosity, I tried also with the same version of libreoffice on windows and with excel 2016 and both do not work.

[mikekaganski]

It’s unclear how can you get the result of =INDIRECT(H1 &":$C$100") in H2; the result is a range. Do you use array formula? But even in that case, you won’t get “correct” sum for =SUM(H2) if you expect that to give the sum of all the array.

When you use H2 in SLOPE's 1st arg, the function thinks that the H2 is the address of its range, not the value kept there. You can do =SLOPE(INDIRECT(H1 &":$C$100");$A$10:$A$100), and it will work, though.

[Lupp]

@mikekaganski: You posted “…the function thinks that the H2 is the address of its range…”
That’s what I assume the OQ to expect. In fact the function thinks(?) H2 is the reference to the data, not to the range address…
The Example =SUMPRODUCT(H2) claimed to work by the OQ also will not do this in the way he expects. It will, however, not raise an error but return the value 0 (zero) as it does for any text passed to it via a parameter.

[mikekaganski]

My meaning was this: “…the function thinks that the “H2” is the address of its range…”

[Lupp]

Sorry. We still are not completely together concerning the question if (or under what circumstances) “address” and “reference” can be used as de-facto synonyms. The way I think it should be handled, you should have used the term “reference to” instead of “address of” in your comment.

[mikekaganski]

terminology… my English is far from perfect, and also my thinking

[Lupp]

I surely did not understand everything. In specific I cannot expect to get working formulas if entering one of the examples from your paragraph

“Unfortunately, it does not work with SLOPE(H2; $A$10:$A$100), INTERCEPT(H2; $A$10:$A$100) and SUMPRODUCT(H2; $A$10:$A$100) too.”
(Semicolon here as parameter delimiter to make quoted formulas usable globally.)

You need to write instead:

SLOPE(INDIRECT(H2); $A$10:$A$100)
INTERCEPT(INDIRECT(H2); $A$10:$A$100)
SUMPRODUCT(INDIRECT(H2); $A$10:$A$100)

Judging from the subject you chose you seemed to know this.
Explicit now:
And address entered as a constant text somewhere and a reference entered as a part of a formula look the same, but are essentially different. The formula parser/tokenizer will automatically convert a typed character string like A10:A100 e.g. into the respective range reference. This reference will, however still be displayed like an address.
The H2 of your examples will thus be interpreted as a reference to the cell H2 and will pass the content of H2 to the called function. Since the functions do not expect a single text constant (as is passed by H2) but an array of values in the respective position, this will cause errors. In your case the errors fortunately are visible on the surface. (In other cases they main remain undiscovered.)
Considering your supposed expectations you need to explicitly convert the range address calculated in H2 into a range reference. This is done by the function INDIRECT(). The function might better be named CONVERT_ADDRESS_TO REFERENCE(), but a few users would probably object.

[Lupp]

Sorry. I cannot explain the issue without insisting on a precise terminology insofar. INDIRECT is actually specified to convert a Text type parameter, interpreting it as an address, into a reference.
The ‘Constraints:’ part of the specification, however, is itself afflictect by the misleading messing up of the terms. It should read:
Constraints: The Text parameter named Ref is a valid address in the given context.

(The chosen name ‘Ref’ is misleading in itself.)

[erotavlas]

Hi,

thank you for your effort. Fortunately after many attempts, I solved my problem.

I explain how I get the solution.

These two formulas allow me to find the range coordinate via ADDRESS function:

AS12=ADDRESS(MATCH(value_to_search, range_where_to_search,1)+ROW(cell1)-1,COLUMN(cell1),1) let say $A$10

AT12=ADDRESS(MATCH(value_to_search, range_where_to_search,1)+ROW(cell2)-1,COLUMN(cell2),1) let say $C$10

If I use the following formula, I get 0 (zero) as @Lupp suggested:

INTERCEPT(INDIRECT(AS12 & ":$C$100"),INDIRECT(AT12 & ":$A$100"))

Finally, the following two formulas produce equivalent results:

INTERCEPT(INDIRECT(ADDRESS(MATCH(value_to_search, range_where_to_search,1)+ROW(cell2)-1,COLUMN(cell2),1)):$C$100,INDIRECT(ADDRESS(MATCH(value_to_search, range_where_to_search,1)+ROW(cell1)-1,COLUMN(cell1),1)):$A$100)

INTERCEPT(INDIRECT(ADDRESS(MATCH(value_to_search, range_where_to_search,1)+ROW(cell2)-1,COLUMN(cell2),1) & ":$C$132"),INDIRECT(ADDRESS(MATCH(value_to_search, range_where_to_search,1)+ROW(cell1)-1,COLUMN(cell1),1) & ":$A$100"))

However, the former is a better choice because it allows to dynamically change the range extremes without modifying the formula.

Best regards.

[Lupp]

When I answered the thread seemed explicitly to be about the use of the INDIRECT function. Now it seems to me as if it was basically: “How can I make range references variable?”
In many cases the OFFSET function allows for more flexible and concise solutions than combinations of INDIRECT with multiple calls to ADDRESS and with text concatenation. Read about OFFSET and try. Your formuals look too sophisticated and complicated to me.