Finding the highest number and notate that in another column

[lllr]

I know it is easy finding the highest number but I want to find the highest number every 4 rows (there also maybe 2 or more). Once the student row has the highest mark, mark a 1 in the test winner column.

See below. 4 students (rows), 3 tests (columns) and the winners are marked in test winners (columns)

	RootScore Rating Results (1H2017)
Market	Test 1	Test 2	Test 3	Test 1 winner	Test 2 winner	Test 3 winner
Student A	4.5	4	5			1
Student B	5	1.5	4.5	1
Student C	2	1	4.5
Student D	5	5	5	1		1
Student A	4.5	4	3.5		1
Student B	5	0.5	3.5	1
Student C	2.5	1.5	1
Student D	5	2	4	1		1
Student A	5	4	5	1	1	1
Student B	3	0	4
Student C	4	3.5	4.5
Student D	5	3.5	5	1		1

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[Eric W]

Consider this:

	A	B	C	D	E	F	G
1		RootScore Rating Results (1H2017)
2	Market	Test 1	Test 2	Test 3	Test 1 winner	Test 2 winner	Test 3 winner
3	Student A	4.5	4	5			1
4	Student B	5	1.5	4.5	1
5	Student C	2	1	4.5
6	Student D	5	5	5	1	1	1
7	Student A	4.5	4	3.5		1
8	Student B	5	0.5	3.5	1
9	Student C	2.5	1.5	1
10	Student D	5	2	4	1		1
11	Student A	5	4	5	1	1	1
12	Student B	3	0	4
13	Student C	4	3.5	4.5
14	Student D	5	3.5	5	1		1
15

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula E3 =IF(B3=MAX(OFFSET(B$3:B$6,INT((ROWS(E$3:E3)-1)/4)*4,0)),1,"") <tbody> </tbody>

<tbody>
</tbody>

Put the formula in E3, then drag down and to the right as needed.

 

[johnmpl]

Hi, to all!

You can use this option too: (drag to down and right):

Book1
	A	B	C	D	E	F	G
1		RootScore Rating Results (1H2017)
2	Market	Test 1	Test 2	Test 3	Test 1 winner	Test 2 winner	Test 3 winner
3	Student A	4.5	4	5			1
4	Student B	5	1.5	4.5	1
5	Student C	2	1	4.5
6	Student D	5	5	5	1	1	1
7	Student A	4.5	4	3.5		1
8	Student B	5	0.5	3.5	1
9	Student C	2.5	1.5	1
10	Student D	5	2	4	1		1
11	Student A	5	4	5	1	1	1
12	Student B	3	0	4
13	Student C	4	3.5	4.5
14	Student D	5	3.5	5	1		1
Hoja1

Cell Formulas
Range	Formula
E3	=IF(B3=MAX(OFFSET(B3,-MOD(ROWS(E$3:E3)-1,4),,4)),1,"")

And with no volatile functions:

Book1
	A	B	C	D	E	F	G
1		RootScore Rating Results (1H2017)
2	Market	Test 1	Test 2	Test 3	Test 1 winner	Test 2 winner	Test 3 winner
3	Student A	4.5	4	5			1
4	Student B	5	1.5	4.5	1
5	Student C	2	1	4.5
6	Student D	5	5	5	1	1	1
7	Student A	4.5	4	3.5		1
8	Student B	5	0.5	3.5	1
9	Student C	2.5	1.5	1
10	Student D	5	2	4	1		1
11	Student A	5	4	5	1	1	1
12	Student B	3	0	4
13	Student C	4	3.5	4.5
14	Student D	5	3.5	5	1		1
Hoja1 (2)

Cell Formulas
Range	Formula
E3	=IF(B3=MAX(INDEX(B$3:B$14,N(IF(1,4*INT((ROWS(E$3:E3)-1)/4)+{1;2;3;4})))),1,"")

Blessings!

 

[lllr]

Works brilliantly. Thank you.

 

[lllr]

Sorry one more question. What if we added an overall that indicates which student with the most test wins? It is then counting rows and columns. I assume this is just as easy to calculate the Overall column right?

	RootScore Rating Results (1H2017)
Market	Test 1	Test 2	Test 3	Test 1 winner	Test 2 winner	Test 3 winner	Overall
Student A	4.5	4	5			1
Student B	5	1.5	4.5	1
Student C	2	1	4.5
Student D	5	5	5	1		1	X
Student A	4.5	4	3.5		1
Student B	5	0.5	3.5	1	1		X
Student C	2.5	1.5	1
Student D	5	2	4	1		1	X
Student A	5	4	5	1	1	1	X
Student B	3	0	4
Student C	4	3.5	4.5
Student D	5	3.5	5	1		1

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[Eric W]

If I understand you correctly, then this should work:

	A	B	C	D	E	F	G	H	I
1		RootScore Rating Results (1H2017)
2	Market	Test 1	Test 2	Test 3	Test 1 winner	Test 2 winner	Test 3 winner	# of wins	Overall
3	Student A	4.5	4	5			1	1
4	Student B	5	1.5	4.5	1			1
5	Student C	2	1	4.5				0
6	Student D	5	5	5	1	1	1	3	X
7	Student A	4.5	4	3.5		1		1
8	Student B	5	0.5	3.5	1			1
9	Student C	2.5	1.5	1				0
10	Student D	5	2	4	1		1	2	X
11	Student A	5	4	5	1	1	1	3	X
12	Student B	3	0	4				0
13	Student C	4	3.5	4.5				0
14	Student D	5	3.5	5	1		1	2

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula E3 =IF(B3=MAX(OFFSET(B$3:B$6,INT((ROWS(E$3:E3)-1)/4)*4,0)),1,"") H3 =SUM(E3:G3) I3 =IF(H3=MAX(OFFSET(H$3:H$6,INT((ROWS(H$3:H3)-1)/4)*4,0)),"X","") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

The E3 formula, which is copied to E3:G14, is the same as before. The H3 formula just sums up the wins from columns E:G. Copy it down to H14. Then the I3 formula is just a variation of the E3 formula which looks for the max value in the current set of 4 rows. Copy it to I14.

 

[johnmpl]

Hi again!

With Volatile functions:

	A	B	C	D	E	F	G	H
1		RootScore Rating Results (1H2017)
2	Market	Test 1	Test 2	Test 3	Test 1 winner	Test 2 winner	Test 3 winner	Overall
3	Student A	4.5	4	5			1
4	Student B	5	1.5	4.5	1
5	Student C	2	1	4.5
6	Student D	5	5	5	1	1	1	X
7	Student A	4.5	4	3.5		1
8	Student B	5	0.5	3.5	1
9	Student C	2.5	1.5	1
10	Student D	5	2	4	1		1	X
11	Student A	5	4	5	1	1	1	X
12	Student B	3	0	4
13	Student C	4	3.5	4.5
14	Student D	5	3.5	5	1		1

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Hoja1​

Worksheet Formulas Cell Formula E3 =IF(B3=MAX(OFFSET(B3,-MOD(ROWS(E$3:E3)-1,4),,4)),1,"") H3 =IF(SUM(E3:G3)=MAX(MMULT(N(+OFFSET(E3,-MOD(ROWS(H$3:H3)-1,4),,4,3)),{1;1;1})),"X","") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Without Volatile functions:

	A	B	C	D	E	F	G	H
1		RootScore Rating Results (1H2017)
2	Market	Test 1	Test 2	Test 3	Test 1 winner	Test 2 winner	Test 3 winner	Overall
3	Student A	4.5	4	5			1
4	Student B	5	1.5	4.5	1
5	Student C	2	1	4.5
6	Student D	5	5	5	1	1	1	X
7	Student A	4.5	4	3.5		1
8	Student B	5	0.5	3.5	1
9	Student C	2.5	1.5	1
10	Student D	5	2	4	1		1	X
11	Student A	5	4	5	1	1	1	X
12	Student B	3	0	4
13	Student C	4	3.5	4.5
14	Student D	5	3.5	5	1		1

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Hoja2​

Worksheet Formulas Cell Formula E3 =IF(B3=MAX(INDEX(B$3:B$14,N(IF(1,4*INT((ROWS(E$3:E3)-1)/4)+{1;2;3;4})))),1,"") H3 =IF(SUM(E3:G3)=MAX(MMULT(N(INDEX(E$3:G$14,N(IF(1,4*INT((ROWS(H$3:H3)-1)/4)+{1;2;3;4})),N(IF(1,{1,2,3})))),{1;1;1})),"X","") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Blessings!