Two parameters functions: slope, intercept, etc. do not work with indirect(address()) as one parameter [closed]
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.
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
H2inSLOPE'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.@Mike Kaganski: 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.My meaning was this: "...the function thinks that the "H2" is the address of its range..."
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.
:) terminology... my English is far from perfect, and also my thinking :)