Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 ! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 ! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting based on Text within Text | Excel Discussion (Misc queries) | |||
SUMPRODUCT vs Text??? | Excel Worksheet Functions | |||
Autofitting a row | Excel Discussion (Misc queries) | |||
Sort or Filter option? | Excel Worksheet Functions | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) |