# Formula Opposite of MODE()

#### Zbish

##### Board Regular
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

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Although I don't see what purpose it might serve...
Book11
ABCD
1
26103
311111
47
53
610
72
81
96
1016
1113
125
138
141
1510
1612
1716
1810
1916
203
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.

Many thanks does the job nicely

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

Regards

Flynne

Replies
5
Views
301
Replies
3
Views
217
Replies
1
Views
336
Replies
3
Views
422
Replies
6
Views
189

1,203,686
Messages
6,056,738
Members
444,888
Latest member
Babi_mn

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back