Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding Data in an Array | Excel Worksheet Functions | |||
finding nearest match from an array | Excel Discussion (Misc queries) | |||
Finding Location of Maximum Value in 2D Array | Excel Discussion (Misc queries) | |||
Finding Location of Maximum Value in 2D Array | New Users to Excel | |||
Finding the most frequent occurances | Excel Discussion (Misc queries) |