Array formula with INDEX, MODE, MATCH - clairification needed

[marc01]

Hi all,

I am using an array formula to show me the most frequently occuring event based on a set criteria. For context, I am looking at points in tennis and wanting to know if a player serves in a certain direction, are they more often or not in an attacking, neutral, or defensive position for their subsequent shot.

The array formula I am using is:

{=(INDEX(Table4[Serve+1],MODE(IF(Table4[Serve+1]<>"",IF(Table4[Serve+1]<>"N/A",IF(Table4[1st serve]=E8,IF(Table4[Week]=Report!B2,IF(Table4[Player]=Report!B3,IF(Table4[Side]="Deuce",(MATCH(Table4[Serve+1],Table4[Serve+1],{0,0})))))))))))}

The answer to the question is attacking = 1, neutral = 0, defensive = 0. With the above formula this is working.......

However, there are instances where there may be a tie between the values recorded e.g. attacking =1, neutral = 1, and defensive =0. When using {0,0} the formula will choose one of the two and display it. When I change {0,0} to 0 this return the error #N/A. For this instance by wrapping the formula in IFERROR i am able to state "there has been a tie"........BUT for some reason when I do this for the above formula where I know the answer should be attacking (as it has 1 occurence, and neutral and defensie have 0 occurrences) it returns an error value.

Does anyone know why this may be the case? Wondering if it something to do with there only being 1 occurence of a value as it appears to be working if the answer is attacking = 2, neutral = 0, and defensive = 0...

Many thanks in advance,

Marc

 

[jasonb75]

You can't have a modal value with a single occurrence, see if this alternative way works (thanks to Eric W for this method).

=(INDEX(Table4[Serve+1],MODE(IF(Table4[Serve+1]<>"",IF(Table4[Serve+1]<>"N/A",IF(Table4[1st serve]=E8,IF(Table4[Week]=Report!B2,IF(Table4[Player]=Report!B3,IF(Table4[Side]="Deuce",(MATCH(Table4[Serve+1],Table4[Serve+1],0)*{1,1}))))))))))

 

[marc01]

You can't have a modal value with a single occurrence, see if this alternative way works (thanks to Eric W for this method).

=(INDEX(Table4[Serve+1],MODE(IF(Table4[Serve+1]<>"",IF(Table4[Serve+1]<>"N/A",IF(Table4[1st serve]=E8,IF(Table4[Week]=Report!B2,IF(Table4[Player]=Report!B3,IF(Table4[Side]="Deuce",(MATCH(Table4[Serve+1],Table4[Serve+1],0)*{1,1}))))))))))

 

[marc01]

Great Jason thank you very much for this. I will give it a try. All the best. Marc