Formula for
"Doctor T" wrote...
I need help creating a formula in Excel. I have a file that contains
products in column A with their corresponding location in column B. I
want to easily determine for each product what the LEAST occurences in
location are. For example, A1:A25=apples, A26:A220=paper clips,
A221:A222=pens. The corresponding location for each product are listed
in column B. Example, B1:B10=Maine,B11=NY,B12:B25=Ohio. From this, it's
easy to see that "apples" has only one location occurance located in NY.
....
With the product of interest in cell D2, try the array formula
=INDEX(B$2:B$1001,MATCH(MIN(IF(FREQUENCY(IF(A$2:A$ 1001=D2,
MATCH(B$2:B$1001,IF(A$2:A$1001=D2,B$2:B$1001),0)),
ROW(A$2:A$1001))0,FREQUENCY(IF(A$2:A$1001=D2,MATC H(B$2:B$1001,
IF(A$2:A$1001=D2,B$2:B$1001),0)),ROW(A$2:A$1001))) ),
FREQUENCY(IF(A$2:A$1001=D2,MATCH(B$2:B$1001,IF(A$2 :A$1001=D2,
B$2:B$1001),0)),ROW(A$2:A$1001)),0))
Note: this formula would recalculate VERY SLOWLY for really large ranges.
This sort of thing is much better suited to databases.
|