min, dmin, etc
 

I would like to have a function that was able to recognize the
smallest/largest figure in one cell based on a criteria. For example, let's
say I run multiple convenience stores and want to find the lowest price for
SKU 12345 in the following table:

SKU Price
11111 $3.00
12345 $1.50
12345 $1.51
11112 $0.92
12345 $1.49

My worksheet has a 2nd tab with a list of unique SKUs that are being used
and a single column to show the lowest price, and another to show the highest
(and the average). I can't seem to get DMIN or any vlookup functions to
work. Any advice would be appreciated!

min, dmin, etc
 

Try these array formulas** :

Min:

=MIN(IF(A1:A5=12345,B1:B5))

Max:

=MAX(IF(A1:A5=12345,B1:B5))

Average:

=AVERAGE(IF(A1:A5=12345,B1:B5))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"sduffield" wrote in message
...
I would like to have a function that was able to recognize the
smallest/largest figure in one cell based on a criteria. For example,
let's
say I run multiple convenience stores and want to find the lowest price
for
SKU 12345 in the following table:

SKU Price
11111 $3.00
12345 $1.50
12345 $1.51
11112 $0.92
12345 $1.49

My worksheet has a 2nd tab with a list of unique SKUs that are being used
and a single column to show the lowest price, and another to show the
highest
(and the average). I can't seem to get DMIN or any vlookup functions to
work. Any advice would be appreciated!