Finding the least frequent value in an array
 

Hi,
Does anyone know if Excel (either 2000, 2003, XP or 2007) has a quick way of
finding the OPPOSITE of the mode, I.E. the least frequently occuring value in
an array of values? I've thought of count and countif possibilities, but for
what I want to set up and who is going to use it that will be too complex. I
need to set up a sheet that will receive about 50 new sets of data per day
and each time it is updated we need to calculate, amongst about 40 other
things, the mode and the least frequent value from one of the data sets. Mode
is hunky dory......can anyone help with the other please?
Thanks
Dave

Finding the least frequent value in an array
 

With
A2:A40 containing numeric values

This regular formula returns the least occurring value in A2:A40
A1:
=INDEX(A2:A40,MATCH(MIN(INDEX(COUNTIF(A2:A40,A2:A4 0),0)),INDEX(COUNTIF(A2:A40,A2:A40),0),0))

Alternatively, you could use this ARRAY FORMULA variation (committed with
CTRL+SHIFT+ENTER, instead of just ENTER)

A1: =INDEX(A2:A40,MATCH(MIN(COUNTIF(A2:A40,A2:A40)),CO UNTIF(A2:A40,A2:A40),0))

Note: That formula does not account for multiple least occurring values.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Dave D" wrote:

Hi,
Does anyone know if Excel (either 2000, 2003, XP or 2007) has a quick way of
finding the OPPOSITE of the mode, I.E. the least frequently occuring value in
an array of values? I've thought of count and countif possibilities, but for
what I want to set up and who is going to use it that will be too complex. I
need to set up a sheet that will receive about 50 new sets of data per day
and each time it is updated we need to calculate, amongst about 40 other
things, the mode and the least frequent value from one of the data sets. Mode
is hunky dory......can anyone help with the other please?
Thanks
Dave

Finding the least frequent value in an array
 

Hi Ron,
Thank you very much - that's brilliant because it confirms that I wasn't
missing something built into Excel (which is a good thing as I'm a MOS
instructor and woudl be upset if I didn't know the built in stuff pretty
well!) and also because, although it isn't dead simple, at least I can ensure
that the people updating the spreadsheet understand where the cell references
might need updating, and they are all the same ref's so no "do this here and
that there" kind of instructions.
Thank you very much indeed.

Dave

"Ron Coderre" wrote:

With
A2:A40 containing numeric values

This regular formula returns the least occurring value in A2:A40
A1:
=INDEX(A2:A40,MATCH(MIN(INDEX(COUNTIF(A2:A40,A2:A4 0),0)),INDEX(COUNTIF(A2:A40,A2:A40),0),0))

Alternatively, you could use this ARRAY FORMULA variation (committed with
CTRL+SHIFT+ENTER, instead of just ENTER)

A1: =INDEX(A2:A40,MATCH(MIN(COUNTIF(A2:A40,A2:A40)),CO UNTIF(A2:A40,A2:A40),0))

Note: That formula does not account for multiple least occurring values.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Dave D" wrote:

Hi,
Does anyone know if Excel (either 2000, 2003, XP or 2007) has a quick way of
finding the OPPOSITE of the mode, I.E. the least frequently occuring value in
an array of values? I've thought of count and countif possibilities, but for
what I want to set up and who is going to use it that will be too complex. I
need to set up a sheet that will receive about 50 new sets of data per day
and each time it is updated we need to calculate, amongst about 40 other
things, the mode and the least frequent value from one of the data sets. Mode
is hunky dory......can anyone help with the other please?
Thanks
Dave

Finding the least frequent value in an array
 

I'm glad that worked for you, Dave........and thanks much for the feedback.

***********
Regards,
Ron

XL2002, WinXP


"Dave D" wrote:

Hi Ron,
Thank you very much - that's brilliant because it confirms that I wasn't
missing something built into Excel (which is a good thing as I'm a MOS
instructor and woudl be upset if I didn't know the built in stuff pretty
well!) and also because, although it isn't dead simple, at least I can ensure
that the people updating the spreadsheet understand where the cell references
might need updating, and they are all the same ref's so no "do this here and
that there" kind of instructions.
Thank you very much indeed.

Dave

"Ron Coderre" wrote:

With
A2:A40 containing numeric values

This regular formula returns the least occurring value in A2:A40
A1:
=INDEX(A2:A40,MATCH(MIN(INDEX(COUNTIF(A2:A40,A2:A4 0),0)),INDEX(COUNTIF(A2:A40,A2:A40),0),0))

Alternatively, you could use this ARRAY FORMULA variation (committed with
CTRL+SHIFT+ENTER, instead of just ENTER)

A1: =INDEX(A2:A40,MATCH(MIN(COUNTIF(A2:A40,A2:A40)),CO UNTIF(A2:A40,A2:A40),0))

Note: That formula does not account for multiple least occurring values.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Dave D" wrote:

Hi,
Does anyone know if Excel (either 2000, 2003, XP or 2007) has a quick way of
finding the OPPOSITE of the mode, I.E. the least frequently occuring value in
an array of values? I've thought of count and countif possibilities, but for
what I want to set up and who is going to use it that will be too complex. I
need to set up a sheet that will receive about 50 new sets of data per day
and each time it is updated we need to calculate, amongst about 40 other
things, the mode and the least frequent value from one of the data sets. Mode
is hunky dory......can anyone help with the other please?
Thanks
Dave