reverse order of numbers
 

Assume I have the following values in the range A1:C1:
1 | 2 | 3

Is there a formula I can use in A3:C3 that would reverse this order;
i.e., 3 | 2 | 1

Or is this best done in VBA?

reverse order of numbers
 

If you put this in A3 and copy across

=INDEX($A$1:$C$1,COLUMNS($A$1:$C$1)+1-COLUMNS($A$1:A1))

note that the last occurrence of COLUMNS should always refer to $A$1:A1 in
the first cell

you can get a shorter formula using offset and column but that would be
volatile (offset that is)

--

Regards,

Peo Sjoblom

"Dave F" wrote in message
ups.com...
Assume I have the following values in the range A1:C1:
1 | 2 | 3

Is there a formula I can use in A3:C3 that would reverse this order;
i.e., 3 | 2 | 1

Or is this best done in VBA?

reverse order of numbers
 

Another way:

=INDEX($A$1:$C$1,3-(COLUMN(A1)-1))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Peo Sjoblom" wrote in message
...
If you put this in A3 and copy across

=INDEX($A$1:$C$1,COLUMNS($A$1:$C$1)+1-COLUMNS($A$1:A1))

note that the last occurrence of COLUMNS should always refer to $A$1:A1 in
the first cell

you can get a shorter formula using offset and column but that would be
volatile (offset that is)

--

Regards,

Peo Sjoblom

"Dave F" wrote in message
ups.com...
Assume I have the following values in the range A1:C1:
1 | 2 | 3

Is there a formula I can use in A3:C3 that would reverse this order;
i.e., 3 | 2 | 1

Or is this best done in VBA?

reverse order of numbers
 

A small suggestion to remove the hardcoded 3:
=INDEX($A1:$C1,COLUMNS(A1:$C1))
copied across.


"Ragdyer" wrote:

Another way:

=INDEX($A$1:$C$1,3-(COLUMN(A1)-1))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Peo Sjoblom" wrote in message
...
If you put this in A3 and copy across

=INDEX($A$1:$C$1,COLUMNS($A$1:$C$1)+1-COLUMNS($A$1:A1))

note that the last occurrence of COLUMNS should always refer to $A$1:A1 in
the first cell

you can get a shorter formula using offset and column but that would be
volatile (offset that is)

--

Regards,

Peo Sjoblom

"Dave F" wrote in message
ups.com...
Assume I have the following values in the range A1:C1:
1 | 2 | 3

Is there a formula I can use in A3:C3 that would reverse this order;
i.e., 3 | 2 | 1

Or is this best done in VBA?

reverse order of numbers
 

I presume all the suggested formulas were posted with an eye that the OP
will be using more than 3 columns; however, if the OP's posting was really
all he wanted, then I think this might be easier. Place these formulas in
the indicated cells...

A3: =C1
B3: =B1
C3: =A1

<g

Rick


"JMB" wrote in message
...
A small suggestion to remove the hardcoded 3:
=INDEX($A1:$C1,COLUMNS(A1:$C1))
copied across.


"Ragdyer" wrote:

Another way:

=INDEX($A$1:$C$1,3-(COLUMN(A1)-1))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Peo Sjoblom" wrote in message
...
If you put this in A3 and copy across

=INDEX($A$1:$C$1,COLUMNS($A$1:$C$1)+1-COLUMNS($A$1:A1))

note that the last occurrence of COLUMNS should always refer to $A$1:A1
in
the first cell

you can get a shorter formula using offset and column but that would be
volatile (offset that is)

--

Regards,

Peo Sjoblom

"Dave F" wrote in message
ups.com...
Assume I have the following values in the range A1:C1:
1 | 2 | 3

Is there a formula I can use in A3:C3 that would reverse this order;
i.e., 3 | 2 | 1

Or is this best done in VBA?

reverse order of numbers
 

I will be using 15 columns. The three columns given were an example.

Thanks to all for your responses.

Dave

On Sep 16, 2:25 am, "Rick Rothstein \(MVP - VB\)"
wrote:
I presume all the suggested formulas were posted with an eye that the OP
will be using more than 3 columns; however, if the OP's posting was really
all he wanted, then I think this might be easier. Place these formulas in
the indicated cells...

A3: =C1
B3: =B1
C3: =A1

<g

Rick

"JMB" wrote in message

...



A small suggestion to remove the hardcoded 3:
=INDEX($A1:$C1,COLUMNS(A1:$C1))
copied across.

"Ragdyer" wrote:

Another way:

=INDEX($A$1:$C$1,3-(COLUMN(A1)-1))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Peo Sjoblom" wrote in message
.. .
If you put this in A3 and copy across

=INDEX($A$1:$C$1,COLUMNS($A$1:$C$1)+1-COLUMNS($A$1:A1))

note that the last occurrence of COLUMNS should always refer to $A$1:A1
in
the first cell

you can get a shorter formula using offset and column but that would be
volatile (offset that is)

--

Regards,

Peo Sjoblom

"Dave F" wrote in message
oups.com...
Assume I have the following values in the range A1:C1:
1 | 2 | 3

Is there a formula I can use in A3:C3 that would reverse this order;
i.e., 3 | 2 | 1

Or is this best done in VBA?- Hide quoted text -

- Show quoted text -