Array not changing cell number when dragged down

[jarhead58]

Hey everyone! I must be doing something wrong but for the life of me, I can't figure out what it is. I have cell M21 with =MODE(B2:F3500). Now in M22 I have =MODE(IF(COUNTIF($M21:M22,$B$2:$F$3500),"",$B$2:$F$3500)) to find the next largest number which I make an array via the CTRL-Shift-Alt Enter which brings up "0" into that cell. Am I doing this wrong or is there something I'm missing? TIA

 

[Fluff]

How about

=MODE(IF((COUNTIF($M$21:M21,$B$2:$F$3500))+($B$2:$F$3500=""),"",$B$2:$F$3500))

 

[jarhead58]

How about

=MODE(IF((COUNTIF($M$21:M21,$B$2:$F$3500))+($B$2:$F$3500=""),"",$B$2:$F$3500))

Nope, still 0.

 

[jarhead58]

Also verified that the range only contained numbers no formulas.

 

[Fluff]

Did you confirm it with Ctrl Shift enter?

 

[jarhead58]

Did you confirm it with Ctrl Shift enter?

Sure did.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[jarhead58]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Done, I could swear I did that when I first joined.

 

[Fluff]

Thanks for that, maybe it's a version thing as it works for me.

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2		649	180	814	74	361
3		599	545	661	983	14
4		679	341	790	135	255					8		6
5		787	844	198	899	539					7		401
6		82	6	512	578	629					5		839
7		10	544	282	48	75					5		825
8		34	865	634	6	765					5		123
9		401	567	656	583	289					4		814
10		291	399	240	450	382					3		464
11		997	655	537	227	895					3		150
12		151	839	552	733	825					3		923
13		752	401	379	454	33
14		6	123	757	473	264
15		930	883	26	962	6
16		201	194	991	994	59
17		772	123	386	817	858
18		814	369	517	417	278
19		620	401	123	188	464
20		150	827	425	458	839
21		855	150	465	656	872
22		527	709	285	641	215
23		489	595	630	20	463
24		567	36	730	870	33
25		627	814	123	296	825
Summary

Cell Formulas
Range	Formula
K4:K12	K4	=COUNTIF($B$2:$F$101,M4)
M4	M4	=MODE(B2:F3500)
M5:M12	M5	=MODE(IF((COUNTIF($M$4:M4,$B$2:$F$3500))+($B$2:$F$3500=""),"",$B$2:$F$3500))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[jarhead58]

Thanks for that, maybe it's a version thing as it works for me.

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2		649	180	814	74	361
3		599	545	661	983	14
4		679	341	790	135	255					8		6
5		787	844	198	899	539					7		401
6		82	6	512	578	629					5		839
7		10	544	282	48	75					5		825
8		34	865	634	6	765					5		123
9		401	567	656	583	289					4		814
10		291	399	240	450	382					3		464
11		997	655	537	227	895					3		150
12		151	839	552	733	825					3		923
13		752	401	379	454	33
14		6	123	757	473	264
15		930	883	26	962	6
16		201	194	991	994	59
17		772	123	386	817	858
18		814	369	517	417	278
19		620	401	123	188	464
20		150	827	425	458	839
21		855	150	465	656	872
22		527	709	285	641	215
23		489	595	630	20	463
24		567	36	730	870	33
25		627	814	123	296	825
Summary

Cell Formulas
Range	Formula
K4:K12	K4	=COUNTIF($B$2:$F$101,M4)
M4	M4	=MODE(B2:F3500)
M5:M12	M5	=MODE(IF((COUNTIF($M$4:M4,$B$2:$F$3500))+($B$2:$F$3500=""),"",$B$2:$F$3500))
Press CTRL+SHIFT+ENTER to enter array formulas.

Well that's weird! I changed my formula to reflect what your formula had and it didn't work. I then just copied my formula into another cell and then copied your formula into my cell and it worked. I verified both mine and yours via the paste of mine elsewhere and they were identical!!! Hmmm...oh well, it's working!!! Thank you Fluff!!