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:

Day	1	1	1	2	2	2	3	3	3
Shift	1	2	3	1	2	3	1	2	3
Level	Empty	Min	Min	Max	Min	Max	Empty	Max	Max

<tbody>
</tbody>

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

Day	1	1	1	2	2	2	3	3	3		Shift	Day	Mode
Shift	1	2	3	1	2	3	1	2	3		1	3	Empty
Level	Empty	Min	Min	Max	Min	Max	Empty	Max	Max

<tbody>
</tbody>

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

Day	1	1	1	2	2	2	3	3	3		Day
Shift	1	2	3	1	2	3	1	2	3		3
Level	Empty	Min	Min	Max	Min	Max	Empty	Max	Max		Empty	Min	. . .

<tbody>
</tbody>

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.