Find mode of dynamic array

[himperson1]

Hello. I'm looking for some help with the following:

I need to find the mode of an array that changes based on the day of the month and which shift im looking at.

Ex:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Day[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Shift[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Level[/TD]
[TD]Empty[/TD]
[TD]Min[/TD]
[TD]Min[/TD]
[TD]Max[/TD]
[TD]Min[/TD]
[TD]Max[/TD]
[TD]Empty[/TD]
[TD]Max[/TD]
[TD]Max[/TD]
[/TR]
</tbody>[/TABLE]


If I wanted to find the mode of 1st shift until the 3rd of the month, the output result is "Empty"
The array it is looking at would be {Empty, 0, 0, Max, 0, 0, Empty, 0, 0}

If I wanted to find the mode of 2nd shift until the 2nd of the month, the output result is "Min"
The array it is looking at would be {0, Min, 0, 0, Min, 0}



I have found the following code to potentially use, but I am having difficulties making it fit my spreadsheet.

= INDEX(rng,MODE(IF(rng<>"",MATCH(rng,rng,0))))

I use the following code elsewhere in my spreadsheet to find the sum of a similar array

=SUMPRODUCT(--(MOD(COLUMN(INDIRECT("'MS'!E"&MS!$A$35&":"&A$1&MS!$A$35))-COLUMN(INDIRECT("'MS'!E"&MS!$A$35))+1,3)=1),INDIRECT("'MS'!E"&MS!$A$35&":"&A$1&MS!$A$35))

Thank you

 

[Domenic]

Try...

A1:N3

[TABLE="width: 896"]
<tbody>[TR]
[TD="class: xl63, width: 64"]Day[/TD]
[TD="class: xl63, width: 64, align: right"]1[/TD]
[TD="class: xl63, width: 64, align: right"]1[/TD]
[TD="class: xl63, width: 64, align: right"]1[/TD]
[TD="class: xl63, width: 64, align: right"]2[/TD]
[TD="class: xl63, width: 64, align: right"]2[/TD]
[TD="class: xl63, width: 64, align: right"]2[/TD]
[TD="class: xl63, width: 64, align: right"]3[/TD]
[TD="class: xl63, width: 64, align: right"]3[/TD]
[TD="class: xl63, width: 64, align: right"]3[/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"]Shift[/TD]
[TD="class: xl63, width: 64"]Day[/TD]
[TD="class: xl63, width: 64"]Mode[/TD]
[/TR]
[TR]
[TD="class: xl63"]Shift[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63"]Empty[/TD]
[/TR]
[TR]
[TD="class: xl63"]Level[/TD]
[TD="class: xl63"]Empty[/TD]
[TD="class: xl63"]Min[/TD]
[TD="class: xl63"]Min[/TD]
[TD="class: xl63"]Max[/TD]
[TD="class: xl63"]Min[/TD]
[TD="class: xl63"]Max[/TD]
[TD="class: xl63"]Empty[/TD]
[TD="class: xl63"]Max[/TD]
[TD="class: xl63"]Max[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
</tbody>[/TABLE]

N2, confirmed with CONTROL+SHIFT+ENTER:

=INDEX(B3:J3,MODE(IF(B1:J1<=M2,IF(B2:J2=L2,IF(LEN(B3:J3)>0,MATCH(B3:J3,B3:J3,0))))))

Hope this helps!

 

[himperson1]

This is close!

The one detail that I've forgotten is that the shift is never actually picked.
I have separate columns for each display of data.

Ex: N2 data for shift 1 always appears in L3. N2 data for shift 2 always appears in M3. etc.

 

[Domenic]

Is this what you mean?

A1:N3

[TABLE="width: 896"]
<tbody>[TR]
[TD="class: xl63, width: 64"]Day[/TD]
[TD="class: xl63, width: 64, align: right"]1[/TD]
[TD="class: xl63, width: 64, align: right"]1[/TD]
[TD="class: xl63, width: 64, align: right"]1[/TD]
[TD="class: xl63, width: 64, align: right"]2[/TD]
[TD="class: xl63, width: 64, align: right"]2[/TD]
[TD="class: xl63, width: 64, align: right"]2[/TD]
[TD="class: xl63, width: 64, align: right"]3[/TD]
[TD="class: xl63, width: 64, align: right"]3[/TD]
[TD="class: xl63, width: 64, align: right"]3[/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"]Day[/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl63"]Shift[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]Level[/TD]
[TD="class: xl63"]Empty[/TD]
[TD="class: xl63"]Min[/TD]
[TD="class: xl63"]Min[/TD]
[TD="class: xl63"]Max[/TD]
[TD="class: xl63"]Min[/TD]
[TD="class: xl63"]Max[/TD]
[TD="class: xl63"]Empty[/TD]
[TD="class: xl63"]Max[/TD]
[TD="class: xl63"]Max[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"]Empty[/TD]
[TD="class: xl63"]Min[/TD]
[TD="class: xl63"]. . .[/TD]
[/TR]
</tbody>[/TABLE]

L3, confirmed with CONTROL+SHIFT+ENTER:

=INDEX($B3:$J3,MODE(IF($B$1:$J$1<=$L$2,IF($B$2:$J$2=1,IF(LEN($B3:$J3)>0,MATCH($B3:$J3,$B3:$J3,0))))))

M3, confirmed with CONTROL+SHIFT+ENTER:

=INDEX($B3:$J3,MODE(IF($B$1:$J$1<=$L$2,IF($B$2:$J$2=2,IF(LEN($B3:$J3)>0,MATCH($B3:$J3,$B3:$J3,0))))))

Alternatively, you can use a single formula...

L3, confirmed with CONTROL+SHIFT+ENTER, and copied across:

=INDEX($B3:$J3,MODE(IF($B$1:$J$1<=$L$2,IF($B$2:$J$2=COLUMNS($L3:L3),IF(LEN($B3:$J3)>0,MATCH($B3:$J3,$B3:$J3,0))))))

If this is not what you mean, please post a sample of the expected results based on the data you provided.