Originally Posted by
mbrown89
I need ROW(), COLUMN() for good reasons.
Please elaborate... if you need to reference based on current cell position use RC referencing in the INDIRECT, eg:
Originally Posted by
mbrown
Do you think it's a bug or can you offer a good explanation that it should return 64?
Not sure I would say it's a bug... but equally I don't have the knowledge to explain it "properly".
I would put it down to the fact that
a) All Names formula are processed as Array functions (same holds true for Validation, Conditional Formatting etc)
note: this does not mean all names would return output type of 64
To illustrate, if we add:
We know that we to retrieve MAX of A in a cell we need to use an Array, eg:
Result, when confirmed as an Array, is 3 as expected.
We can illustrate the Array nature of Name functions by creating the following Name:
Now in our cell we can replace the earlier CSE function with:
The result will be 3 ... ie the Name is processed as a CSE function by default.
b) The ROW / COLUMN functions return value arrays - eg:
The return is 1, now, in the formula bar highlight the function and press F9 - you will see: {1;2;3;4;5;6;7;8;9;10} - ie an array of values
Now change the formula reference range from A1:A10 to A1 (ie single cell) and repeat the above - you will see {1} - ie still an "array" of sorts
Now change the formula from ROW(A1) to ROWS(A1) and repeat the above - you will see 1 - ie not an array return.
So, conceded, you're using ROW()/COLUMN() outside of a Named Range without the 64 return but I would say that a) coupled with b) leads to Type 64 output.
I am trying to think of other ways to demonstrate the same output using different functions but this early on a Sat morning I'm struggling....
Bookmarks