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​

 

[Rick Rothstein]

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​

Did you try the formula I posted yet?

 

[Scott Huish]

Do you have a value right underneath where the formula is? It should've returned 2 values one right under the other

 

[Peter_SSs]

Does this mean there are two numbers that have the same amount and are the most common?

Yes. That sample data contains ten "1"s and ten "2"s.

You will only get the spill error if you have something already in the cell immediately below the cell that contains this formula & the data contains more than one "equal most" digit.

If you want to report all values if they are equally most common then there are options.

First, you could ensure there is plenty of room under the formula cell to accommodate any equal most common value like in D5 below.
Second, if you have space to the right of the formula cell you could use a formula like in D17.
Third, you could report all the values in a single cell like D19.
D21 offers a fourth option where any equally popular digits are put into ascending order.

24 02 25.xlsm
	B	C	D	E
5	2.91		2
6	2.55		1
7	3.28
8	2.33
9	2.67
10	2.5
11	0
12	-0.22
13	11
14	29.41
15	-1
16	0.45
17	-1		2	1
18	-3.28
19	-1.07		2, 1
20	101.01
21	90.74		1, 2
Mode (4)

Cell Formulas
Range	Formula
D5:D6	D5	=LET(c,SUBSTITUTE(CONCAT(ABS(B5:B21)),".",""),MODE.MULT(--MID(c,SEQUENCE(LEN(c)),1)))
D17:E17	D17	=LET(c,SUBSTITUTE(CONCAT(ABS(B5:B21)),".",""),TRANSPOSE(MODE.MULT(--MID(c,SEQUENCE(LEN(c)),1))))
D19	D19	=LET(c,SUBSTITUTE(CONCAT(ABS(B5:B21)),".",""),TEXTJOIN(", ",,MODE.MULT(--MID(c,SEQUENCE(LEN(c)),1))))
D21	D21	=LET(c,SUBSTITUTE(CONCAT(ABS(B5:B21)),".",""),TEXTJOIN(", ",,SORT(MODE.MULT(--MID(c,SEQUENCE(LEN(c)),1)))))
Dynamic array formulas.

 

[nirvehex]

Thanks, I did have a value right below. I fixed it. Appreciate the help!

 

[Peter_SSs]

Cheers. Glad it is sorted.