Extract the lowest from the most repeated range

[Shareez Saleem]

Please help with a formula to get the lowest among the most repeated values.


1
1
0.25
0.5
.5
3
4
1
.5


I have used the formula =MODE(B2:B7,B2:B7) but it will give us the result 1 instead of 0.5

[AliGW]

MODE works with whole numbers.

[XOR LX]

Hi,

=MIN(MODE.MULT(A1:A9))

Regards

[XOR LX]

MODE works with whole numbers.

Not true! Works with non-integers as well.

Regards

[Shareez Saleem]

Thank you for your time and appreciate your help
If there is a criteria, how does it work?
See the attached sample report.

[XOR LX]

Sure.

=MIN(MODE.MULT(IF($A$2:$A$13=D3,$B$2:$B$13)))

Or, more rigorously (in case no one Value for a given Item should occur more than once):

=MIN(MODE.MULT(IF($A$2:$A$13=D3,{1,1}*$B$2:$B$13)))

both with CSE.

Regards

[Shareez Saleem]

You are the best.

[XOR LX]

You're welcome!

Cheers

[AliGW]

Not true! Works with non-integers as well.

Regards

I can't get it to recognise non-integers.

[XOR LX]

I can't get it to recognise non-integers.

Can you give an example to that effect?

Cheers

[AliGW]

MODE does not work with the non-integers here (but your example with MODE.MULT does):

Excel 2016 (Windows) 32 bit

	B	C	D	E
2	1		1	=MODE(B2:B10)
3	1		0.5	=MIN(MODE.MULT(B2:B10))
4	0.25
5	0.5
6	0.5
7	3
8	4
9	1
10	0.5

Sheet: Sheet1

[XOR LX]

Hi Ali,

It's not "not working" in that case.

Remove one of the 1s and you'll see that the MODE formula happily returns 0.5.

What you're seeing in your example is because there are 2 values (0.5 and 1) which share the mode. In such cases MODE returns that which occurs first in the list, which you can easily verify by re-ordering if you want.

Cheers

[AliGW]

It's odd - I tried that and it was not responding earlier with more 0.5 than 1 - I could not get it to change, and could only return results for integers. Having restarted Excel, it is now happily doing its stuff. Gremlins!!!