Interpreting "comma" where an optional argument is

[Epinn]

To all:

This is a continuation of the discussion from the following thread.

http://groups.google.ca/group/micro...47a7538d98438064?tvc=1&hl=en#47a7538d98438064

or http://tinyurl.com/wj2s3

JMB,

Thank you for taking the time to help me solve my mystery plus *more*. I appreciate it very much.

I had no problem dealing with "optional" when INDEXing on a single *column* because the column number comes *after* the row number in the syntax. But I was quite confused on *row* because of the position of the row_number in the syntax. I was quite surprised that a comma was not needed where the row_number was called for.

This is the truth. I have always been under the impression (at least with other programming languages) that comma (in this context) means I am omitting the argument which is optional. I never thought of it the way you described it. This is interesting but at the same time a bit of a problem for me. This is simply because I don't know how to translate "empty (or blank)" into the function. As you said, it is Excel Help terminology and I should get used to it. But I can translate a zero into the function and I think I may be more ready to accept "...... but it is *zero*."

=INDEX(A11,0,3) and
=INDEX(A11,,3)
return the same result as =INDEX(A11,3) and =INDEX(A11,1,3).

I am surprised that =INDEX(A11,0,3) works and doesn't give me an error.

Trying to translate "empty or blank" ......
=INDEX(A11,"",3) or =INDEX(A11," ",3) of course will give the #VALUE! error. <G>
"" is null and not even blank.

For the record, my true confusion was that =INDEX(A11,3) worked without me placing a comma where the row_number was supposed to be. Now I know Excel is pretty flexible and that it will take care of all four versions listed above. Interesting!

Your illustration of IF( ) and comma is precious. I didn't even know that I could put two comma's in the IF function.

Once again, thanks for your guidance. Look forward to chatting with you again.

Epinn

 

[Guest]

First, for clarification, the help file uses the wording "blank argument" but
I think it could be confusing since the empty string "" is often referred to
as "blank". I use the term empty, which may not be right, it just helps me
keep it straight in my mind.

=INDEX(A11,0,3)

A 0 is used to return an entire row/column. In this case, the function
returns all rows of the third column. Since there is only 1 row, you still
only get one element. A13, however would return 3 elements and would need
to entered as an array formula to see all of the elements.

=INDEX(A11,,3)

Never tried it this way. Perhaps designed to treat an empty/blank argument
the same as if it were omitted (unlike the IF function).

 

[Epinn]

A 0 is used to return an entire row/column. <<

Yes, I read that from Excel Help and might have seen it before. But I forgot about it when it was a *single row*. Thank you for reminding me. I also realize that I must highlight a *column* before I enter the formula with CSE if I want to return a *column*. If I highlight a *row* and the formula says return a *column*, I will get wrong result - the first value repeated.

From Excel Help:

"If each area in reference contains only one row or column, the row_num or column_num argument, respectively, is optional. For example, for a single row reference, use INDEX(reference,,column_num)."

So, it is official. But it doesn't say that I can leave out the comma and shift the position of the column_num argument to the left. Guess I learn from experiment.

I found something interesting with the IF( ).

A1: blank i.e. nothing keyed in =IF(A1,) returns FALSE.
A1: text i.e. letters =IF(A1,) returns #VALUE!
A1: numbers i.e. positive or negative but not zero =IF(A1,) returns 0
A1: 0 =IF(A1,) returns FALSE
A1: null string i.e. ="" =IF(A1,) returns #VALUE!

I can guess why 0 and blank return FALSE, but surprised that a number returns a TRUE condition but not letters and/or null.

No idea what's going on and *don't* plan to get to the bottom. But if anyone wants to comment, please feel free to do so.

This is my discovery for today. Thank you for reading.

Epinn

First, for clarification, the help file uses the wording "blank argument" but
I think it could be confusing since the empty string "" is often referred to
as "blank". I use the term empty, which may not be right, it just helps me
keep it straight in my mind.

=INDEX(A11,0,3)

A 0 is used to return an entire row/column. In this case, the function
returns all rows of the third column. Since there is only 1 row, you still
only get one element. A13, however would return 3 elements and would need
to entered as an array formula to see all of the elements.

=INDEX(A11,,3)

Never tried it this way. Perhaps designed to treat an empty/blank argument
the same as if it were omitted (unlike the IF function).

 

[Harlan Grove]

Epinn wrote...

Yes, I read that from Excel Help and might have seen it before. . . .

Then why not try rereading online help before posting?

. . . But I forgot about it when it was a *single row*. Thank you for reminding me. I also realize that I must
highlight a *column* before I enter the formula with CSE if I want to return a *column*. If I highlight a *row*
and the formula says return a *column*, I will get wrong result - the first value repeated.

Incorrect. Excel returns the CORRECT result, but you don't know what it
is.

Excel automatically extends 1D arrays in the other worksheet dimension.
So entering ={1;2;3} in A11 will result in each cell in A11
returning 1. Likewise, entering ={1,2,3,4} in A1:A5 will return 1 in
each cell in A1:A5. When you enter the formula
={1;2;3}*{1,10,100,1000}, Excel extends both arrays in the other
dimension, so this formulas is equivalent to

={1,1,1,1;2,2,2,2;3,3,3,3}*{1,10,100,1000;1,10,100,1000;1,10,100,1000}

While this is either undocumented or poorly documented, Excel has
always worked this way.

From Excel Help:

"If each area in reference contains only one row or column, the row_num or column_num argument,
respectively, is optional. For example, for a single row reference, use INDEX(reference,,column_num)."

....

Given 1 in A1, b in B1, a in A2, 2 in B2, ="" in A3 and B3 blank,
=INDEX(A1:B1,,2) returns B but only because there's a single row in
A1:B1. Similarly, =INDEX(B1:B3,1) and =INDEX(B1:B3,1,) both return b
but only because B1:B3 has only one column. When INDEX's 1st argument
evaluates to a 2D array, missing 2nd or 3rd arguments are treated as 0.
Sloppy documentation of INDEX, but this is hardly the only instance of
sloppy documentation.

 

[Epinn]

Thank you Sir for taking time to share your wisdom. I'll read and reread to make sure that I truly understand and remember.

Just want to say that I make a point to do my best reading Help, researching, analyzing and experimenting before I post. A few things can happen...... I choose not to list them here because if I do, it will sound like talking back. Believe me, I did set a goal to refrain from posting. Give me time. I promise I'll try harder in my pre-post search.



In this case, the answer to the original question I posted was no where to be found. The "0" business was just a side comment.



Thanks again for your guidance.



Epinn

Yes, I read that from Excel Help and might have seen it before. . . .

Then why not try rereading online help before posting?

. . . But I forgot about it when it was a *single row*. Thank you for reminding me. I also realize that I must
highlight a *column* before I enter the formula with CSE if I want to return a *column*. If I highlight a *row*
and the formula says return a *column*, I will get wrong result - the first value repeated.

Incorrect. Excel returns the CORRECT result, but you don't know what it
is.

Excel automatically extends 1D arrays in the other worksheet dimension.
So entering ={1;2;3} in A11 will result in each cell in A11
returning 1. Likewise, entering ={1,2,3,4} in A1:A5 will return 1 in
each cell in A1:A5. When you enter the formula
={1;2;3}*{1,10,100,1000}, Excel extends both arrays in the other
dimension, so this formulas is equivalent to

={1,1,1,1;2,2,2,2;3,3,3,3}*{1,10,100,1000;1,10,100,1000;1,10,100,1000}

While this is either undocumented or poorly documented, Excel has
always worked this way.

From Excel Help:

"If each area in reference contains only one row or column, the row_num or column_num argument,
respectively, is optional. For example, for a single row reference, use INDEX(reference,,column_num)."

....

Given 1 in A1, b in B1, a in A2, 2 in B2, ="" in A3 and B3 blank,
=INDEX(A1:B1,,2) returns B but only because there's a single row in
A1:B1. Similarly, =INDEX(B1:B3,1) and =INDEX(B1:B3,1,) both return b
but only because B1:B3 has only one column. When INDEX's 1st argument
evaluates to a 2D array, missing 2nd or 3rd arguments are treated as 0.
Sloppy documentation of INDEX, but this is hardly the only instance of
sloppy documentation.