|
A real challenge...can anyone solve this !??!?
Hi all,
I posted a problem I am having with getting the lowest value from a set of data WITHOUT using any loops/ADO/sql. I want to get the LOWEST value of B where A=1003,C=R1,D=23. In this case the answer would be B=0.79 Sample Data: A B C D 1003 0.89 R1 23 1003 0.59 R2 24 2444 0.32 R1 23 2444 0.84 R2 24 1003 1.14 R3 13 1003 0.79 R1 23 1003 0.12 R1 16 Ken gave me a brilliant answer (see below) which works, using a worksheet function and array formulas. However I need the answer in the form of a user defined VBA function ( because its part of a much larger problem). Function Format== GetLowest(A,C,D) where A C D are the parameters and Getlowest is the name of the function. Therefo GetLowest(1003,"R1",23) = 0.79 I can do this with Loops , but since the actual data set is 4000 rows plus, and the function needs to be used for each unique combination of A&C&D, this isn't very efficient. Can anybody offer the quickest possible solution possible !? TIA Bob __________________________________________________ ________ Try the following:- =MIN(IF(($A$2:$A$4000=1003)*($C$2:$C$4000="R1")* ($D$2:$D$4000=23),$B$2:$B$4000)) array entered using CTRL+SHIFT+ENTER Assumes that the 1003 and 23 fields are numeric, but if not then simply enclose them with quotes, ie:- =MIN(IF(($A$2:$A$4000="1003")*($C$2:$C$4000="R1")* ($D$2:$D$4000="23"),$B$2:$B$40 00)) or any mixture depending on your data. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL2K & XLXP ----------------------------------------------------------- Attitude - A little thing that makes a BIG difference ----------------------------------------------------------- "Bob" wrote in message ... Imagine the following data: A B C D 1003 0.89 R1 23 1003 0.59 R2 24 2444 0.32 R1 23 2444 0.84 R2 24 1003 1.14 R3 13 1003 0.79 R1 23 1003 0.12 R1 16 WITHOUT using ANY LOOPS (or ADO/SQL), I would like to retrieve the lowest value for B , where A = 1003 and C = R1 and D = 23 (affectively criteria from the first row). In this case the answer should be B=0.79. Does anybody know a way that this can be done!? (it would have to work assuming that there were 4000+ rows in the table) --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.532 / Virus Database: 326 - Release Date: 27/10/2003 |
A real challenge...can anyone solve this !??!?
Do it all at once
Data=Pivot Table report That produced a table: Min of Header2 Header1 Header3 Header4 Total 1003 R1 16 0.12 23 0.79 R2 24 0.59 R3 13 1.14 2444 R1 23 0.32 R2 24 0.84 Regards, Tom Ogilvy Bob wrote in message ... Hi all, I posted a problem I am having with getting the lowest value from a set of data WITHOUT using any loops/ADO/sql. I want to get the LOWEST value of B where A=1003,C=R1,D=23. In this case the answer would be B=0.79 Sample Data: A B C D 1003 0.89 R1 23 1003 0.59 R2 24 2444 0.32 R1 23 2444 0.84 R2 24 1003 1.14 R3 13 1003 0.79 R1 23 1003 0.12 R1 16 Ken gave me a brilliant answer (see below) which works, using a worksheet function and array formulas. However I need the answer in the form of a user defined VBA function ( because its part of a much larger problem). Function Format== GetLowest(A,C,D) where A C D are the parameters and Getlowest is the name of the function. Therefo GetLowest(1003,"R1",23) = 0.79 I can do this with Loops , but since the actual data set is 4000 rows plus, and the function needs to be used for each unique combination of A&C&D, this isn't very efficient. Can anybody offer the quickest possible solution possible !? TIA Bob __________________________________________________ ________ Try the following:- =MIN(IF(($A$2:$A$4000=1003)*($C$2:$C$4000="R1")* ($D$2:$D$4000=23),$B$2:$B$4000)) array entered using CTRL+SHIFT+ENTER Assumes that the 1003 and 23 fields are numeric, but if not then simply enclose them with quotes, ie:- =MIN(IF(($A$2:$A$4000="1003")*($C$2:$C$4000="R1")* ($D$2:$D$4000="23"),$B$2:$B$40 00)) or any mixture depending on your data. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL2K & XLXP ----------------------------------------------------------- Attitude - A little thing that makes a BIG difference ----------------------------------------------------------- "Bob" wrote in message ... Imagine the following data: A B C D 1003 0.89 R1 23 1003 0.59 R2 24 2444 0.32 R1 23 2444 0.84 R2 24 1003 1.14 R3 13 1003 0.79 R1 23 1003 0.12 R1 16 WITHOUT using ANY LOOPS (or ADO/SQL), I would like to retrieve the lowest value for B , where A = 1003 and C = R1 and D = 23 (affectively criteria from the first row). In this case the answer should be B=0.79. Does anybody know a way that this can be done!? (it would have to work assuming that there were 4000+ rows in the table) --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.532 / Virus Database: 326 - Release Date: 27/10/2003 |
A real challenge...can anyone solve this !??!?
Don't know about the quickest, but using Ken's built-in functions
should be pretty quick: Public Function GetLowest(A As Integer, C As String, _ D As Integer) As Variant GetLowest = Evaluate("Min(If((A1:A4000 = " & A & _ ")*(C1:C4000=""" & C & """)*(D1:D4000=" & _ D & "), B1:B4000, """"))") End Function You might also look at a Pivot Table - you can record a macro if desired. In article , "Bob" wrote: Hi all, I posted a problem I am having with getting the lowest value from a set of data WITHOUT using any loops/ADO/sql. I want to get the LOWEST value of B where A=1003,C=R1,D=23. In this case the answer would be B=0.79 Sample Data: A B C D 1003 0.89 R1 23 1003 0.59 R2 24 2444 0.32 R1 23 2444 0.84 R2 24 1003 1.14 R3 13 1003 0.79 R1 23 1003 0.12 R1 16 Ken gave me a brilliant answer (see below) which works, using a worksheet function and array formulas. However I need the answer in the form of a user defined VBA function ( because its part of a much larger problem). Function Format== GetLowest(A,C,D) where A C D are the parameters and Getlowest is the name of the function. Therefo GetLowest(1003,"R1",23) = 0.79 I can do this with Loops , but since the actual data set is 4000 rows plus, and the function needs to be used for each unique combination of A&C&D, this isn't very efficient. Can anybody offer the quickest possible solution possible !? TIA Bob __________________________________________________ ________ Try the following:- =MIN(IF(($A$2:$A$4000=1003)*($C$2:$C$4000="R1")* ($D$2:$D$4000=23),$B$2:$B$4000)) array entered using CTRL+SHIFT+ENTER Assumes that the 1003 and 23 fields are numeric, but if not then simply enclose them with quotes, ie:- =MIN(IF(($A$2:$A$4000="1003")*($C$2:$C$4000="R1")* ($D$2:$D$4000="23"),$B$2:$B$40 00)) or any mixture depending on your data. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL2K & XLXP ----------------------------------------------------------- Attitude - A little thing that makes a BIG difference ----------------------------------------------------------- "Bob" wrote in message ... Imagine the following data: A B C D 1003 0.89 R1 23 1003 0.59 R2 24 2444 0.32 R1 23 2444 0.84 R2 24 1003 1.14 R3 13 1003 0.79 R1 23 1003 0.12 R1 16 WITHOUT using ANY LOOPS (or ADO/SQL), I would like to retrieve the lowest value for B , where A = 1003 and C = R1 and D = 23 (affectively criteria from the first row). In this case the answer should be B=0.79. Does anybody know a way that this can be done!? (it would have to work assuming that there were 4000+ rows in the table) --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.532 / Virus Database: 326 - Release Date: 27/10/2003 |
A real challenge...can anyone solve this !??!?
JE,
Doesn't this need Application.Volatile at the start, in case the table data changes? A further suggestion is to calculate the last row, rather than assume 4000. Small beer probably, but maybe worthwhile. Bob "J.E. McGimpsey" wrote in message ... Don't know about the quickest, but using Ken's built-in functions should be pretty quick: Public Function GetLowest(A As Integer, C As String, _ D As Integer) As Variant GetLowest = Evaluate("Min(If((A1:A4000 = " & A & _ ")*(C1:C4000=""" & C & """)*(D1:D4000=" & _ D & "), B1:B4000, """"))") End Function You might also look at a Pivot Table - you can record a macro if desired. In article , "Bob" wrote: Hi all, I posted a problem I am having with getting the lowest value from a set of data WITHOUT using any loops/ADO/sql. I want to get the LOWEST value of B where A=1003,C=R1,D=23. In this case the answer would be B=0.79 Sample Data: A B C D 1003 0.89 R1 23 1003 0.59 R2 24 2444 0.32 R1 23 2444 0.84 R2 24 1003 1.14 R3 13 1003 0.79 R1 23 1003 0.12 R1 16 Ken gave me a brilliant answer (see below) which works, using a worksheet function and array formulas. However I need the answer in the form of a user defined VBA function ( because its part of a much larger problem). Function Format== GetLowest(A,C,D) where A C D are the parameters and Getlowest is the name of the function. Therefo GetLowest(1003,"R1",23) = 0.79 I can do this with Loops , but since the actual data set is 4000 rows plus, and the function needs to be used for each unique combination of A&C&D, this isn't very efficient. Can anybody offer the quickest possible solution possible !? TIA Bob __________________________________________________ ________ Try the following:- =MIN(IF(($A$2:$A$4000=1003)*($C$2:$C$4000="R1")* ($D$2:$D$4000=23),$B$2:$B$4000)) array entered using CTRL+SHIFT+ENTER Assumes that the 1003 and 23 fields are numeric, but if not then simply enclose them with quotes, ie:- =MIN(IF(($A$2:$A$4000="1003")*($C$2:$C$4000="R1")* ($D$2:$D$4000="23"),$B$2:$B$40 00)) or any mixture depending on your data. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL2K & XLXP ----------------------------------------------------------- Attitude - A little thing that makes a BIG difference ----------------------------------------------------------- "Bob" wrote in message ... Imagine the following data: A B C D 1003 0.89 R1 23 1003 0.59 R2 24 2444 0.32 R1 23 2444 0.84 R2 24 1003 1.14 R3 13 1003 0.79 R1 23 1003 0.12 R1 16 WITHOUT using ANY LOOPS (or ADO/SQL), I would like to retrieve the lowest value for B , where A = 1003 and C = R1 and D = 23 (affectively criteria from the first row). In this case the answer should be B=0.79. Does anybody know a way that this can be done!? (it would have to work assuming that there were 4000+ rows in the table) --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.532 / Virus Database: 326 - Release Date: 27/10/2003 |
A real challenge...can anyone solve this !??!?
I like J.E. method. Would the use of "DMin" work for you? It would require
about 4 helper cells on your worksheet with 3 Named ranges... Sub Demo() Debug.Print [DMIN(DataBase,What,Criteria)] End Sub This returned 0.79 for me. -- Dana DeLouis Using Windows XP & Office XP = = = = = = = = = = = = = = = = = "Bob" wrote in message ... Hi all, I posted a problem I am having with getting the lowest value from a set of data WITHOUT using any loops/ADO/sql. I want to get the LOWEST value of B where A=1003,C=R1,D=23. In this case the answer would be B=0.79 Sample Data: A B C D 1003 0.89 R1 23 1003 0.59 R2 24 2444 0.32 R1 23 2444 0.84 R2 24 1003 1.14 R3 13 1003 0.79 R1 23 1003 0.12 R1 16 Ken gave me a brilliant answer (see below) which works, using a worksheet function and array formulas. However I need the answer in the form of a user defined VBA function ( because its part of a much larger problem). Function Format== GetLowest(A,C,D) where A C D are the parameters and Getlowest is the name of the function. Therefo GetLowest(1003,"R1",23) = 0.79 I can do this with Loops , but since the actual data set is 4000 rows plus, and the function needs to be used for each unique combination of A&C&D, this isn't very efficient. Can anybody offer the quickest possible solution possible !? TIA Bob __________________________________________________ ________ Try the following:- =MIN(IF(($A$2:$A$4000=1003)*($C$2:$C$4000="R1")* ($D$2:$D$4000=23),$B$2:$B$4000)) array entered using CTRL+SHIFT+ENTER Assumes that the 1003 and 23 fields are numeric, but if not then simply enclose them with quotes, ie:- =MIN(IF(($A$2:$A$4000="1003")*($C$2:$C$4000="R1")* ($D$2:$D$4000="23"),$B$2:$B$40 00)) or any mixture depending on your data. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL2K & XLXP ----------------------------------------------------------- Attitude - A little thing that makes a BIG difference ----------------------------------------------------------- "Bob" wrote in message ... Imagine the following data: A B C D 1003 0.89 R1 23 1003 0.59 R2 24 2444 0.32 R1 23 2444 0.84 R2 24 1003 1.14 R3 13 1003 0.79 R1 23 1003 0.12 R1 16 WITHOUT using ANY LOOPS (or ADO/SQL), I would like to retrieve the lowest value for B , where A = 1003 and C = R1 and D = 23 (affectively criteria from the first row). In this case the answer should be B=0.79. Does anybody know a way that this can be done!? (it would have to work assuming that there were 4000+ rows in the table) --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.532 / Virus Database: 326 - Release Date: 27/10/2003 |
| All times are GMT +1. The time now is 10:48 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com