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.:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Year[/TD]
[TD]Grade[/TD]
[/TR]
[TR]
[TD]Year 10[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]Year 10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Year 10[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]Year 10[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Year 10[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Year 11[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]Year 11[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Year 11[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Year 11[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Year 11[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Year 11[/TD]
[TD]B[/TD]
[/TR]
</tbody>[/TABLE]

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...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Year​

[/td][td]

Grade​

[/td][td][/td][td]

Year​

[/td][td]

Mode​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Year 10​

[/td][td]

A​

[/td][td][/td][td]

Year 10​

[/td][td]

A​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Year 10​

[/td][td][/td][td][/td][td]

Year 11​

[/td][td]

B​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Year 10​

[/td][td]

A​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Year 10​

[/td][td]

B​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

Year 10​

[/td][td]

C​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

Year 11​

[/td][td]

A​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

Year 11​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

Year 11​

[/td][td]

B​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

Year 11​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td]

Year 11​

[/td][td]

C​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td]

Year 11​

[/td][td]

B​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

13​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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!