=INDEX($B$2:$P$2,COLUMNS(B$2:$P$2))

[Dave F]

Can someone explain to me why the above formula will reverse the order
of the contents of columns B?

This is my understanding: the COLUMNS(...) portion identifies the
column number in the range B22, and the INDEX(....) portion returns
the contents of the cell corresponding to that column number in the
range B22.

Sound correct?

Thanks,

Dave

 

[Charles Williams]

Yes thats correct, so as you drag the formula across to the right the
Columns(B$2:$P$2) gives 15, then Columns(C$2:$P$2) gives 14 then 13 etc so
the numbers returned get reversed.

Charles
_________________________________________
FastExcel 2.3
Name Manager 4.0
http://www.DecisionModels.com

 

[Sandy Mann]

COLUMNS() does not return the column number ather it returns a count of the
number of columns in the range. So COLUMNS(B$2:$P$2) will return 15 - the
number of columns in the range. Because the Column letter B is not absolute
it will index to C$2 etc as the formula is dragged along th row, returning
14, 13 and so on. This is the amount that INDEX() indexes along gthe range.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[RagDyeR]

Usually, the 2nd argument of Index() represents the row, and the 3rd
designates the column.

However, if using a 1 dimension array, the 2nd argument pertains to *that*
single dimension, be it row or column.

Indexing the range B22, the 2nd argument designates which *column* to
reference.

In this formula, the Columns() function acts *strictly* as a number
generator.

Columns() with an *s*, returns the number of columns in an array.
=Columns(A:A) equals 1
=Columns (A:Z) equals 26

Drag "=Columns(A:A)" along any row, across columns, and it *still* returns
"1", even though the formula itself increments:
=Columns(A:A) =Columns(B:B) =Columns(C:C) ... etc.

However, anchor one of the references with absolute addressing, and you'll
increment the relative column address (decrement if dragging left), thus
modifying the numerical return:


=Columns($A:A) returns 1, =Columns($A:B) returns 2,=Columns($A:C) returns 3,
.... etc.

=Columns(A:$Z) returns 26, =Columns(B:$Z) returns 25, =Columns(C:$Z) returns
24, ... etc.

So, the above formula could just as well have been written:

=INDEX($B$2:$P$2,COLUMNS(B:$P))
OR
=INDEX($B$2:$P$2,COLUMNS(A:$O))
OR even
=INDEX($B$2:$P$2,COLUMNS(L:$Z))
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================



Can someone explain to me why the above formula will reverse the order
of the contents of columns B?

This is my understanding: the COLUMNS(...) portion identifies the
column number in the range B22, and the INDEX(....) portion returns
the contents of the cell corresponding to that column number in the
range B22.

Sound correct?

Thanks,

Dave