| Home |
| Search |
| Today's Posts |
|
#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 |
| 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) | |||
Threaded Mode
