Mode for Text with criteria

[Fazila]

is it possible to get the mode from a column of text ignoring blank cells and where the year equals x, i.e.:

Year	Grade
Year 10	A
Year 10
Year 10	A
Year 10	B
Year 10	C
Year 11	A
Year 11
Year 11	B
Year 11
Year 11	C
Year 11	B

<tbody>
</tbody>

Mode for Year 10 should be A
Mode for Year 11 should be B

This formula works, however, I can't add the Year criteria to it:

=INDEX('2013-14'!$R$2:$R$301,MODE(IF('2013-14'!$R$2:$R$301<>"",MATCH('2013-14'!$R$2:$R$301,'2013-14'!$R$2:$R$301,0))))

Thanks

 

[Eric W]

Try:

=INDEX('2013-14'!$R$2:$R$301,MODE(IF('2013-14'!$Q$2:$Q$301=D2,IF('2013-14'!$R$2:$R$301<>"",MATCH('2013-14'!$R$2:$R$301,'2013-14'!$R$2:$R$301,0)))))

with Control+Shift+Enter, where the year criteria is in D2, and the years are in column Q.

 

[Marcelo Branco]

Maybe something like this...

	A​	B​	C​	D​	E​
1​	Year​	Grade​		Year​	Mode​
2​	Year 10​	A​		Year 10​	A​
3​	Year 10​			Year 11​	B​
4​	Year 10​	A​
5​	Year 10​	B​
6​	Year 10​	C​
7​	Year 11​	A​
8​	Year 11​
9​	Year 11​	B​
10​	Year 11​
11​	Year 11​	C​
12​	Year 11​	B​
13​

Array formula in E2 copied down
=INDEX(B$2:B$12,MODE(IF(A$2:A$12=D2,IF(B$2:B$12<>"",MATCH(B$2:B$12,B$2:B$12,0)))))
Ctrl+Shift+Enter

M.

 

[Canapone]

Hi

more or less same formula. In the example in Q2:Q301 the year list. Array status.

=INDEX($R$2:$R$301,MODE(IF($R$2:$R$301<>"",IF(Q2:Q301="Year 10",MATCH($R$2:$R$301,$R$2:$R$301,0)))))


Edit: sorry: I was writing very slowly. I did not mean to overlap. Regards

 

[Fazila]

Thank you all it worked beautifully

 

[Eric W]

Happy we could help!