Pls help! Expanding range based on a cell value!

[adurham22926192]

I am trying to create a dance scoring software. I have a 3 sheets: Registration, Entry and Adjudicator 1.

Sheet 1 (Registration)

• In Column A, we have the competitors number (Michael’s number is 101, Joanne’s is 102, etc)
• In Colmn B, we have their name
• In Column C, we have the school that they dance for.

	A	B	C
1	Number	Name	School
2	101	Michael Murphy	Elite Dance
3	102	Joanne Brown	Sheer Talent
4	103	Tom Cooke	Reegan School of Dance
5	104	Layla McGuinness	Sheer Talent

Sheet 2 (Entry)

• Here we have the same layout as Sheet 1 but it’s a little different.
• there are 4 competitors entered and their numbers are still in column A

	A	B	C	D
1	Number	Name	School	Total Number of Competitors
2	101	Michael Murphy	Elite Dance	=COUNTA(A2:A200)
3	102	Joanne Brown	Sheer Talent
4	103	Tom Cooke	Reegan School of Dance
5	104	Layla McGuinness	Sheer Talent
6
7
8
9

Sheet 3 (Adjudicator 1)

• On this sheet, I want there To be a formula in D2 that gets changed due to the number of competitors entered.
• For example, I have 4 competitors entered on Sheet 2 so the formula range would be from row 2 to row 5 but if I added an extra two competitors on Sheet 2, I want the formula to change so that the range would then be from row 2 to row 7.
• The formula I want to change is this: =SUMPRODUCT((C2<=$C$2:$C$5)/COUNTIF($C$2:$C$5,$C$2:$C$5))
• The reason why in the formula it is C2:C5 is because there are 4 competitors. When I enter the two extra competitors on the Entry sheet, their number will automatically appear under the other peoples numbers (in this case in cell A6 and A7) and I want the formula that is in D2:D7 expand The range to C2:C7 to include the two extra competitors score in the rank.

	A	B	C	D
1	Number	Score	Converted score	Rank
2	101	85.55	80.00
3	102	92.85	100.00
4	103	84.45	70.00
5	104	91.35	90.00
6	105	73.55	50.00
7	106	83.15	60.00

Hope this makes sense! All of your help would be appreciated!

 

[Toadstool]

Hi ADurham22926192,

Try

=SUMPRODUCT((C2<=OFFSET($C$1,1,,COUNTA($C$2:$C$999)))/COUNTIF(OFFSET($C$1,1,,COUNTA($C$2:$C$999)),OFFSET($C$1,1,,COUNTA($C$2:$C$999))))

 

[adurham22926192]

Hi ADurham22926192,

Try

=SUMPRODUCT((C2<=OFFSET($C$1,1,,COUNTA($C$2:$C$999)))/COUNTIF(OFFSET($C$1,1,,COUNTA($C$2:$C$999)),OFFSET($C$1,1,,COUNTA($C$2:$C$999))))

Thank you! This worked! I’ve another problem:

In Column A, we have the competitors number
In Column B, we have their total points

In Column C and D, i want it to list from highest to lowest who had the most points and to put the matching competitor number in column C

Example: in C2, it should be 102 and in D2 it should be 100 because that competitor is the competitor with the highest score.
Then in C3 and D3, I want it to show the second highest competitor and their score and so on.

The formula I have in C2 is =IF(D5=””,””,INDEX(A:A,AGGREGATE(15,6,ROW(A$2:A$7)/(B$2:B$7=D2),COUNTIF(D$2:D2,D2))))
The formula I have in D2 is =LARGE($B$2:$B$7,ROWS(E$2:E2))
The formula I have in E2 is =SUMPRODUCT((D2<=$D$2:$D$7)/COUNTIF($D$2:$D$7,$D$2:$D$7))

I want all of these formula ranges to expand like my original post so that if more people enter the competition, it will still be able to calculate it. Hope this makes sense!

	A	B	C	D	E
1	Number	Total	Number	Total	Rank
2	101	80
3	102	100
4	103	70
5	104	90
6	105	50
7	106	60
8

 

[Toadstool]

I think your C2 example should start IF(D2= ?

I'm not reverse engineering your formulae to check they are correct, just trying to answer your range question, but I believe columns C and D work OK with just a very high number for the top range. For column E try:

=SUMPRODUCT((D2<=OFFSET($D$1,1,,COUNTA($D$2:$D$9999)))/COUNTIF(OFFSET($D$1,1,,COUNTA($D$2:$D$9999)),OFFSET($D$1,1,,COUNTA($D$2:$D$9999))))

adurham22921692.xlsx
	A	B	C	D	E
1	Number	Total	Number	Total	Rank
2	101	80	102	100	1
3	102	100	104	90	2
4	103	70	101	80	3
5	104	90	103	70	4
6	105	50	106	60	5
7	106	60	105	50	6
Sheet1 (2)

Cell Formulas
Range	Formula
C2:C7	C2	=IF(D2="","",INDEX(A:A,AGGREGATE(15,6,ROW(A$2:A$9999)/(B$2:B$9999=D2),COUNTIF(D$2:D2,D2))))
D2:D7	D2	=LARGE($B$2:$B$9999,ROWS(E$2:E2))
E2:E7	E2	=SUMPRODUCT((D2<=OFFSET($D$1,1,,COUNTA($D$2:$D$9999)))/COUNTIF(OFFSET($D$1,1,,COUNTA($D$2:$D$9999)),OFFSET($D$1,1,,COUNTA($D$2:$D$9999))))