calculate min in col B subject to text in col A

[Guest]

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

 

[Max]

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

 

[Guest]

Thanks a million !

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

 

[Max]

You're welcome !
Thanks for the feedback ..

 

[Guest]

formul give error that you wrote. ( "if" given subject )

"Max":

 

[Guest]

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

 

[Max]

Source data in Sheet1's cols A and B,
from row2 to say row100, items in col A, prices in col B

In Sheet2,

With the items listed in A1 down, eg: Pen, Paper, etc

Array-entered in B1 (press CTRL+SHIFT+ENTER):
=MIN(IF(Sheet1!$A$2:$A$100=A1,Sheet1!$B$2:$B$100))
Copy B1 down

A quick working sample for your easy ref:
http://www.flypicture.com/download/ODE0NDQ=
erhan_wks.xls

Your earlier "error" posted was probably due to incorrect or non array-
entering of the array formula. You should confirm that the formula is
correctly array-entered by looking out for the curly braces inserted
by Excel: { } within the formula bar. If you don't see the curly
braces, then it hasn't been done correctly, and needs to be redone.

 

[Guest]

http://www.flypicture.com/download/ODE0NDQ=

erhan_wks.xls

A1,Sheet1

give error!

"Max":

 

[Guest]

http://www.flypicture.com/download/ODE0NDQ=
erhan_wks.JPG

formul is not solve. couse it seems like subject.

"erhan":

 

[Max]

Erhan,

I presume you are having some difficulties getting the download?

Try this:

Copy and paste the link below into the address bar in your browser:

http://www.flypicture.com/download/ODE0NDQ=


Press Enter

Then in the page that opens, just click on "Download/View"
This will pop up a dialog to either open/save the file
Save the file somewhere, then open it.
The file contains a working construct of the array formula

I really don't know what more I can do for you
besides flying over there and sitting next to you <g>

---

 

[Guest]

thanks alot Max.

Array-entered in B1 (press CTRL+SHIFT+ENTER):