is it possible to get the mode from a column of text ignoring blank cells and where the year equals x, i.e.:
<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
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