Formula Opposite of MODE()

[Zbish]

Is there a formula that could result in the opposite of MODE(),which generates the most freqently number noted in a range
I'm looking for the least frequent number within a range

Appreciate any assistance

 

[Aladin Akyurek]

Although I don't see what purpose it might serve...

Book11
	A	B	C	D
1
2	6		10	3
3	11		11	1
4	7
5	3
6	10
7	2
8	1
9	6
10	16
11	13
12	5
13	8
14	1
15	10
16	12
17	16
18	10
19	16
20	3
Sheet1

The formula in C3 is with SETV/GETV from the morefunc.xll add-in:

=INDEX(A2:A20,MATCH(MIN(SETV(COUNTIF(A2:A20,A2:A20))),GETV(),0))

which must be confirmed with control+shift+enter instead of just enter.

Without morefunc...

=INDEX(A2:A20,MATCH(MIN(COUNTIF(A2:A20,A2:A20)),COUNTIF(A2:A20,A2:A20),0))

which must still be confirmed with control+shift+enter instead of just enter.

 

[Zbish]

Many thanks does the job nicely

 

[EFLYNN]

Hello Aladin Akyurek

Your Formula works a treat.

Can this formula be extended to determine the 2nd least frequency ,3rd least frequency etc ?

Regards

Flynne