I would like assistance with a formula to accomplish the following:
From a row of data I would like to generate a second row that contains 20
nonzero cells from the original row of data beginning with the cell working
from right to left. Note: I will be adding data to the original row and would
like the formula to regenerate a new row considering the new data and
dropping off data that is more than 20 nonzero cells to the right of the last
entry.

Do you want the order retained or reversed??

That is, most recent first or most recent last?
--
Gary''s Student - gsnu200827


"Larry L" wrote:

I would like assistance with a formula to accomplish the following:
From a row of data I would like to generate a second row that contains 20
nonzero cells from the original row of data beginning with the cell working
from right to left. Note: I will be adding data to the original row and would
like the formula to regenerate a new row considering the new data and
dropping off data that is more than 20 nonzero cells to the right of the last
entry.

I want the most recent retained -- so the formula needs to consider data from
right to left in the row.

"Gary''s Student" wrote:

Do you want the order retained or reversed??

That is, most recent first or most recent last?
--
Gary''s Student - gsnu200827


"Larry L" wrote:

I would like assistance with a formula to accomplish the following:
From a row of data I would like to generate a second row that contains 20
nonzero cells from the original row of data beginning with the cell working
from right to left. Note: I will be adding data to the original row and would
like the formula to regenerate a new row considering the new data and
dropping off data that is more than 20 nonzero cells to the right of the last
entry.

The most recent will be last in the new row.

"Larry L" wrote:

I want the most recent retained -- so the formula needs to consider data from
right to left in the row.

"Gary''s Student" wrote:

Do you want the order retained or reversed??

That is, most recent first or most recent last?
--
Gary''s Student - gsnu200827


"Larry L" wrote:

I would like assistance with a formula to accomplish the following:
From a row of data I would like to generate a second row that contains 20
nonzero cells from the original row of data beginning with the cell working
from right to left. Note: I will be adding data to the original row and would
like the formula to regenerate a new row considering the new data and
dropping off data that is more than 20 nonzero cells to the right of the last
entry.

What Gary meant was this:

Assume this is your data:

1 2 3 4 5

Do you want the result to appear as:

1 2 3 4 5

Or:

5 4 3 2 1

--
Biff
Microsoft Excel MVP


"Larry L" wrote in message
...
The most recent will be last in the new row.

"Larry L" wrote:

I want the most recent retained -- so the formula needs to consider data
from
right to left in the row.

"Gary''s Student" wrote:

Do you want the order retained or reversed??

That is, most recent first or most recent last?
--
Gary''s Student - gsnu200827


"Larry L" wrote:

I would like assistance with a formula to accomplish the following:
From a row of data I would like to generate a second row that
contains 20
nonzero cells from the original row of data beginning with the cell
working
from right to left. Note: I will be adding data to the original row
and would
like the formula to regenerate a new row considering the new data and
dropping off data that is more than 20 nonzero cells to the right of
the last
entry.

Assuming the original row has 20 cells with data
I would like the new data to be 1,2,...19,20

When an additional cell is added to the original row I want the new data to
be 2,3,....20,21

Ocassionally the original row may have a cell that does not have data, in
that case
I want the new row to look at additional cells so that it will always have
all the non blank cells up to 20,

"T. Valko" wrote:

What Gary meant was this:

Assume this is your data:

1 2 3 4 5

Do you want the result to appear as:

1 2 3 4 5

Or:

5 4 3 2 1

--
Biff
Microsoft Excel MVP


"Larry L" wrote in message
...
The most recent will be last in the new row.

"Larry L" wrote:

I want the most recent retained -- so the formula needs to consider data
from
right to left in the row.

"Gary''s Student" wrote:

Do you want the order retained or reversed??

That is, most recent first or most recent last?
--
Gary''s Student - gsnu200827


"Larry L" wrote:

I would like assistance with a formula to accomplish the following:
From a row of data I would like to generate a second row that
contains 20
nonzero cells from the original row of data beginning with the cell
working
from right to left. Note: I will be adding data to the original row
and would
like the formula to regenerate a new row considering the new data and
dropping off data that is more than 20 nonzero cells to the right of
the last
entry.

Assuming your original data is in Row 1, enter this *array* formula anywhere
*except* in Row 1, and copy across 20 columns:

=INDEX(1:1,LARGE(COLUMN(1:1)*(1:1<""),COLUMNS(A:$ T))):$IV$1

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

If you should ever wish to see your data in the other direction, try this,
also an *array* formula:

=INDEX(1:1,LARGE(COLUMN(1:1)*(1:1<""),COLUMNS($A: A))):$IV$1

--

HTH,

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


"Larry L" wrote in message
...
Assuming the original row has 20 cells with data
I would like the new data to be 1,2,...19,20

When an additional cell is added to the original row I want the new data to
be 2,3,....20,21

Ocassionally the original row may have a cell that does not have data, in
that case
I want the new row to look at additional cells so that it will always have
all the non blank cells up to 20,

"T. Valko" wrote:

What Gary meant was this:

Assume this is your data:

1 2 3 4 5

Do you want the result to appear as:

1 2 3 4 5

Or:

5 4 3 2 1

--
Biff
Microsoft Excel MVP


"Larry L" wrote in message
...
The most recent will be last in the new row.

"Larry L" wrote:

I want the most recent retained -- so the formula needs to consider
data
from
right to left in the row.

"Gary''s Student" wrote:

Do you want the order retained or reversed??

That is, most recent first or most recent last?
--
Gary''s Student - gsnu200827


"Larry L" wrote:

I would like assistance with a formula to accomplish the following:
From a row of data I would like to generate a second row that
contains 20
nonzero cells from the original row of data beginning with the cell
working
from right to left. Note: I will be adding data to the original row
and would
like the formula to regenerate a new row considering the new data
and
dropping off data that is more than 20 nonzero cells to the right
of
the last
entry.

OK, assuming there will *always* be at least 20 entries in the range...

I'm using row 1 in the example.

Array entered**

=INDEX(1:1,LARGE(IF(1:1<"",COLUMN(1:1)),20-(COLUMNS($A:A)-1)))

Copy across to a total of 20 cells.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Larry L" wrote in message
...
Assuming the original row has 20 cells with data
I would like the new data to be 1,2,...19,20

When an additional cell is added to the original row I want the new data
to
be 2,3,....20,21

Ocassionally the original row may have a cell that does not have data, in
that case
I want the new row to look at additional cells so that it will always have
all the non blank cells up to 20,

"T. Valko" wrote:

What Gary meant was this:

Assume this is your data:

1 2 3 4 5

Do you want the result to appear as:

1 2 3 4 5

Or:

5 4 3 2 1

--
Biff
Microsoft Excel MVP


"Larry L" wrote in message
...
The most recent will be last in the new row.

"Larry L" wrote:

I want the most recent retained -- so the formula needs to consider
data
from
right to left in the row.

"Gary''s Student" wrote:

Do you want the order retained or reversed??

That is, most recent first or most recent last?
--
Gary''s Student - gsnu200827


"Larry L" wrote:

I would like assistance with a formula to accomplish the
following:
From a row of data I would like to generate a second row that
contains 20
nonzero cells from the original row of data beginning with the
cell
working
from right to left. Note: I will be adding data to the original
row
and would
like the formula to regenerate a new row considering the new data
and
dropping off data that is more than 20 nonzero cells to the right
of
the last
entry.

This formula works - thank you

"T. Valko" wrote:

OK, assuming there will *always* be at least 20 entries in the range...

I'm using row 1 in the example.

Array entered**

=INDEX(1:1,LARGE(IF(1:1<"",COLUMN(1:1)),20-(COLUMNS($A:A)-1)))

Copy across to a total of 20 cells.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Larry L" wrote in message
...
Assuming the original row has 20 cells with data
I would like the new data to be 1,2,...19,20

When an additional cell is added to the original row I want the new data
to
be 2,3,....20,21

Ocassionally the original row may have a cell that does not have data, in
that case
I want the new row to look at additional cells so that it will always have
all the non blank cells up to 20,

"T. Valko" wrote:

What Gary meant was this:

Assume this is your data:

1 2 3 4 5

Do you want the result to appear as:

1 2 3 4 5

Or:

5 4 3 2 1

--
Biff
Microsoft Excel MVP


"Larry L" wrote in message
...
The most recent will be last in the new row.

"Larry L" wrote:

I want the most recent retained -- so the formula needs to consider
data
from
right to left in the row.

"Gary''s Student" wrote:

Do you want the order retained or reversed??

That is, most recent first or most recent last?
--
Gary''s Student - gsnu200827


"Larry L" wrote:

I would like assistance with a formula to accomplish the
following:
From a row of data I would like to generate a second row that
contains 20
nonzero cells from the original row of data beginning with the
cell
working
from right to left. Note: I will be adding data to the original
row
and would
like the formula to regenerate a new row considering the new data
and
dropping off data that is more than 20 nonzero cells to the right
of
the last
entry.

I have 1 more question

Now have the 20 cells populated.

I then created another row with the 10 lowest from the 20 (figured that one
out my self!)

I now want to create an if statement to put an astrick under the 10 lowest
cells in the row of 20.

The formula I used was (this is for row 51)
=IF(C49=50:50,"*","") Note row 49 contains the formula you provided to
select 20 and row 50 is the ten lowest of the 20

But this will only put an astrick under the cell that is equal to the one
above it. What I want is for an astrick for all cells in row 49 that have a
cell equal to one of the 10 cells in row 50


"T. Valko" wrote:

OK, assuming there will *always* be at least 20 entries in the range...

I'm using row 1 in the example.

Array entered**

=INDEX(1:1,LARGE(IF(1:1<"",COLUMN(1:1)),20-(COLUMNS($A:A)-1)))

Copy across to a total of 20 cells.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Larry L" wrote in message
...
Assuming the original row has 20 cells with data
I would like the new data to be 1,2,...19,20

When an additional cell is added to the original row I want the new data
to
be 2,3,....20,21

Ocassionally the original row may have a cell that does not have data, in
that case
I want the new row to look at additional cells so that it will always have
all the non blank cells up to 20,

"T. Valko" wrote:

What Gary meant was this:

Assume this is your data:

1 2 3 4 5

Do you want the result to appear as:

1 2 3 4 5

Or:

5 4 3 2 1

--
Biff
Microsoft Excel MVP


"Larry L" wrote in message
...
The most recent will be last in the new row.

"Larry L" wrote:

I want the most recent retained -- so the formula needs to consider
data
from
right to left in the row.

"Gary''s Student" wrote:

Do you want the order retained or reversed??

That is, most recent first or most recent last?
--
Gary''s Student - gsnu200827


"Larry L" wrote:

I would like assistance with a formula to accomplish the
following:
From a row of data I would like to generate a second row that
contains 20
nonzero cells from the original row of data beginning with the
cell
working
from right to left. Note: I will be adding data to the original
row
and would
like the formula to regenerate a new row considering the new data
and
dropping off data that is more than 20 nonzero cells to the right
of
the last
entry.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Larry L" wrote in message
...
This formula works - thank you

"T. Valko" wrote:

OK, assuming there will *always* be at least 20 entries in the range...

I'm using row 1 in the example.

Array entered**

=INDEX(1:1,LARGE(IF(1:1<"",COLUMN(1:1)),20-(COLUMNS($A:A)-1)))

Copy across to a total of 20 cells.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Larry L" wrote in message
...
Assuming the original row has 20 cells with data
I would like the new data to be 1,2,...19,20

When an additional cell is added to the original row I want the new
data
to
be 2,3,....20,21

Ocassionally the original row may have a cell that does not have data,
in
that case
I want the new row to look at additional cells so that it will always
have
all the non blank cells up to 20,

"T. Valko" wrote:

What Gary meant was this:

Assume this is your data:

1 2 3 4 5

Do you want the result to appear as:

1 2 3 4 5

Or:

5 4 3 2 1

--
Biff
Microsoft Excel MVP


"Larry L" wrote in message
...
The most recent will be last in the new row.

"Larry L" wrote:

I want the most recent retained -- so the formula needs to consider
data
from
right to left in the row.

"Gary''s Student" wrote:

Do you want the order retained or reversed??

That is, most recent first or most recent last?
--
Gary''s Student - gsnu200827


"Larry L" wrote:

I would like assistance with a formula to accomplish the
following:
From a row of data I would like to generate a second row that
contains 20
nonzero cells from the original row of data beginning with the
cell
working
from right to left. Note: I will be adding data to the original
row
and would
like the formula to regenerate a new row considering the new
data
and
dropping off data that is more than 20 nonzero cells to the
right
of
the last
entry.

I have 1 more question

Now have the 20 cells populated.

I then created another row with the 10 lowest from the 20 (figured that one
out my self!)

I now want to create an if statement to put an astrick under the 10 lowest
cells in the row of 20.

The formula I used was (this is for row 51)
=IF(C49=50:50,"*","") Note row 49 contains the formula you provided to
select 20 and row 50 is the ten lowest of the 20

But this will only put an astrick under the cell that is equal to the one
above it. What I want is for an astrick for all cells in row 49 that have a
cell equal to one of the 10 cells in row 50



"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Larry L" wrote in message
...
This formula works - thank you

"T. Valko" wrote:

OK, assuming there will *always* be at least 20 entries in the range...

I'm using row 1 in the example.

Array entered**

=INDEX(1:1,LARGE(IF(1:1<"",COLUMN(1:1)),20-(COLUMNS($A:A)-1)))

Copy across to a total of 20 cells.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Larry L" wrote in message
...
Assuming the original row has 20 cells with data
I would like the new data to be 1,2,...19,20

When an additional cell is added to the original row I want the new
data
to
be 2,3,....20,21

Ocassionally the original row may have a cell that does not have data,
in
that case
I want the new row to look at additional cells so that it will always
have
all the non blank cells up to 20,

"T. Valko" wrote:

What Gary meant was this:

Assume this is your data:

1 2 3 4 5

Do you want the result to appear as:

1 2 3 4 5

Or:

5 4 3 2 1

--
Biff
Microsoft Excel MVP


"Larry L" wrote in message
...
The most recent will be last in the new row.

"Larry L" wrote:

I want the most recent retained -- so the formula needs to consider
data
from
right to left in the row.

"Gary''s Student" wrote:

Do you want the order retained or reversed??

That is, most recent first or most recent last?
--
Gary''s Student - gsnu200827


"Larry L" wrote:

I would like assistance with a formula to accomplish the
following:
From a row of data I would like to generate a second row that
contains 20
nonzero cells from the original row of data beginning with the
cell
working
from right to left. Note: I will be adding data to the original
row
and would
like the formula to regenerate a new row considering the new
data
and
dropping off data that is more than 20 nonzero cells to the
right
of
the last
entry.

So, you're working on golf scores, aren't you? <g

Assume the last 20 scores are in the range A49:T49
Assume the 10 lowest scores are in the range A50:J50

Try something like this:

=IF(COUNTIF($A50:$J50,A49),"*","")

--
Biff
Microsoft Excel MVP


"Larry L" wrote in message
...
I have 1 more question

Now have the 20 cells populated.

I then created another row with the 10 lowest from the 20 (figured that
one
out my self!)

I now want to create an if statement to put an astrick under the 10 lowest
cells in the row of 20.

The formula I used was (this is for row 51)
=IF(C49=50:50,"*","") Note row 49 contains the formula you provided to
select 20 and row 50 is the ten lowest of the 20

But this will only put an astrick under the cell that is equal to the one
above it. What I want is for an astrick for all cells in row 49 that have
a
cell equal to one of the 10 cells in row 50



"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Larry L" wrote in message
...
This formula works - thank you

"T. Valko" wrote:

OK, assuming there will *always* be at least 20 entries in the
range...

I'm using row 1 in the example.

Array entered**

=INDEX(1:1,LARGE(IF(1:1<"",COLUMN(1:1)),20-(COLUMNS($A:A)-1)))

Copy across to a total of 20 cells.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Larry L" wrote in message
...
Assuming the original row has 20 cells with data
I would like the new data to be 1,2,...19,20

When an additional cell is added to the original row I want the new
data
to
be 2,3,....20,21

Ocassionally the original row may have a cell that does not have
data,
in
that case
I want the new row to look at additional cells so that it will
always
have
all the non blank cells up to 20,

"T. Valko" wrote:

What Gary meant was this:

Assume this is your data:

1 2 3 4 5

Do you want the result to appear as:

1 2 3 4 5

Or:

5 4 3 2 1

--
Biff
Microsoft Excel MVP


"Larry L" wrote in message
...
The most recent will be last in the new row.

"Larry L" wrote:

I want the most recent retained -- so the formula needs to
consider
data
from
right to left in the row.

"Gary''s Student" wrote:

Do you want the order retained or reversed??

That is, most recent first or most recent last?
--
Gary''s Student - gsnu200827


"Larry L" wrote:

I would like assistance with a formula to accomplish the
following:
From a row of data I would like to generate a second row
that
contains 20
nonzero cells from the original row of data beginning with
the
cell
working
from right to left. Note: I will be adding data to the
original
row
and would
like the formula to regenerate a new row considering the new
data
and
dropping off data that is more than 20 nonzero cells to the
right
of
the last
entry.

You are correct it is for golf

The formula works -- thank you again.

If this is not too much trouble, I would like to try to tweak the last
formula.

Here is the issue -- When I consider the 10 best of 20 scores and then apply
an * to those scores -- which I can now do thanks to you, I run into a small
issue.

I could have 5 scores of 45 and 6 scores of 46 and all the other scores
higher. With the formula as it is now, I will have 11 scores with an *.
Ideally I would have 10 scores -- to 5 scores of 45 and the 5 most recent of
the 46's.

Is there any way to accomplish this?

"T. Valko" wrote:

So, you're working on golf scores, aren't you? <g

Assume the last 20 scores are in the range A49:T49
Assume the 10 lowest scores are in the range A50:J50

Try something like this:

=IF(COUNTIF($A50:$J50,A49),"*","")

--
Biff
Microsoft Excel MVP


"Larry L" wrote in message
...
I have 1 more question

Now have the 20 cells populated.

I then created another row with the 10 lowest from the 20 (figured that
one
out my self!)

I now want to create an if statement to put an astrick under the 10 lowest
cells in the row of 20.

The formula I used was (this is for row 51)
=IF(C49=50:50,"*","") Note row 49 contains the formula you provided to
select 20 and row 50 is the ten lowest of the 20

But this will only put an astrick under the cell that is equal to the one
above it. What I want is for an astrick for all cells in row 49 that have
a
cell equal to one of the 10 cells in row 50



"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Larry L" wrote in message
...
This formula works - thank you

"T. Valko" wrote:

OK, assuming there will *always* be at least 20 entries in the
range...

I'm using row 1 in the example.

Array entered**

=INDEX(1:1,LARGE(IF(1:1<"",COLUMN(1:1)),20-(COLUMNS($A:A)-1)))

Copy across to a total of 20 cells.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Larry L" wrote in message
...
Assuming the original row has 20 cells with data
I would like the new data to be 1,2,...19,20

When an additional cell is added to the original row I want the new
data
to
be 2,3,....20,21

Ocassionally the original row may have a cell that does not have
data,
in
that case
I want the new row to look at additional cells so that it will
always
have
all the non blank cells up to 20,

"T. Valko" wrote:

What Gary meant was this:

Assume this is your data:

1 2 3 4 5

Do you want the result to appear as:

1 2 3 4 5

Or:

5 4 3 2 1

--
Biff
Microsoft Excel MVP


"Larry L" wrote in message
...
The most recent will be last in the new row.

"Larry L" wrote:

I want the most recent retained -- so the formula needs to
consider
data
from
right to left in the row.

"Gary''s Student" wrote:

Do you want the order retained or reversed??

That is, most recent first or most recent last?
--
Gary''s Student - gsnu200827


"Larry L" wrote:

I would like assistance with a formula to accomplish the
following:
From a row of data I would like to generate a second row
that
contains 20
nonzero cells from the original row of data beginning with
the
cell
working
from right to left. Note: I will be adding data to the
original
row
and would
like the formula to regenerate a new row considering the new
data
and
dropping off data that is more than 20 nonzero cells to the
right
of
the last
entry.

Wow!

You've stumped me on that one!

So far I'm coming up with zilch!

Is it absolutely necessary to identify those scores?

I'm a golfer so I know what you're doing. I can give you a single formula
that will average the lowest 10 out of the last 20 scores without having to
do all these extra steps.

I can also give you a single formula that meets the USGA guidelines for
handicaps:

If there are <10 scores it will average whatever scores are available.
If there are =10 and <=20 scores it will average the lowest 10 scores.
If there are 20 scores it will average the lowest 10 scores out of the last
20 scores.


--
Biff
Microsoft Excel MVP


"Larry L" wrote in message
...
You are correct it is for golf

The formula works -- thank you again.

If this is not too much trouble, I would like to try to tweak the last
formula.

Here is the issue -- When I consider the 10 best of 20 scores and then
apply
an * to those scores -- which I can now do thanks to you, I run into a
small
issue.

I could have 5 scores of 45 and 6 scores of 46 and all the other scores
higher. With the formula as it is now, I will have 11 scores with an *.
Ideally I would have 10 scores -- to 5 scores of 45 and the 5 most recent
of
the 46's.

Is there any way to accomplish this?

"T. Valko" wrote:

So, you're working on golf scores, aren't you? <g

Assume the last 20 scores are in the range A49:T49
Assume the 10 lowest scores are in the range A50:J50

Try something like this:

=IF(COUNTIF($A50:$J50,A49),"*","")

--
Biff
Microsoft Excel MVP


"Larry L" wrote in message
...
I have 1 more question

Now have the 20 cells populated.

I then created another row with the 10 lowest from the 20 (figured that
one
out my self!)

I now want to create an if statement to put an astrick under the 10
lowest
cells in the row of 20.

The formula I used was (this is for row 51)
=IF(C49=50:50,"*","") Note row 49 contains the formula you provided to
select 20 and row 50 is the ten lowest of the 20

But this will only put an astrick under the cell that is equal to the
one
above it. What I want is for an astrick for all cells in row 49 that
have
a
cell equal to one of the 10 cells in row 50



"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Larry L" wrote in message
...
This formula works - thank you

"T. Valko" wrote:

OK, assuming there will *always* be at least 20 entries in the
range...

I'm using row 1 in the example.

Array entered**

=INDEX(1:1,LARGE(IF(1:1<"",COLUMN(1:1)),20-(COLUMNS($A:A)-1)))

Copy across to a total of 20 cells.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and
the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Larry L" wrote in message
...
Assuming the original row has 20 cells with data
I would like the new data to be 1,2,...19,20

When an additional cell is added to the original row I want the
new
data
to
be 2,3,....20,21

Ocassionally the original row may have a cell that does not have
data,
in
that case
I want the new row to look at additional cells so that it will
always
have
all the non blank cells up to 20,

"T. Valko" wrote:

What Gary meant was this:

Assume this is your data:

1 2 3 4 5

Do you want the result to appear as:

1 2 3 4 5

Or:

5 4 3 2 1

--
Biff
Microsoft Excel MVP


"Larry L" wrote in message
...
The most recent will be last in the new row.

"Larry L" wrote:

I want the most recent retained -- so the formula needs to
consider
data
from
right to left in the row.

"Gary''s Student" wrote:

Do you want the order retained or reversed??

That is, most recent first or most recent last?
--
Gary''s Student - gsnu200827


"Larry L" wrote:

I would like assistance with a formula to accomplish the
following:
From a row of data I would like to generate a second row
that
contains 20
nonzero cells from the original row of data beginning
with
the
cell
working
from right to left. Note: I will be adding data to the
original
row
and would
like the formula to regenerate a new row considering the
new
data
and
dropping off data that is more than 20 nonzero cells to
the
right
of
the last
entry.

I have the formula for doing the average -- I think you may have given it to
me before.

Its not that important to do the * thing. I just wanted to periodically
publish the average calculation sheet. Because the 20 weeks covers parts of 2
years and the weekly score sheet only have scores for the current year, they
don't get to see all of their scores that are used for their averages. In
fact the 1st sheet they will get this season will only have 1 score with the
other 19 scores from 2007 and 2008 seasons.

I can give them the 20 score sheet without the * and they can figure it out
themselves.


Thank you again - I really appreciate your help.

Larry

"T. Valko" wrote:

Wow!

You've stumped me on that one!

So far I'm coming up with zilch!

Is it absolutely necessary to identify those scores?

I'm a golfer so I know what you're doing. I can give you a single formula
that will average the lowest 10 out of the last 20 scores without having to
do all these extra steps.

I can also give you a single formula that meets the USGA guidelines for
handicaps:

If there are <10 scores it will average whatever scores are available.
If there are =10 and <=20 scores it will average the lowest 10 scores.
If there are 20 scores it will average the lowest 10 scores out of the last
20 scores.


--
Biff
Microsoft Excel MVP


"Larry L" wrote in message
...
You are correct it is for golf

The formula works -- thank you again.

If this is not too much trouble, I would like to try to tweak the last
formula.

Here is the issue -- When I consider the 10 best of 20 scores and then
apply
an * to those scores -- which I can now do thanks to you, I run into a
small
issue.

I could have 5 scores of 45 and 6 scores of 46 and all the other scores
higher. With the formula as it is now, I will have 11 scores with an *.
Ideally I would have 10 scores -- to 5 scores of 45 and the 5 most recent
of
the 46's.

Is there any way to accomplish this?

"T. Valko" wrote:

So, you're working on golf scores, aren't you? <g

Assume the last 20 scores are in the range A49:T49
Assume the 10 lowest scores are in the range A50:J50

Try something like this:

=IF(COUNTIF($A50:$J50,A49),"*","")

--
Biff
Microsoft Excel MVP


"Larry L" wrote in message
...
I have 1 more question

Now have the 20 cells populated.

I then created another row with the 10 lowest from the 20 (figured that
one
out my self!)

I now want to create an if statement to put an astrick under the 10
lowest
cells in the row of 20.

The formula I used was (this is for row 51)
=IF(C49=50:50,"*","") Note row 49 contains the formula you provided to
select 20 and row 50 is the ten lowest of the 20

But this will only put an astrick under the cell that is equal to the
one
above it. What I want is for an astrick for all cells in row 49 that
have
a
cell equal to one of the 10 cells in row 50



"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Larry L" wrote in message
...
This formula works - thank you

"T. Valko" wrote:

OK, assuming there will *always* be at least 20 entries in the
range...

I'm using row 1 in the example.

Array entered**

=INDEX(1:1,LARGE(IF(1:1<"",COLUMN(1:1)),20-(COLUMNS($A:A)-1)))

Copy across to a total of 20 cells.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and
the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Larry L" wrote in message
...
Assuming the original row has 20 cells with data
I would like the new data to be 1,2,...19,20

When an additional cell is added to the original row I want the
new
data
to
be 2,3,....20,21

Ocassionally the original row may have a cell that does not have
data,
in
that case
I want the new row to look at additional cells so that it will
always
have
all the non blank cells up to 20,

"T. Valko" wrote:

What Gary meant was this:

Assume this is your data:

1 2 3 4 5

Do you want the result to appear as:

1 2 3 4 5

Or:

5 4 3 2 1

--
Biff
Microsoft Excel MVP


"Larry L" wrote in message
...
The most recent will be last in the new row.

"Larry L" wrote:

I want the most recent retained -- so the formula needs to
consider
data
from
right to left in the row.

"Gary''s Student" wrote:

Do you want the order retained or reversed??

That is, most recent first or most recent last?
--
Gary''s Student - gsnu200827


"Larry L" wrote:

I would like assistance with a formula to accomplish the
following:
From a row of data I would like to generate a second row
that
contains 20
nonzero cells from the original row of data beginning
with
the
cell
working
from right to left. Note: I will be adding data to the
original
row
and would
like the formula to regenerate a new row considering the
new
data
and
dropping off data that is more than 20 nonzero cells to
the
right
of
the last
entry.

I'll see if I can come up with something but this is very complicated.

I get a hdcp index card from my course and it shows you which scores were
used to calc the hdcp just like you're wanting to do. They put an * beside
each score used.

--
Biff
Microsoft Excel MVP


"Larry L" wrote in message
...
I have the formula for doing the average -- I think you may have given it
to
me before.

Its not that important to do the * thing. I just wanted to periodically
publish the average calculation sheet. Because the 20 weeks covers parts
of 2
years and the weekly score sheet only have scores for the current year,
they
don't get to see all of their scores that are used for their averages. In
fact the 1st sheet they will get this season will only have 1 score with
the
other 19 scores from 2007 and 2008 seasons.

I can give them the 20 score sheet without the * and they can figure it
out
themselves.


Thank you again - I really appreciate your help.

Larry

"T. Valko" wrote:

Wow!

You've stumped me on that one!

So far I'm coming up with zilch!

Is it absolutely necessary to identify those scores?

I'm a golfer so I know what you're doing. I can give you a single formula
that will average the lowest 10 out of the last 20 scores without having
to
do all these extra steps.

I can also give you a single formula that meets the USGA guidelines for
handicaps:

If there are <10 scores it will average whatever scores are available.
If there are =10 and <=20 scores it will average the lowest 10 scores.
If there are 20 scores it will average the lowest 10 scores out of the
last
20 scores.


--
Biff
Microsoft Excel MVP


"Larry L" wrote in message
...
You are correct it is for golf

The formula works -- thank you again.

If this is not too much trouble, I would like to try to tweak the last
formula.

Here is the issue -- When I consider the 10 best of 20 scores and then
apply
an * to those scores -- which I can now do thanks to you, I run into a
small
issue.

I could have 5 scores of 45 and 6 scores of 46 and all the other scores
higher. With the formula as it is now, I will have 11 scores with an *.
Ideally I would have 10 scores -- to 5 scores of 45 and the 5 most
recent
of
the 46's.

Is there any way to accomplish this?

"T. Valko" wrote:

So, you're working on golf scores, aren't you? <g

Assume the last 20 scores are in the range A49:T49
Assume the 10 lowest scores are in the range A50:J50

Try something like this:

=IF(COUNTIF($A50:$J50,A49),"*","")

--
Biff
Microsoft Excel MVP


"Larry L" wrote in message
...
I have 1 more question

Now have the 20 cells populated.

I then created another row with the 10 lowest from the 20 (figured
that
one
out my self!)

I now want to create an if statement to put an astrick under the 10
lowest
cells in the row of 20.

The formula I used was (this is for row 51)
=IF(C49=50:50,"*","") Note row 49 contains the formula you provided
to
select 20 and row 50 is the ten lowest of the 20

But this will only put an astrick under the cell that is equal to
the
one
above it. What I want is for an astrick for all cells in row 49 that
have
a
cell equal to one of the 10 cells in row 50



"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Larry L" wrote in message
...
This formula works - thank you

"T. Valko" wrote:

OK, assuming there will *always* be at least 20 entries in the
range...

I'm using row 1 in the example.

Array entered**

=INDEX(1:1,LARGE(IF(1:1<"",COLUMN(1:1)),20-(COLUMNS($A:A)-1)))

Copy across to a total of 20 cells.

** array formulas need to be entered using the key combination
of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key
and
the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Larry L" wrote in message
...
Assuming the original row has 20 cells with data
I would like the new data to be 1,2,...19,20

When an additional cell is added to the original row I want
the
new
data
to
be 2,3,....20,21

Ocassionally the original row may have a cell that does not
have
data,
in
that case
I want the new row to look at additional cells so that it will
always
have
all the non blank cells up to 20,

"T. Valko" wrote:

What Gary meant was this:

Assume this is your data:

1 2 3 4 5

Do you want the result to appear as:

1 2 3 4 5

Or:

5 4 3 2 1

--
Biff
Microsoft Excel MVP


"Larry L" wrote in message
...
The most recent will be last in the new row.

"Larry L" wrote:

I want the most recent retained -- so the formula needs to
consider
data
from
right to left in the row.

"Gary''s Student" wrote:

Do you want the order retained or reversed??

That is, most recent first or most recent last?
--
Gary''s Student - gsnu200827


"Larry L" wrote:

I would like assistance with a formula to accomplish
the
following:
From a row of data I would like to generate a second
row
that
contains 20
nonzero cells from the original row of data beginning
with
the
cell
working
from right to left. Note: I will be adding data to the
original
row
and would
like the formula to regenerate a new row considering
the
new
data
and
dropping off data that is more than 20 nonzero cells
to
the
right
of
the last
entry.