calculate min in col B subject to text in col A
 

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

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

Thanks a million !

"Max" wrote:

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

You're welcome !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"WvR" wrote in message
...
Thanks a million !

calculate min in col B subject to text in col A
 

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

"Max":

You're welcome !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"WvR" wrote in message
...
Thanks a million !

calculate min in col B subject to text in col A
 

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

calculate min in col B subject to text in col A
 

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
On Feb 5, 8:45 pm, erhan wrote:
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

calculate min in col B subject to text in col A
 

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

A1,Sheet1

give error!

"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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
On Feb 5, 8:45 pm, erhan wrote:
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

calculate min in col B subject to text in col A
 

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

formul is not solve. couse it seems like subject.

"erhan":

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

A1,Sheet1

give error!

"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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
On Feb 5, 8:45 pm, erhan wrote:
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

calculate min in col B subject to text in col A
 

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
On Feb 6, 1:15 am, erhan wrote:
http://www.flypicture.com/download/ODE0NDQ=
erhan_wks.JPG

formul is not solve. couse it seems like subject.

"erhan":



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

A1,Sheet1

give error!

calculate min in col B subject to text in col A
 

thanks alot Max.

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

"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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
On Feb 5, 8:45 pm, erhan wrote:
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