Trouble Ranking in One Column Using Different Criteria

[Labamba32]

Hi All,

Looking for a little help and perhaps creativity. I built a pretty extensive spreadsheet to run my football pool. I made a template pick sheet so when players send me their weekly picks I just copy and paste them into a "raw data" tab (this is literally a tab of nothing but everyone's picks for the entire season). From this I can then enter in the game winners on another tab and through a series of formulas on a third tab I'm able to calculate tie breaks etc to come up with our weekly winners. I display the weekly winners in an easy to read weekly format but for tracking purposes I also have a tab that keeps historical results. I've designed the historical results tab like this:[TABLE="class: grid, width: 800, align: center"]
<tbody>[TR]
[TD]Player[/TD]
[TD]Season[/TD]
[TD]Week #[/TD]
[TD]Weekly Total Score[/TD]
[TD]Rank[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]2015[/TD]
[TD]1[/TD]
[TD]10[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]2015[/TD]
[TD]1[/TD]
[TD]12[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]2016[/TD]
[TD]1[/TD]
[TD]9[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]2016[/TD]
[TD]1[/TD]
[TD]15[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

The problem I'm having is after the first season the rank function is ranking all weekly total scores for all seasons (i.e rows 1 - 4) vs just the individual seasons (i.e. 1-2 and 3-4). I realize the easy way to solve this would be to have the rank function applied only to the rows for which that season is contained vs the column as a whole however since we have players come and go that can be a bit difficult to predict. Another solution I thought of was to have multiple rank columns that would only rank if the season matched (i.e. the header would be 2015 and if column B was 2015 it would only rank those rows). However the problem with that is that as we get further into the future i'm always having to add columns. I was hoping to find a more all inclusive solution in one column but am stumped thus far. Any ideas would be greatly appreciated.

Some other info: I don't know much about VBA but am willing to learn. Ideally I'd like to take this to an online set up where people could log in and make their picks on their own with out having me send a pick sheet template and them send it back in Excel (even better would be if I could have the picks locked and displayed as games kicked off as well as results automatically recorded as games completed). Then if I'm totally dreaming a linked app for mobile view would be amazing, LOL. I realize there are sites that do this for a small fee or even for free but we have a few unique things about our pool (multiple competitions at the same time) that I'm able to build into one Excel file which I haven't seen any of the sites do yet. So I'm thinking it would have to be a custom site (which I'm totally willing to learn just need some guidance). Gotta crawl before I can walk though so any solution even if its still in the excel file is sufficient.

Thanks in advance.

 

[jorismoerings]

Hi,

Convert your RAnk formula to this and it calculates the rank per season.
Hope this helps:

Excel 2016 (Windows) 64 bit
	A	B	C	D	E	F
1	Player	Season	Week #	Weekly Total Score	Rank
2	A	2015	1	10	2
3	B	2015	1	12	1
4	A	2016	1	9	2
5	B	2016	1	15	1
Sheet1

Cell Formulas
Range	Formula
E2	=1+SUMPRODUCT(($B$2:$B$5=B2)*($D$2:$D$5>D2))

 

[Labamba32]

Hi thanks of for this formula - this works if the only criteria for ranking was the season. However once I added a new row with player C for 2016 season in week # 2 it ranked that row in with the other 2016 rows. However this should be separate since its for week 2 not week 1. So to clarify each week of each season needs to be ranked as a set not just the seasons as a whole. Any suggestions? For clarity this is what I'm getting now. Player C should be ranked 1 since they are the only one in 2016 week 2:

[TABLE="class: grid, width: 320"]
<colgroup><col width="64" style="width:48pt" span="5"> </colgroup><tbody>[TR]
[TD="width: 64"]Player[/TD]
[TD="width: 64"]Season[/TD]
[TD="width: 64"]Week #[/TD]
[TD="width: 64"]Weekly Total[/TD]
[TD="width: 64"]Rank[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]

 

[jorismoerings]

Hi,

Just add the week criteria to the formula

=1+SUMPRODUCT(($B$2:$B$6=B2)*($C$2:$C$6=C2)*($D$2:$D$5>D2))

 

[Labamba32]

This worked great, now the only issue is its taking forever to calculate. I know that i can turn calculations to manual but is there anything else I can do? I send the sheet to others who aren't excel savvy and they'd have no idea how to manually turn on the calcs. Currently my formula is as follows:

=SUMPRODUCT(($B:$B=$B3)*($C:$C=$C3)*($U:$U>$U3))+1

Its calculating over 1200 rows now and I anticipate adding 600ish rows each year.

 

[Marcelo Branco]

Try

=COUNTIFS($B:$B,$B3,$C:$C,$C3,$U:$U,">"&$U3)+1

M.

 

[Labamba32]

Try

=COUNTIFS($B:$B,$B3,$C:$C,$C3,$U:$U,">"&$U3)+1

M.

This works much better thanks - I feel like I should have been able to think of that on my own, LOL.

 

[Marcelo Branco]

You are welcome. Glad to help

M.

 

[jorismoerings]

Hi,
All formulas with usage off array type functions like sumproduct or countif will always be draining on calculation time when used with full columns as criteria.
Regardless the usage of Marcelo's or my solution, you should consider to limit the ranges used as they drain unnecessary calculation time.

 

[Marcelo Branco]

Hi,
All formulas with usage off array type functions like sumproduct or countif will always be draining on calculation time when used with full columns as criteria.
Regardless the usage of Marcelo's or my solution, you should consider to limit the ranges used as they drain unnecessary calculation time.

You're only partly right.

One should never use references to entire columns in SUMPRODUCT because this function will do the comparisons across all rows (1048576 rows in Excel 2007 or higher).

On the other hand, in the case of COUNTIF(s), AVERAGEIF(s) or SUMIF(s) references to entire columns do not cause detriment in performance. Such functions are smart enough to handle only rows that contain data. That's why Labamba32 observed a great improvement in performance using COUNTIFS instead of SUMPRODUCT.

M.