Finding most common number in a range

[nirvehex]

Hi all - I'm looking to create a formula that will return the most common number in a range. I can't just use the mode function as my list contains decimal numbers and I want to return the most single digit number in the range. In the example table below, the most common number would be 6 as it appears 4 times throughout. Any ideas on a formula that would work for this? Thank you!!

3.67​

3.26​

6.16​

 

[Peter_SSs]

Like this then?

24 02 25.xlsm
	A	B	C	D	E
1	3.67	6		3.2	3
2	3.26			2.3	2
3	6.16			3.2
4
Mode

Cell Formulas
Range	Formula
B1,E1:E2	B1	=LET(c,SUBSTITUTE(CONCAT(A1:A3),".",""),MODE.MULT(--MID(c,SEQUENCE(LEN(c)),1)))

 

[nirvehex]

Thanks Peter, appreciate your help! It worked for a small range, but when I try to adjust for my full range it gives me a #VALUE error. Any ideas on what I'm doing wrong/how to modify? Here is the full range I was testing it on. This range will differ in numbers each time.

Formula used: =LET(c,SUBSTITUTE(CONCAT(B5:B21),".",""),MODE.MULT(--MID(c,SEQUENCE(LEN(c)),1)))

3.54​

3.41​

3.67​

4​

4​

3.75​

1.5​

0.37​

90.00​

62.96​

-1​

0.52​

-1​

-3.16​

-0.68​

102.77​

90.51​

 

[Peter_SSs]

Any ideas on what I'm doing wrong/how to modify?

Yes, you didn't mention or include examples of negative numbers before so as well as the formula removing the decimal points it also needs to remove those "-" signs so we are left with digits only.

24 02 25.xlsm
	B	C	D
5	3.54		1
6	3.41
7	3.67
8	4
9	4
10	3.75
11	1.5
12	0.37
13	90
14	62.96
15	-1
16	0.52
17	-1
18	-3.16
19	-0.68
20	102.77
21	90.51
Mode (2)

Cell Formulas
Range	Formula
D5	D5	=LET(c,SUBSTITUTE(SUBSTITUTE(CONCAT(B5:B21),".",""),"-",""),MODE.MULT(--MID(c,SEQUENCE(LEN(c)),1)))

 

[Scott Huish]

A little shorter if you take the absolute value instead of substitute
=LET(c,SUBSTITUTE(CONCAT(ABS(B5:B21)),".",""),MODE.MULT(--MID(c,SEQUENCE(LEN(c)),1)))

 

[Rick Rothstein]

Here is another formula, a longer one using a different approach, that will also work...

=LET(s,SEQUENCE(10)-1,c,CONCAT(B5:B21),m,MAP(s,LAMBDA(x,LEN(c)-LEN(SUBSTITUTE(c,x,"")))),FILTER(s,m=MAX(m)))

 

[StephenCrump]

... and another

=LET(s,SEQUENCE(10,,0),L,LEN(SUBSTITUTE(CONCAT(B5:B21),s,"")),FILTER(s,L=MIN(L)))

EDIT: which I now see is just a variation on Rick's

 

[nirvehex]

Thank you all! Very helpful and much appreciated!

 

[Peter_SSs]

You're welcome. Glad we were able to help. Thanks for the follow-up.

 

[nirvehex]

Sorry - another question. I'm getting a #SPILL! error when I use this data set below, using this formula: =LET(c,SUBSTITUTE(CONCAT(ABS(B5:B21)),".",""),MODE.MULT(--MID(c,SEQUENCE(LEN(c)),1)))
Does this mean there are two numbers that have the same amount and are the most common?

2.91​

2.55​

3.28​

2.33​

2.67​

2.5​

0​

-0.22​

11.00​

29.41​

-1​

0.45​

-1​

-3.28​

-1.07​

101.01​

90.74​