Hi,
This is sortof the inverse of indirect(), which takes text and outputs a range.
I have looked but can not find out how to do this.
The reason I need this is that I have a sheet with the formula in a cell is:
=average(someRange) that returns the average.
someRange is calculated and I am not sure it is correct.
I’d like a function, someFunction(), such that,
if someRange is A10:B45
=someFunction(someRange) returns “A10:B45”
Thank you.
When you pass a range to a Cell Function, it will be passed the contents of the cells only, but not the other informations about the cell range. The name and the position of the range, the formatting properties will not be passed, therefore you can not “decode” these properties from a passed cell range by Cell Functions even if they are custom - macro based - cell functions.
Workaround: Create a custom cell function, and pass the Row and Column numbers of the top-left and the bottom-right cells of the range to it. Then you will able to get the textual reference of the passed cell range (the name, or the “A1” type reference by usage the relevant API functions.)
What information gain does such a function give you? Obviously you already know »someRange« beforehand!
As I said in OP I’m not sure I have calculated the range properly, I’d like to confirm that what I think I’m doing is actually what is happening.
Also it would be useful for debugging, I tend to put my formulas together in small bits seeing what each bit outputs to the next, but if a range is being output you can’t do this.
I have changed the way I approach the problem,
instead of using =AVERAGE(OFFSET(INDIRECT(ADDRESS(
I have done the offset and address part using string functions (my_text_range) in a separate column
and used, =Average(indirect(my_text_range))
Only problem is if I insert or delete a column, I will have to remember to modify the string calculation.
.
Zizi64
I don’t understand what you are saying.
If indirect doesnt return a range but passes the contents of the cells only, but not the other informations about the cell range,
why does =ROW(INDIRECT(“a10:b45”)) returns 10, first row of the range which row() isn’t supposed to know about.
for the same reason as =ROW(a10:b45) returns 10 (respectivly as array-formula a range with numbers from 10 to 45)
Sorry, I’m lost.
if A10 has value 41, A11 has value 42, A12 has value 43,
.
then when Zizi64 says
When you pass a range to a Cell Function, it will be passed the contents of the cells only, but not the other informations about the cell range. The name and the position of the range, the formatting properties will not be passed,
I read that as saying, that in ROW(INDIRECT(“a10:a12”))
INDIRECT(“a10:a12”) will return {41,42,43} (forgive me if the syntax is incorrect)
so row({41,42,43}) can’t return 10.
.
Do we agree that =SUM(A10:A12) should return 126 but not 33 ??
Do we agree that =MIN(A10:A12) should return 41 ??
Do we agree that =ROW(A10:A12) should return a array ( 10 … 11 … 12 ) ??
So roughly:
"literal string" back to a cell-range-reference
ps. edit for the sake of nitpicking
Caution! This is wrong.
ROW() expects a reference to a Cell or to a CellRange given as argument.
Used in a sigle-cell formula it returns the row number (1-based) of the first row referenced by the argument.
Entereed for array-evaluation (Ctrl+Shift+Enter) it returns the sequence of the row numbers covered by the argument as a one-column output which will be locked.
Calc formulas can only return numbers, strings or error values, but never objects.
And where does my explanation contradict this 
The contradiction isn’t concerning the part of my comment which you quoted.
It’s here:
The question so far has not been about the difference between single-cell-reference versus array-reference!
I therefore did not go into this explicitly again
Oh my! Did you actually read what I wrote, including…?
That’s not an object …
Did you read and understand what I wrote?
You may also have a look at: https://api.libreoffice.org/docs/idl/ref/servicecom_1_1sun_1_1star_1_1sheet_1_1AddIn.html
exactly the com::sun::star::table::XCellRange below:
Each AddIn function can take parameters of the following types:
long
for integer values.
double
for floating point values.
string
for text strings.
long[][]
for (two-dimensional) arrays of integer values.
double[][]
for (two-dimensional) arrays of floating point values.
string[][]
for (two-dimensional) arrays of text strings.
any[][]
for (two-dimensional) arrays of mixed contents. Each any will contain a double or a string, depending on the data.
any
Depending on the data, a double, a string, or an any[][] will be passed. If no argument is specified in the function call, VOID will be passed. This allows for optional parameters.
com::sun::star::table::XCellRange ⇐⇐ #### look here
for a com::sun::star::table::XCellRange interface to the source data.
com::sun::star::beans::XPropertySet
for a com::sun::star::beans::XPropertySet interface to the SpreadsheetDocument making the function call. Only one parameter of this type is allowed in each function. It can be used to query document settings like SpreadsheetDocumentSettings::NullDate.
any[]
for varying parameters. Only the last parameter of a function may have this type. It will be filled with the remaining arguments of the function call that were not used for the previous parameters. Each element of the sequence will be filled as in the case of any above.Yes, it’s hard to hit the dartboard when you’re aiming at the scorekeeper.
Fine.
Is all that consistent with what Zizi64 said?
Yes it is! because for example =ROW( INDIRECT("A10:A12") )
The “…” was the important part of the question.
I didn’t ask if your previous answer was self consistent.