tricks of gurus

[hatman]

"On the other hand, if you are looking for a single cell..."

FWIW, index() can return a range:

"INDEX(array,row_num,column_num) returns the value of a specified cell or array of cells within array."

...not to mention the index():index() idiom

I don't follow that (either one)... can you give a couple of examples?

 

[PaddyD]

As in:

Book1
	A	B	C	D
1	1	2	3
2	4	5	6
3	7	8	9
4
5	15
6	7
Index Examples

=SUM(INDEX(A1:C3,2,0))

...specifying 0 in the column / row argument returns the entire row / column

=SUM(INDEX(A1:C3,1,2):INDEX(A1:C3,2,2))

evaluates to sum(b1:b2). I strongly suspect that the overhead of two calls to index() is less than that of constantly recalculating the offset() equivalent.

 

[hatman]

I had no idea that a zero did that... wow, that's even more powerful than I suspected...

Thanx PaddyD

 

[Greg Truby]

Never had call to use that 2^nd syntax, Paddy. Question is will I remember it when I need it. As for the other, I've always done that by leaving the arg blank. Are you aware of any downside to leaving it blank versus explicitely dropping in a zero?

 

[PaddyD]

"Are you aware of any downside to leaving it blank versus explicitely dropping in a zero?"

Proves you mean it, much like putting in the brackets to make explicit the fact that you want a calculation to be calculated in the order of operator precendence. Can't think of anything else.

 

[hatman]

Never had call to use that 2^nd syntax, Paddy. Question is will I remember it when I need it.

Isn't that always the challenge?

 

[xld]

=SUM(INDEX(A1:C3,2,0))

...specifying 0 in the column / row argument returns the entire row / column

Not the whole row/column, just the intersection of the array with the specified row/column.

 

[PaddyD]

"Not the whole row/column, just the intersection of the array with the specified row/column."

Indeed - given the earlier quote for the syntax:

INDEX(array,row_num,column_num) returns the value of a specified cell or array of cells within array."

...had taken that as read, but may as well be explicit about these things!

 

[nbrcrunch]

My goodness. I'm the one that started this thread nearly 2 years ago, complained that no one seemed interest, then disappeared for a while. I come back to find this wealth of contributions! I've transcribed the ones I can use. Thanks to all you experts.

(For the past several months I learned a great deal about SQL and performing "business analysis." What little need I've had for VBA has come in handy, but I definitely am not working at the level many of you seem to need for VBA.)

I've wanted to learn, but unless there's a business need, I usually focus on the matters at hand.

 

[erik.van.geit]

My goodness. I'm the one that started this thread nearly 2 years ago, complained that no one seemed interest, then disappeared for a while. I come back to find this wealth of contributions! I've transcribed the ones I can use. Thanks to all you experts.

Aha, WELCOME back,
I did almost email you to inform you of the good news