Return MODE of first digits only, from an array of numbers

[Legacy 287389]

Hi good people,

please help me with a formula that will return the MODE of only the first digits of an array of numbers, for example,
if I have in row 1, columns A-E for example:

12345, 32647, 36143, 41345, 53261,

I would like "3" returned. Thank you kindly...

 

[Scott T]

Try

Excel 2010
	A	B	C	D	E
1	12345	32647	36143	41345	53261
2
3	3
Sheet4

Cell Formulas
Range	Formula
A3	{=MODE(--LEFT(A1:E1,1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Legacy 287389]

Thank you Scott T,

I do appreciate!!!..have a great day!!

 

[Legacy 287389]

Try
Excel 2010

	A	B	C	D	E
1	12345	32647	36143	41345	53261
2
3	3

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4​

Array Formulas Cell Formula A3 {=MODE(--LEFT(A1:E1,1))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

hi Scott T,

actually, I thanked you before I tried it...your formula returnes the correct value, however, a mistake on my part, I forgot to mention that some rows have a lot of blank cells...for those rows the formula returnes a VALUE error..can it be changed to ignore blank cells maybe?

 

[Legacy 287389]

Hi Scott T,

no worries thank you, i have managed the little problem. Also an array formula...

=MODE(IF(BD131:BJ131<>"",--LEFT(BD131:BJ131,1)))

 

[Scott T]

Try

If no number is repeated mode returns the N/A error. This will return blank.

Excel 2010
	A	B	C	D	E	F	G
1	12345	32647	36143	41345	53261		3
2	12345	42647	36143	41345	53261		4
3	1237	7789	97443	97	76		7
4	1237	7789	97443		76		7
5	12345	22647	36143	41235	9999
Sheet4

Cell Formulas
Range	Formula
G1	{=IFERROR(MODE(IFERROR(--LEFT(A1:E1,1),"0")),"")}
G2	{=IFERROR(MODE(IFERROR(--LEFT(A2:E2,1),"0")),"")}
G3	{=IFERROR(MODE(IFERROR(--LEFT(A3:E3,1),"0")),"")}
G4	{=IFERROR(MODE(IFERROR(--LEFT(A4:E4,1),"0")),"")}
G5	{=IFERROR(MODE(IFERROR(--LEFT(A5:E5,1),"0")),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Legacy 287389]

Thanx again Scott T, I do appreciate!..have a cool weekend!