First let me credit Joseph Rubin's website as the source for this formula:

=INDEX($A$2:$A$13,(ROW()-2)*3+3)

to be used to grab every third value from a list (range) in $A$2:$A&13. The
results look like this:

a c
b f
c i
d ...
e
f
g
h
i

My problem is that I need to calculate from the bottom up of a range so that
the last value is always at the end in the manufactured "every 3rd value"
list. So, for the other end of the alphabet, for example,

r
s
t
u
v
w ...
x t
y w
z z

I know I can just use =z to get started with the new column end, but I am
struggling with the formula reversing up from there.

My thanks in advance for any help! Cheers! Brad

Use a helper column. Enter the numbers 1 thru n. Sort the stuff by the
helper column in descending order. This should flip your list from last to
first. Only then apply the Rubin formula.
--
Gary's Student


"Brad" wrote:

First let me credit Joseph Rubin's website as the source for this formula:

=INDEX($A$2:$A$13,(ROW()-2)*3+3)

to be used to grab every third value from a list (range) in $A$2:$A&13. The
results look like this:

a c
b f
c i
d ...
e
f
g
h
i

My problem is that I need to calculate from the bottom up of a range so that
the last value is always at the end in the manufactured "every 3rd value"
list. So, for the other end of the alphabet, for example,

r
s
t
u
v
w ...
x t
y w
z z

I know I can just use =z to get started with the new column end, but I am
struggling with the formula reversing up from there.

My thanks in advance for any help! Cheers! Brad

Gary, yes, I could set it up that way for a static-valued column of values,
but this is a real time application where the numbers change constantly.
So, I am not sure sorting is an alternative. Brad


"Gary''s Student" wrote in message
...
Use a helper column. Enter the numbers 1 thru n. Sort the stuff by the
helper column in descending order. This should flip your list from last
to
first. Only then apply the Rubin formula.
--
Gary's Student


"Brad" wrote:

First let me credit Joseph Rubin's website as the source for this
formula:

=INDEX($A$2:$A$13,(ROW()-2)*3+3)

to be used to grab every third value from a list (range) in $A$2:$A&13.
The
results look like this:

a c
b f
c i
d ...
e
f
g
h
i

My problem is that I need to calculate from the bottom up of a range so
that
the last value is always at the end in the manufactured "every 3rd value"
list. So, for the other end of the alphabet, for example,

r
s
t
u
v
w ...
x t
y w
z z

I know I can just use =z to get started with the new column end, but I am
struggling with the formula reversing up from there.

My thanks in advance for any help! Cheers! Brad

=INDEX(A1:A13,MAX(IF(ISBLANK(A1:A13),0,ROW(A1:A13) ))-ROW()*3+6)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Brad" wrote in message
...
Gary, yes, I could set it up that way for a static-valued column of
values,
but this is a real time application where the numbers change constantly.
So, I am not sure sorting is an alternative. Brad


"Gary''s Student" wrote in
message
...
Use a helper column. Enter the numbers 1 thru n. Sort the stuff by the
helper column in descending order. This should flip your list from last
to
first. Only then apply the Rubin formula.
--
Gary's Student


"Brad" wrote:

First let me credit Joseph Rubin's website as the source for this
formula:

=INDEX($A$2:$A$13,(ROW()-2)*3+3)

to be used to grab every third value from a list (range) in $A$2:$A&13.
The
results look like this:

a c
b f
c i
d ...
e
f
g
h
i

My problem is that I need to calculate from the bottom up of a range so
that
the last value is always at the end in the manufactured "every 3rd
value"
list. So, for the other end of the alphabet, for example,

r
s
t
u
v
w ...
x t
y w
z z

I know I can just use =z to get started with the new column end, but I
am
struggling with the formula reversing up from there.

My thanks in advance for any help! Cheers! Brad