I have a strange problem with libreoffice calc latest version 220.127.116.11 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
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
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
Unfortunately, it does not work with
SLOPE(H2,$A$10:$A$100), INTERCEPT(H2,$A$10:$A$100) and
So is there any intrinsic limit about using
INDIRECT(ADDRESS()) together with functions that take two range as parameters?
For curiosity, I tried also with the same version of libreoffice on windows and with excel 2016 and both do not work.