Excel formula / Macro ?
 

I operate a football pool for the season (approx 140 entries) Briefly, here
is what it looks like;

Current Name Total points Week One WeekTwo Week Three ect.

1 Joe 35 11 13
11
2 Sam 34 12 12
10
3 Bob 33 12 12
9

What I would like to add is a Column for their standing the previous week,
in this case Week Two. It would hopefully look like this;

Current Previous Name Total Points Week One Week Two
Week Three
1 6 Joe 35 11
13 11
2 4 Sam 34 12
12 10

There will be approx 140 rows and I sort by Total Points

Is this a formula or a macro ?

Thanks

Bob

Excel formula / Macro ?
 

Hi Robert,
It is easiest if you first add a column for the total to last week, so in
your example Week 1 + Week 2 and then extend this sum each week.

Then you can use the RANK function, so if last week's total is H1:H140, use
RANK(H1, H$1:H$140) and that should put 1 in the highest value, 2 in the
next highest etc. If my understanding of your problem is correct, that
should be the same as their positions in the previous week.

Regards
Roger

"robert morris" wrote:

I operate a football pool for the season (approx 140 entries) Briefly, here
is what it looks like;

Current Name Total points Week One WeekTwo Week Three ect.

1 Joe 35 11 13
11
2 Sam 34 12 12
10
3 Bob 33 12 12
9

What I would like to add is a Column for their standing the previous week,
in this case Week Two. It would hopefully look like this;

Current Previous Name Total Points Week One Week Two
Week Three
1 6 Joe 35 11
13 11
2 4 Sam 34 12
12 10

There will be approx 140 rows and I sort by Total Points

Is this a formula or a macro ?

Thanks

Bob

Excel formula / Macro ?
 

Roger,

Sorry about my delay in replying, out on the town.

Your idea works perfectly. I inserted a column for the previous week and
can hide it from view. As always, I was probably trying to make it more
complicated than it needed.

Thanks so much,

Bob

"Roger Nye" wrote:

Hi Robert,
It is easiest if you first add a column for the total to last week, so in
your example Week 1 + Week 2 and then extend this sum each week.

Then you can use the RANK function, so if last week's total is H1:H140, use
RANK(H1, H$1:H$140) and that should put 1 in the highest value, 2 in the
next highest etc. If my understanding of your problem is correct, that
should be the same as their positions in the previous week.

Regards
Roger

"robert morris" wrote:

I operate a football pool for the season (approx 140 entries) Briefly, here
is what it looks like;

Current Name Total points Week One WeekTwo Week Three ect.

1 Joe 35 11 13
11
2 Sam 34 12 12
10
3 Bob 33 12 12
9

What I would like to add is a Column for their standing the previous week,
in this case Week Two. It would hopefully look like this;

Current Previous Name Total Points Week One Week Two
Week Three
1 6 Joe 35 11
13 11
2 4 Sam 34 12
12 10

There will be approx 140 rows and I sort by Total Points

Is this a formula or a macro ?

Thanks

Bob