Solved Excel Tracking movement in Ranking from Month to Month

January 8, 2013 at 10:26:35
Specs: Windows XP

Hi, I have a spreadsheet that tracks Hits and Player rankings based on the Hits. Each month is contained on a different worksheet.

I need a formula that tells me how many places up and down in the rankings a player fell or increased each month.

Is this even possible?


See More: Excel Tracking movement in Ranking from Month to Month

Report •


✔ Best Answer
January 9, 2013 at 12:21:46

There are multiple ways to reference a cell to the left of a value.

One method is to use OFFSET and MATCH:

=OFFSET(Sheet1!$B$2,MATCH(Sheet2!B2,Sheet1!$B$2:$B$10,0)-1,-1)

This formula will MATCH the value from Sheet2!B2 in the range SHEET1!$B$2:$B$10 and return position of the player number in that list. It will then use that number as the rows argument (after we subtract 1 from it) for the OFFSET function. -1 as the cols argument will reference the column to the left.

Another method is SUMIF:

=SUMIF(Sheet1!$B$2:$B$10, Sheet2!B2, Sheet1!$A$2:$A$10)

Since there is only one occurrence of each player number in the range, the SUMIF will only return a single rank value for each player.

You could subtract 2 SUMIF's to get the change in Rank directly.

=SUMIF(Sheet1!$B$2:$B$10,Sheet2!B2,Sheet1!$A$2:$A$10) -
SUMIF(Sheet2!$B$2:$B$10,Sheet2!B2,Sheet2!$A$2:$A$10)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
January 8, 2013 at 11:08:25

Hi RE1972

Yes, it is possible but we would need to know a lot more about how you set up your worksheet columns and rows and what the worksheet names are and what and how you want to get the rankings.

A sample of your data would also be useful. Read this to find out how to post data.


Report •

#2
January 8, 2013 at 11:32:25


Thank you for your help with this!

This is the first Sheet

Rank	Player #	Hits	Places Up or Down
1	42	        32	-
8	33	        8	-
5	26	        20	-
6	24	        18	-
9	19	        6	-
3	15	        28	-
7	10	        17	-
4	4	        25	-
2	3	        30	-

This is the 2nd Sheet

Rank	Player#	Hits	Places Up or Down
3	42	35	
10	33	10	
4	26	27	
9	24	13	
8	19	15	
7	15	20	
6	10	25	
5	4	26	
2	3	37	
1	4	55	

I would like to know how many places up and down the players ranking moves from month to month. An example is the Places up and Down column would have +1 or -1 or -2 etc.

Also new players would be added to sheets as they join the team.


Report •

#3
January 8, 2013 at 11:42:04

In addition to what AlteK has suggested about providing some sample data, if you aren't already storing the monthly rank values, you are going to need to.

In other words, if you are using a single cell to determine a player's rank for each month, and the new data overwrites the old, there will be no way to determine the change in rank on a month by month basis.

Therefore, you are going to have to store some "old" data so that calculations can be done using the old values and the new. If you are only interested in the change in rank from the previous month to the current, then you'll only need those 2 months of rankings. If you are looking to determine trends or record a player's change in rank over some specified number of months, then you'll need to retain as many months worth of data as you want to track.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
January 8, 2013 at 13:06:16

Why does player #4 appear twice on Sheet 2?

Assuming that's a typo, and assuming that your rankings and player numbers are in A2:B10 of each sheet, why not just use this in Row 2 of your "Places Up/Down" column and drag it down?

=Sheet1!A2-A2

As long as your player numbers are always in the same order, the ranking for that player will always be in the same order.

Am I missing something?

BTW...giving us the Row numbers and Column letters as shown in the instruction example means we don't have to make assumptions as to where your data resides.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
January 9, 2013 at 11:11:07

Hi, Thanks for everyone's help with this.

My example's were not very good. The more I thought about it what I really need to know is how can I reference the cell to the left of a specific value?

My first work sheet is named April and my second work sheet is named May.

The columns will be RANK | PLAYER# | HITS | PREVIOUS RANK |

The players have to be sorted by Hits every month.

An example of a senario is in April #42 had the most hits and is ranked 1. In May #42 has the fourth most hits so he is ranked 4. On the May work sheet I need to be able to show #42's previous ranking of 1 in the PREVIOUS RANK column. The problem I am having is #42 will not be in the same cell every month but I need to be able to reference the cell to the left of #42.

The Player (in this case #42 in cell B2 in the April Worksheet) needs to be tied to the cell to the left (A2 in the April Worksheet)

In the May worksheet player #42 is in B5 and his Rank is in A5


Thanks


Report •

#6
January 9, 2013 at 12:21:46
✔ Best Answer

There are multiple ways to reference a cell to the left of a value.

One method is to use OFFSET and MATCH:

=OFFSET(Sheet1!$B$2,MATCH(Sheet2!B2,Sheet1!$B$2:$B$10,0)-1,-1)

This formula will MATCH the value from Sheet2!B2 in the range SHEET1!$B$2:$B$10 and return position of the player number in that list. It will then use that number as the rows argument (after we subtract 1 from it) for the OFFSET function. -1 as the cols argument will reference the column to the left.

Another method is SUMIF:

=SUMIF(Sheet1!$B$2:$B$10, Sheet2!B2, Sheet1!$A$2:$A$10)

Since there is only one occurrence of each player number in the range, the SUMIF will only return a single rank value for each player.

You could subtract 2 SUMIF's to get the change in Rank directly.

=SUMIF(Sheet1!$B$2:$B$10,Sheet2!B2,Sheet1!$A$2:$A$10) -
SUMIF(Sheet2!$B$2:$B$10,Sheet2!B2,Sheet2!$A$2:$A$10)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
January 9, 2013 at 13:26:59

That's it!! Perfect! Thank you very much for all the help!

Report •

#8
January 9, 2013 at 14:12:12

I'm glad that DerbyDad has solved your issue. I just wanted to throw something in for your consideration.

In designing your ranking application I'm wondering if separate tabs for each month will add lots of additional work for you in terms of getting a full season view as well as any charting you may do. It is likely that as the season progresses you'll want a consolidation of all the months in a new tab. If this is likely then why not start from a consolidation view and have a dynamic view for the current month.

It might look something like this ....

Ranking

In this approach you would enter the current month "Hits" in the order shown and everything else will calculate automatically. The Current Month stats would give you your information sorted by Rank.

It is more than likely you are too advanced on your current course to change it now or there may be factors of which I am unaware that makes your design the most efficient but, as I said, this for consideration only and may assist your spreadsheet design on your next project..


Report •

#9
January 10, 2013 at 12:53:34

Altek, Thank you very much for the tip! I really appreciate you and DerbyDads help with this project!

Report •


Ask Question