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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]12345[/TD]
[TD="align: right"]32647[/TD]
[TD="align: right"]36143[/TD]
[TD="align: right"]41345[/TD]
[TD="align: right"]53261[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A3[/TH]
[TD="align: left"]{=MODE(--LEFT(A1:E1,1))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

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!