| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FInding Min Value with Criteria
Hello there,
I have a problem... I have a sheet of values and one certain column tells me if the line item is either a but order or a sell order - nominated by either "True" (for sell orders) or "False" (for buy orders), I would like to be able to return the most minimum value of all the "False" lines within the sheet also on a separate formula return the MAX of the "True" lines. i have tried a mixture of formula's from DMIN to IFs and MINs and INDEX and MATCH... it is frustrating me now!!! Many thanks... |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
FInding Min Value with Criteria
either "True" (for sell orders) or "False" (for buy orders)
Are these TEXT values or Boolean values? Booleans appear in all upper case and are centered in the cells: TRUE, FALSE. Try one of these array formula** : For TEXT values: =MIN(IF(B1:B20="FALSE",A1:A20)) =MAX(IF(B1:B20="TRUE",A1:A20)) For Boolean logicals: =MIN(IF(B1:B20=FALSE,A1:A20)) =MAX(IF(B1:B20=TRUE,A1:A20)) ** 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 "AndrewK" wrote in message ... Hello there, I have a problem... I have a sheet of values and one certain column tells me if the line item is either a but order or a sell order - nominated by either "True" (for sell orders) or "False" (for buy orders), I would like to be able to return the most minimum value of all the "False" lines within the sheet also on a separate formula return the MAX of the "True" lines. i have tried a mixture of formula's from DMIN to IFs and MINs and INDEX and MATCH... it is frustrating me now!!! Many thanks... |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
FInding Min Value with Criteria
Hell Valko,
Appreciate the response... the fields are boolean... i have tried the formula and it works fine when there are no TRUE values in the same IF lookup - once i strech this to include the TRUE it comes back with 0 - this is the problem i have been facing... Also i would like to include a formula using whole ranges such as B:B instead of numbering rows (H2:H10) etc... This sheet uses a self updating TXT import hourly... hence being unable to keep chaging formulas hourly... Thank you. "T. Valko" wrote: either "True" (for sell orders) or "False" (for buy orders) Are these TEXT values or Boolean values? Booleans appear in all upper case and are centered in the cells: TRUE, FALSE. Try one of these array formula** : For TEXT values: =MIN(IF(B1:B20="FALSE",A1:A20)) =MAX(IF(B1:B20="TRUE",A1:A20)) For Boolean logicals: =MIN(IF(B1:B20=FALSE,A1:A20)) =MAX(IF(B1:B20=TRUE,A1:A20)) ** 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 "AndrewK" wrote in message ... Hello there, I have a problem... I have a sheet of values and one certain column tells me if the line item is either a but order or a sell order - nominated by either "True" (for sell orders) or "False" (for buy orders), I would like to be able to return the most minimum value of all the "False" lines within the sheet also on a separate formula return the MAX of the "True" lines. i have tried a mixture of formula's from DMIN to IFs and MINs and INDEX and MATCH... it is frustrating me now!!! Many thanks... |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
FInding Min Value with Criteria
For Boolean logicals:
=MIN(IF(B1:B20=FALSE,A1:A20)) =MAX(IF(B1:B20=TRUE,A1:A20)) Try the above formulas itself but as normal (not array entered) If this post helps click Yes --------------- Jacob Skaria "AndrewK" wrote: Hell Valko, Appreciate the response... the fields are boolean... i have tried the formula and it works fine when there are no TRUE values in the same IF lookup - once i strech this to include the TRUE it comes back with 0 - this is the problem i have been facing... Also i would like to include a formula using whole ranges such as B:B instead of numbering rows (H2:H10) etc... This sheet uses a self updating TXT import hourly... hence being unable to keep chaging formulas hourly... Thank you. "T. Valko" wrote: either "True" (for sell orders) or "False" (for buy orders) Are these TEXT values or Boolean values? Booleans appear in all upper case and are centered in the cells: TRUE, FALSE. Try one of these array formula** : For TEXT values: =MIN(IF(B1:B20="FALSE",A1:A20)) =MAX(IF(B1:B20="TRUE",A1:A20)) For Boolean logicals: =MIN(IF(B1:B20=FALSE,A1:A20)) =MAX(IF(B1:B20=TRUE,A1:A20)) ** 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 "AndrewK" wrote in message ... Hello there, I have a problem... I have a sheet of values and one certain column tells me if the line item is either a but order or a sell order - nominated by either "True" (for sell orders) or "False" (for buy orders), I would like to be able to return the most minimum value of all the "False" lines within the sheet also on a separate formula return the MAX of the "True" lines. i have tried a mixture of formula's from DMIN to IFs and MINs and INDEX and MATCH... it is frustrating me now!!! Many thanks... |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
FInding Min Value with Criteria
I don't quite understand this:
it works fine when there are no TRUE values in the same IF lookup - once i strech this to include the TRUE it comes back with 0 - this is the problem i have been facing... =MAX(IF(B1:B20=TRUE,A1:A20)) You say if there are no TRUEs it works fine... If there are no TRUEs in the range then the formula will return 0. But then you say: include the TRUE it comes back with 0 = problem. Are there any empty cells with a corresponding TRUE? i would like to include a formula using whole ranges such as B:B What version of Excel are you using? If you're using Excel 2007 then replace the specific ranges with the entire column like B:B. If you're using any other version then you can't use entire columns as range references. You can use the entire column minus 1 row: B1:B65535 B2:B65536 -- Biff Microsoft Excel MVP "AndrewK" wrote in message ... Hell Valko, Appreciate the response... the fields are boolean... i have tried the formula and it works fine when there are no TRUE values in the same IF lookup - once i strech this to include the TRUE it comes back with 0 - this is the problem i have been facing... Also i would like to include a formula using whole ranges such as B:B instead of numbering rows (H2:H10) etc... This sheet uses a self updating TXT import hourly... hence being unable to keep chaging formulas hourly... Thank you. "T. Valko" wrote: either "True" (for sell orders) or "False" (for buy orders) Are these TEXT values or Boolean values? Booleans appear in all upper case and are centered in the cells: TRUE, FALSE. Try one of these array formula** : For TEXT values: =MIN(IF(B1:B20="FALSE",A1:A20)) =MAX(IF(B1:B20="TRUE",A1:A20)) For Boolean logicals: =MIN(IF(B1:B20=FALSE,A1:A20)) =MAX(IF(B1:B20=TRUE,A1:A20)) ** 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 "AndrewK" wrote in message ... Hello there, I have a problem... I have a sheet of values and one certain column tells me if the line item is either a but order or a sell order - nominated by either "True" (for sell orders) or "False" (for buy orders), I would like to be able to return the most minimum value of all the "False" lines within the sheet also on a separate formula return the MAX of the "True" lines. i have tried a mixture of formula's from DMIN to IFs and MINs and INDEX and MATCH... it is frustrating me now!!! Many thanks... |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
FInding Min Value with Criteria
I mean you are trying the same formulas itself but as normal (non array
entered). It should be array-entered as mentioned by Biff.. If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: For Boolean logicals: =MIN(IF(B1:B20=FALSE,A1:A20)) =MAX(IF(B1:B20=TRUE,A1:A20)) Try the above formulas itself but as normal (not array entered) If this post helps click Yes --------------- Jacob Skaria "AndrewK" wrote: Hell Valko, Appreciate the response... the fields are boolean... i have tried the formula and it works fine when there are no TRUE values in the same IF lookup - once i strech this to include the TRUE it comes back with 0 - this is the problem i have been facing... Also i would like to include a formula using whole ranges such as B:B instead of numbering rows (H2:H10) etc... This sheet uses a self updating TXT import hourly... hence being unable to keep chaging formulas hourly... Thank you. "T. Valko" wrote: either "True" (for sell orders) or "False" (for buy orders) Are these TEXT values or Boolean values? Booleans appear in all upper case and are centered in the cells: TRUE, FALSE. Try one of these array formula** : For TEXT values: =MIN(IF(B1:B20="FALSE",A1:A20)) =MAX(IF(B1:B20="TRUE",A1:A20)) For Boolean logicals: =MIN(IF(B1:B20=FALSE,A1:A20)) =MAX(IF(B1:B20=TRUE,A1:A20)) ** 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 "AndrewK" wrote in message ... Hello there, I have a problem... I have a sheet of values and one certain column tells me if the line item is either a but order or a sell order - nominated by either "True" (for sell orders) or "False" (for buy orders), I would like to be able to return the most minimum value of all the "False" lines within the sheet also on a separate formula return the MAX of the "True" lines. i have tried a mixture of formula's from DMIN to IFs and MINs and INDEX and MATCH... it is frustrating me now!!! Many thanks... |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
FInding Min Value with Criteria
Try the above formulas itself but as normal (not array entered)
Why would you do that? =MIN(IF(B1:B20=FALSE,A1:A20)) =MAX(IF(B1:B20=TRUE,A1:A20)) Normally enter one of those formulas in cell C35 and let me know what happens. -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... For Boolean logicals: =MIN(IF(B1:B20=FALSE,A1:A20)) =MAX(IF(B1:B20=TRUE,A1:A20)) Try the above formulas itself but as normal (not array entered) If this post helps click Yes --------------- Jacob Skaria "AndrewK" wrote: Hell Valko, Appreciate the response... the fields are boolean... i have tried the formula and it works fine when there are no TRUE values in the same IF lookup - once i strech this to include the TRUE it comes back with 0 - this is the problem i have been facing... Also i would like to include a formula using whole ranges such as B:B instead of numbering rows (H2:H10) etc... This sheet uses a self updating TXT import hourly... hence being unable to keep chaging formulas hourly... Thank you. "T. Valko" wrote: either "True" (for sell orders) or "False" (for buy orders) Are these TEXT values or Boolean values? Booleans appear in all upper case and are centered in the cells: TRUE, FALSE. Try one of these array formula** : For TEXT values: =MIN(IF(B1:B20="FALSE",A1:A20)) =MAX(IF(B1:B20="TRUE",A1:A20)) For Boolean logicals: =MIN(IF(B1:B20=FALSE,A1:A20)) =MAX(IF(B1:B20=TRUE,A1:A20)) ** 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 "AndrewK" wrote in message ... Hello there, I have a problem... I have a sheet of values and one certain column tells me if the line item is either a but order or a sell order - nominated by either "True" (for sell orders) or "False" (for buy orders), I would like to be able to return the most minimum value of all the "False" lines within the sheet also on a separate formula return the MAX of the "True" lines. i have tried a mixture of formula's from DMIN to IFs and MINs and INDEX and MATCH... it is frustrating me now!!! Many thanks... |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
FInding Min Value with Criteria
Sorry to confuse - this is the table below;
price typeID bid 700000 649 FALSE 700000 649 FALSE 700000 649 FALSE 700000 649 FALSE 719000 649 FALSE 720000 649 FALSE 720000 649 FALSE 725000 649 FALSE 375000 649 TRUE 373000.05 649 TRUE 373000.04 649 TRUE 372608.4699 649 TRUE 371608.55 649 TRUE 371109.01 649 TRUE 371109 649 TRUE 371101 649 TRUE 371100 649 TRUE 363082.03 649 TRUE 360700.04 649 TRUE The criteria is: If itemID = 649 AND bid = FALSE THEN Return the MIN of them = 700000 So i tried =MIN(IF(AND(B:B="649",C:C="FALSE"))) doesn't work right... although i don't know if the logic does either... =] "T. Valko" wrote: I don't quite understand this: it works fine when there are no TRUE values in the same IF lookup - once i strech this to include the TRUE it comes back with 0 - this is the problem i have been facing... =MAX(IF(B1:B20=TRUE,A1:A20)) You say if there are no TRUEs it works fine... If there are no TRUEs in the range then the formula will return 0. But then you say: include the TRUE it comes back with 0 = problem. Are there any empty cells with a corresponding TRUE? i would like to include a formula using whole ranges such as B:B What version of Excel are you using? If you're using Excel 2007 then replace the specific ranges with the entire column like B:B. If you're using any other version then you can't use entire columns as range references. You can use the entire column minus 1 row: B1:B65535 B2:B65536 -- Biff Microsoft Excel MVP "AndrewK" wrote in message ... Hell Valko, Appreciate the response... the fields are boolean... i have tried the formula and it works fine when there are no TRUE values in the same IF lookup - once i strech this to include the TRUE it comes back with 0 - this is the problem i have been facing... Also i would like to include a formula using whole ranges such as B:B instead of numbering rows (H2:H10) etc... This sheet uses a self updating TXT import hourly... hence being unable to keep chaging formulas hourly... Thank you. "T. Valko" wrote: either "True" (for sell orders) or "False" (for buy orders) Are these TEXT values or Boolean values? Booleans appear in all upper case and are centered in the cells: TRUE, FALSE. Try one of these array formula** : For TEXT values: =MIN(IF(B1:B20="FALSE",A1:A20)) =MAX(IF(B1:B20="TRUE",A1:A20)) For Boolean logicals: =MIN(IF(B1:B20=FALSE,A1:A20)) =MAX(IF(B1:B20=TRUE,A1:A20)) ** 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 "AndrewK" wrote in message ... Hello there, I have a problem... I have a sheet of values and one certain column tells me if the line item is either a but order or a sell order - nominated by either "True" (for sell orders) or "False" (for buy orders), I would like to be able to return the most minimum value of all the "False" lines within the sheet also on a separate formula return the MAX of the "True" lines. i have tried a mixture of formula's from DMIN to IFs and MINs and INDEX and MATCH... it is frustrating me now!!! Many thanks... |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
FInding Min Value with Criteria
Try the below formulas (again array entered)
649 is a number right ??? =MIN(IF(B1:B20=649,IF(C1:C20=FALSE,A1:A20))) =MAX(IF(B1:B20=649,IF(C1:C20=TRUE,A1:A20))) If this post helps click Yes --------------- Jacob Skaria "AndrewK" wrote: Sorry to confuse - this is the table below; price typeID bid 700000 649 FALSE 700000 649 FALSE 700000 649 FALSE 700000 649 FALSE 719000 649 FALSE 720000 649 FALSE 720000 649 FALSE 725000 649 FALSE 375000 649 TRUE 373000.05 649 TRUE 373000.04 649 TRUE 372608.4699 649 TRUE 371608.55 649 TRUE 371109.01 649 TRUE 371109 649 TRUE 371101 649 TRUE 371100 649 TRUE 363082.03 649 TRUE 360700.04 649 TRUE The criteria is: If itemID = 649 AND bid = FALSE THEN Return the MIN of them = 700000 So i tried =MIN(IF(AND(B:B="649",C:C="FALSE"))) doesn't work right... although i don't know if the logic does either... =] "T. Valko" wrote: I don't quite understand this: it works fine when there are no TRUE values in the same IF lookup - once i strech this to include the TRUE it comes back with 0 - this is the problem i have been facing... =MAX(IF(B1:B20=TRUE,A1:A20)) You say if there are no TRUEs it works fine... If there are no TRUEs in the range then the formula will return 0. But then you say: include the TRUE it comes back with 0 = problem. Are there any empty cells with a corresponding TRUE? i would like to include a formula using whole ranges such as B:B What version of Excel are you using? If you're using Excel 2007 then replace the specific ranges with the entire column like B:B. If you're using any other version then you can't use entire columns as range references. You can use the entire column minus 1 row: B1:B65535 B2:B65536 -- Biff Microsoft Excel MVP "AndrewK" wrote in message ... Hell Valko, Appreciate the response... the fields are boolean... i have tried the formula and it works fine when there are no TRUE values in the same IF lookup - once i strech this to include the TRUE it comes back with 0 - this is the problem i have been facing... Also i would like to include a formula using whole ranges such as B:B instead of numbering rows (H2:H10) etc... This sheet uses a self updating TXT import hourly... hence being unable to keep chaging formulas hourly... Thank you. "T. Valko" wrote: either "True" (for sell orders) or "False" (for buy orders) Are these TEXT values or Boolean values? Booleans appear in all upper case and are centered in the cells: TRUE, FALSE. Try one of these array formula** : For TEXT values: =MIN(IF(B1:B20="FALSE",A1:A20)) =MAX(IF(B1:B20="TRUE",A1:A20)) For Boolean logicals: =MIN(IF(B1:B20=FALSE,A1:A20)) =MAX(IF(B1:B20=TRUE,A1:A20)) ** 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 "AndrewK" wrote in message ... Hello there, I have a problem... I have a sheet of values and one certain column tells me if the line item is either a but order or a sell order - nominated by either "True" (for sell orders) or "False" (for buy orders), I would like to be able to return the most minimum value of all the "False" lines within the sheet also on a separate formula return the MAX of the "True" lines. i have tried a mixture of formula's from DMIN to IFs and MINs and INDEX and MATCH... it is frustrating me now!!! Many thanks... |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
FInding Min Value with Criteria
sorry champ i get #N/A
and im sure the fields are a number - i have formatted them as a number... "Jacob Skaria" wrote: Try the below formulas (again array entered) 649 is a number right ??? =MIN(IF(B1:B20=649,IF(C1:C20=FALSE,A1:A20))) =MAX(IF(B1:B20=649,IF(C1:C20=TRUE,A1:A20))) If this post helps click Yes --------------- Jacob Skaria "AndrewK" wrote: Sorry to confuse - this is the table below; price typeID bid 700000 649 FALSE 700000 649 FALSE 700000 649 FALSE 700000 649 FALSE 719000 649 FALSE 720000 649 FALSE 720000 649 FALSE 725000 649 FALSE 375000 649 TRUE 373000.05 649 TRUE 373000.04 649 TRUE 372608.4699 649 TRUE 371608.55 649 TRUE 371109.01 649 TRUE 371109 649 TRUE 371101 649 TRUE 371100 649 TRUE 363082.03 649 TRUE 360700.04 649 TRUE The criteria is: If itemID = 649 AND bid = FALSE THEN Return the MIN of them = 700000 So i tried =MIN(IF(AND(B:B="649",C:C="FALSE"))) doesn't work right... although i don't know if the logic does either... =] "T. Valko" wrote: I don't quite understand this: it works fine when there are no TRUE values in the same IF lookup - once i strech this to include the TRUE it comes back with 0 - this is the problem i have been facing... =MAX(IF(B1:B20=TRUE,A1:A20)) You say if there are no TRUEs it works fine... If there are no TRUEs in the range then the formula will return 0. But then you say: include the TRUE it comes back with 0 = problem. Are there any empty cells with a corresponding TRUE? i would like to include a formula using whole ranges such as B:B What version of Excel are you using? If you're using Excel 2007 then replace the specific ranges with the entire column like B:B. If you're using any other version then you can't use entire columns as range references. You can use the entire column minus 1 row: B1:B65535 B2:B65536 -- Biff Microsoft Excel MVP "AndrewK" wrote in message ... Hell Valko, Appreciate the response... the fields are boolean... i have tried the formula and it works fine when there are no TRUE values in the same IF lookup - once i strech this to include the TRUE it comes back with 0 - this is the problem i have been facing... Also i would like to include a formula using whole ranges such as B:B instead of numbering rows (H2:H10) etc... This sheet uses a self updating TXT import hourly... hence being unable to keep chaging formulas hourly... Thank you. "T. Valko" wrote: either "True" (for sell orders) or "False" (for buy orders) Are these TEXT values or Boolean values? Booleans appear in all upper case and are centered in the cells: TRUE, FALSE. Try one of these array formula** : For TEXT values: =MIN(IF(B1:B20="FALSE",A1:A20)) =MAX(IF(B1:B20="TRUE",A1:A20)) For Boolean logicals: =MIN(IF(B1:B20=FALSE,A1:A20)) =MAX(IF(B1:B20=TRUE,A1:A20)) ** 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 "AndrewK" wrote in message ... Hello there, I have a problem... I have a sheet of values and one certain column tells me if the line item is either a but order or a sell order - nominated by either "True" (for sell orders) or "False" (for buy orders), I would like to be able to return the most minimum value of all the "False" lines within the sheet also on a separate formula return the MAX of the "True" lines. i have tried a mixture of formula's from DMIN to IFs and MINs and INDEX and MATCH... it is frustrating me now!!! Many thanks... |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
FInding Min Value with Criteria
Interesting...
--Did you try with the same formulas or on a different range. --I tried exactly the same formulas with the below sample data in the range A1:C20..In a new worksheet copy the below sample data...and try with the same formulas....and feedback In D2 =MIN(IF(B1:B20=649,IF(C1:C20=FALSE,A1:A20))) In D3 =MAX(IF(B1:B20=649,IF(C1:C20=TRUE,A1:A20))) Col A Col B Col C Col D price typeID bid Results 700000 649 FALSE 700000 700000 649 FALSE 375000 700000 649 FALSE 700000 649 FALSE 719000 649 FALSE 720000 649 FALSE 720000 649 FALSE 725000 649 FALSE 375000 649 TRUE 373000.05 649 TRUE 373000.04 649 TRUE 372608.46 649 TRUE 371608.55 649 TRUE 371109.01 649 TRUE 371109 649 TRUE 371101 649 TRUE 371100 649 TRUE 363082.03 649 TRUE 360700.04 649 TRUE If this post helps click Yes --------------- Jacob Skaria "AndrewK" wrote: sorry champ i get #N/A and im sure the fields are a number - i have formatted them as a number... "Jacob Skaria" wrote: Try the below formulas (again array entered) 649 is a number right ??? =MIN(IF(B1:B20=649,IF(C1:C20=FALSE,A1:A20))) =MAX(IF(B1:B20=649,IF(C1:C20=TRUE,A1:A20))) If this post helps click Yes --------------- Jacob Skaria "AndrewK" wrote: Sorry to confuse - this is the table below; price typeID bid 700000 649 FALSE 700000 649 FALSE 700000 649 FALSE 700000 649 FALSE 719000 649 FALSE 720000 649 FALSE 720000 649 FALSE 725000 649 FALSE 375000 649 TRUE 373000.05 649 TRUE 373000.04 649 TRUE 372608.4699 649 TRUE 371608.55 649 TRUE 371109.01 649 TRUE 371109 649 TRUE 371101 649 TRUE 371100 649 TRUE 363082.03 649 TRUE 360700.04 649 TRUE The criteria is: If itemID = 649 AND bid = FALSE THEN Return the MIN of them = 700000 So i tried =MIN(IF(AND(B:B="649",C:C="FALSE"))) doesn't work right... although i don't know if the logic does either... =] "T. Valko" wrote: I don't quite understand this: it works fine when there are no TRUE values in the same IF lookup - once i strech this to include the TRUE it comes back with 0 - this is the problem i have been facing... =MAX(IF(B1:B20=TRUE,A1:A20)) You say if there are no TRUEs it works fine... If there are no TRUEs in the range then the formula will return 0. But then you say: include the TRUE it comes back with 0 = problem. Are there any empty cells with a corresponding TRUE? i would like to include a formula using whole ranges such as B:B What version of Excel are you using? If you're using Excel 2007 then replace the specific ranges with the entire column like B:B. If you're using any other version then you can't use entire columns as range references. You can use the entire column minus 1 row: B1:B65535 B2:B65536 -- Biff Microsoft Excel MVP "AndrewK" wrote in message ... Hell Valko, Appreciate the response... the fields are boolean... i have tried the formula and it works fine when there are no TRUE values in the same IF lookup - once i strech this to include the TRUE it comes back with 0 - this is the problem i have been facing... Also i would like to include a formula using whole ranges such as B:B instead of numbering rows (H2:H10) etc... This sheet uses a self updating TXT import hourly... hence being unable to keep chaging formulas hourly... Thank you. "T. Valko" wrote: either "True" (for sell orders) or "False" (for buy orders) Are these TEXT values or Boolean values? Booleans appear in all upper case and are centered in the cells: TRUE, FALSE. Try one of these array formula** : For TEXT values: =MIN(IF(B1:B20="FALSE",A1:A20)) =MAX(IF(B1:B20="TRUE",A1:A20)) For Boolean logicals: =MIN(IF(B1:B20=FALSE,A1:A20)) =MAX(IF(B1:B20=TRUE,A1:A20)) ** 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 "AndrewK" wrote in message ... Hello there, I have a problem... I have a sheet of values and one certain column tells me if the line item is either a but order or a sell order - nominated by either "True" (for sell orders) or "False" (for buy orders), I would like to be able to return the most minimum value of all the "False" lines within the sheet also on a separate formula return the MAX of the "True" lines. i have tried a mixture of formula's from DMIN to IFs and MINs and INDEX and MATCH... it is frustrating me now!!! Many thanks... |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
FInding Min Value with Criteria
Perhaps when im importing the data i am not doing it correctly??
im using 2007 auto importing... all data imported from txt file as "general" "Jacob Skaria" wrote: Interesting... --Did you try with the same formulas or on a different range. --I tried exactly the same formulas with the below sample data in the range A1:C20..In a new worksheet copy the below sample data...and try with the same formulas....and feedback In D2 =MIN(IF(B1:B20=649,IF(C1:C20=FALSE,A1:A20))) In D3 =MAX(IF(B1:B20=649,IF(C1:C20=TRUE,A1:A20))) Col A Col B Col C Col D price typeID bid Results 700000 649 FALSE 700000 700000 649 FALSE 375000 700000 649 FALSE 700000 649 FALSE 719000 649 FALSE 720000 649 FALSE 720000 649 FALSE 725000 649 FALSE 375000 649 TRUE 373000.05 649 TRUE 373000.04 649 TRUE 372608.46 649 TRUE 371608.55 649 TRUE 371109.01 649 TRUE 371109 649 TRUE 371101 649 TRUE 371100 649 TRUE 363082.03 649 TRUE 360700.04 649 TRUE If this post helps click Yes --------------- Jacob Skaria "AndrewK" wrote: sorry champ i get #N/A and im sure the fields are a number - i have formatted them as a number... "Jacob Skaria" wrote: Try the below formulas (again array entered) 649 is a number right ??? =MIN(IF(B1:B20=649,IF(C1:C20=FALSE,A1:A20))) =MAX(IF(B1:B20=649,IF(C1:C20=TRUE,A1:A20))) If this post helps click Yes --------------- Jacob Skaria "AndrewK" wrote: Sorry to confuse - this is the table below; price typeID bid 700000 649 FALSE 700000 649 FALSE 700000 649 FALSE 700000 649 FALSE 719000 649 FALSE 720000 649 FALSE 720000 649 FALSE 725000 649 FALSE 375000 649 TRUE 373000.05 649 TRUE 373000.04 649 TRUE 372608.4699 649 TRUE 371608.55 649 TRUE 371109.01 649 TRUE 371109 649 TRUE 371101 649 TRUE 371100 649 TRUE 363082.03 649 TRUE 360700.04 649 TRUE The criteria is: If itemID = 649 AND bid = FALSE THEN Return the MIN of them = 700000 So i tried =MIN(IF(AND(B:B="649",C:C="FALSE"))) doesn't work right... although i don't know if the logic does either... =] "T. Valko" wrote: I don't quite understand this: it works fine when there are no TRUE values in the same IF lookup - once i strech this to include the TRUE it comes back with 0 - this is the problem i have been facing... =MAX(IF(B1:B20=TRUE,A1:A20)) You say if there are no TRUEs it works fine... If there are no TRUEs in the range then the formula will return 0. But then you say: include the TRUE it comes back with 0 = problem. Are there any empty cells with a corresponding TRUE? i would like to include a formula using whole ranges such as B:B What version of Excel are you using? If you're using Excel 2007 then replace the specific ranges with the entire column like B:B. If you're using any other version then you can't use entire columns as range references. You can use the entire column minus 1 row: B1:B65535 B2:B65536 -- Biff Microsoft Excel MVP "AndrewK" wrote in message ... Hell Valko, Appreciate the response... the fields are boolean... i have tried the formula and it works fine when there are no TRUE values in the same IF lookup - once i strech this to include the TRUE it comes back with 0 - this is the problem i have been facing... Also i would like to include a formula using whole ranges such as B:B instead of numbering rows (H2:H10) etc... This sheet uses a self updating TXT import hourly... hence being unable to keep chaging formulas hourly... Thank you. "T. Valko" wrote: either "True" (for sell orders) or "False" (for buy orders) Are these TEXT values or Boolean values? Booleans appear in all upper case and are centered in the cells: TRUE, FALSE. Try one of these array formula** : For TEXT values: =MIN(IF(B1:B20="FALSE",A1:A20)) =MAX(IF(B1:B20="TRUE",A1:A20)) For Boolean logicals: =MIN(IF(B1:B20=FALSE,A1:A20)) =MAX(IF(B1:B20=TRUE,A1:A20)) ** 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 "AndrewK" wrote in message ... Hello there, I have a problem... I have a sheet of values and one certain column tells me if the line item is either a but order or a sell order - nominated by either "True" (for sell orders) or "False" (for buy orders), I would like to be able to return the most minimum value of all the "False" lines within the sheet also on a separate formula return the MAX of the "True" lines. i have tried a mixture of formula's from DMIN to IFs and MINs and INDEX and MATCH... it is frustrating me now!!! Many thanks... |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| finding a value with more than one criteria | Excel Discussion (Misc queries) | |||
| Finding criteria within a range | Excel Discussion (Misc queries) | |||
| Finding Average with Criteria | Excel Worksheet Functions | |||
| finding an entry from two criteria. | Excel Worksheet Functions | |||
| Finding One Value, Matching Three Criteria | Excel Discussion (Misc queries) | |||
Linear Mode
