Assuming the source data is in Sheet1, data in say, A2:B100
In Sheet2
------------
Assuming the products are listed in A1 down,
i.e. in A1: pen, in A2: paper and so on
Put in B1 and array-enter (press CTRL+SHIFT+ENTER):
=MIN(IF(Sheet1!$A$2:$A$100=A1,Sheet1!$B$2:$B$100))
Format B1 as currency and copy down
Col B will return the min prices for the products in col A
Adapt the ranges to suit
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"WvR" wrote in message
...
I need to calculate the min value in col A subject to text in column B
ie;
A B
1 Product Price
2 pen $0.50
3 paper $0.70
4 pen $0.20
I cannot use DMIN because I need to do this calc in a different list, and
i
am batteling with the criteria section of this function as I cannot refer
to
two rows for header and criteria, I would like to specify criteria as -
"product"=A2 - for instance.
Please Help
|