| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
MIN from another worksheet
Using Excel 2007, I need to a formula on Sheet 2, using the data on Sheet 1,
that gives me the MIN Sales Price (Sheet 1, column F) when the Style (Sheet 1, column G) equals "Condo". -- Shelina |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
MIN from another worksheet
Try this array formula** :
=MIN(IF(Sheet1!G2:G10="condo",Sheet1!F2:F10)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Shelina" wrote in message ... Using Excel 2007, I need to a formula on Sheet 2, using the data on Sheet 1, that gives me the MIN Sales Price (Sheet 1, column F) when the Style (Sheet 1, column G) equals "Condo". -- Shelina |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
MIN from another worksheet
Hi Shelina
Try the below array formula . Enter using Ctrl+Shift+Enter =MIN(IF((Sheet1!G1:G100="condo"),F1:F100)) 'error handled version..incase you have any error cells in the range F1:F100 =MIN(IF((Sheet1!G1:G100="condo")*(ISNUMBER(F1:F100 )),F1:F100)) If this post helps click Yes --------------- Jacob Skaria "Shelina" wrote: Using Excel 2007, I need to a formula on Sheet 2, using the data on Sheet 1, that gives me the MIN Sales Price (Sheet 1, column F) when the Style (Sheet 1, column G) equals "Condo". -- Shelina |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
MIN from another worksheet
Ofcourse with the sheet references....
(array entered) =MIN(IF((Sheet1!G1:G100="condo"),Sheet1!F1:F100)) 'error handled version..incase you have any error cells in the range F1:F100 (array entered) =MIN(IF((Sheet1!G1:G100="condo")* (ISNUMBER(Sheet1!F1:F100)),Sheet1!F1:F100)) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Hi Shelina Try the below array formula . Enter using Ctrl+Shift+Enter =MIN(IF((Sheet1!G1:G100="condo"),F1:F100)) 'error handled version..incase you have any error cells in the range F1:F100 =MIN(IF((Sheet1!G1:G100="condo")*(ISNUMBER(F1:F100 )),F1:F100)) If this post helps click Yes --------------- Jacob Skaria "Shelina" wrote: Using Excel 2007, I need to a formula on Sheet 2, using the data on Sheet 1, that gives me the MIN Sales Price (Sheet 1, column F) when the Style (Sheet 1, column G) equals "Condo". -- Shelina |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
MIN from another worksheet
I tried both formulas and both times the result was 0. It should have been
$182,500. What do you think is the problem? -- Shelina "Jacob Skaria" wrote: Ofcourse with the sheet references.... (array entered) =MIN(IF((Sheet1!G1:G100="condo"),Sheet1!F1:F100)) 'error handled version..incase you have any error cells in the range F1:F100 (array entered) =MIN(IF((Sheet1!G1:G100="condo")* (ISNUMBER(Sheet1!F1:F100)),Sheet1!F1:F100)) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Hi Shelina Try the below array formula . Enter using Ctrl+Shift+Enter =MIN(IF((Sheet1!G1:G100="condo"),F1:F100)) 'error handled version..incase you have any error cells in the range F1:F100 =MIN(IF((Sheet1!G1:G100="condo")*(ISNUMBER(F1:F100 )),F1:F100)) If this post helps click Yes --------------- Jacob Skaria "Shelina" wrote: Using Excel 2007, I need to a formula on Sheet 2, using the data on Sheet 1, that gives me the MIN Sales Price (Sheet 1, column F) when the Style (Sheet 1, column G) equals "Condo". -- Shelina |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
MIN from another worksheet
Try this out in a new worksheet with few dummy entries and see
=MIN(IF((G1:G10="condo"),F1:F10)) If this post helps click Yes --------------- Jacob Skaria "Shelina" wrote: I tried both formulas and both times the result was 0. It should have been $182,500. What do you think is the problem? -- Shelina "Jacob Skaria" wrote: Ofcourse with the sheet references.... (array entered) =MIN(IF((Sheet1!G1:G100="condo"),Sheet1!F1:F100)) 'error handled version..incase you have any error cells in the range F1:F100 (array entered) =MIN(IF((Sheet1!G1:G100="condo")* (ISNUMBER(Sheet1!F1:F100)),Sheet1!F1:F100)) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Hi Shelina Try the below array formula . Enter using Ctrl+Shift+Enter =MIN(IF((Sheet1!G1:G100="condo"),F1:F100)) 'error handled version..incase you have any error cells in the range F1:F100 =MIN(IF((Sheet1!G1:G100="condo")*(ISNUMBER(F1:F100 )),F1:F100)) If this post helps click Yes --------------- Jacob Skaria "Shelina" wrote: Using Excel 2007, I need to a formula on Sheet 2, using the data on Sheet 1, that gives me the MIN Sales Price (Sheet 1, column F) when the Style (Sheet 1, column G) equals "Condo". -- Shelina |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| plot graph from multiple worksheet as embedded chart object on every worksheet | Charts and Charting in Excel | |||
| plot graph from multiple worksheet as embedded chart object on every worksheet | Excel Discussion (Misc queries) | |||
| plot graph from multiple worksheet as embedded chart object on every worksheet | Excel Worksheet Functions | |||
| Upload multiple text files into 1 excel worksheet + put the filename as the first column in the worksheet | Excel Worksheet Functions | |||
| I want in one worksheet to relatively link to/reference cells in another without changing the format of the current worksheet. | Excel Discussion (Misc queries) | |||
Linear Mode
