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

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

Sound correct?

Thanks,

Dave

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

"Dave F" wrote in message
...
Can someone explain to me why the above formula will reverse the order
of the contents of columns B:P?

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

Sound correct?

Thanks,

Dave

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


Replace @mailinator.com with @tiscali.co.uk


"Dave F" wrote in message
...
Can someone explain to me why the above formula will reverse the order
of the contents of columns B:P?

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

Sound correct?

Thanks,

Dave

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 B2:P2, 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!
================================================== ===



"Dave F" wrote in message
...
Can someone explain to me why the above formula will reverse the order
of the contents of columns B:P?

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

Sound correct?

Thanks,

Dave