Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can anyone solve this!?
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) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can anyone solve this!?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can anyone solve this!?
Hi Ken, Thanks for your response. I tried using the formula you gave me for the data in the example below, however it doesn't return the correct result of 0.79. am I doing something wrong ? Please advise, Many thanks, -----Original Message----- 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can anyone solve this!?
Just tested again and I still get 0.79 based on your data. Your 0.79 and other
records in that filed are numerical aren't they?? ie there is no possibility of that data being text. Also, you did array enter the data, so that you see curly brackets around the formula if you look in the cell. Only other thing to check is that you have the formula looking for a text or numerical entry depending on what is in Columns A and C. It MUST be the correct format in the formula that matches what is in the spreadsheet. If that all fails, then by all means fire the spreadsheet down to me and i'll take a look, or I can send you my test worksheet with your example data working. If you send me anything you need to take the NOSPAM bit out of my email address. -- 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 ... Hi Ken, Thanks for your response. I tried using the formula you gave me for the data in the example below, however it doesn't return the correct result of 0.79. am I doing something wrong ? Please advise, Many thanks, -----Original Message----- 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 . --- 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can anyone solve this!?
My apologies, this DOES in fact work and is briliant!!!!! I didn't enter the formula as an array formula!! Many thanks, Supposing I wanted to implement this in VBA? Is there a VBA equivalent to the way this array formula works ? -----Original Message----- Just tested again and I still get 0.79 based on your data. Your 0.79 and other records in that filed are numerical aren't they?? ie there is no possibility of that data being text. Also, you did array enter the data, so that you see curly brackets around the formula if you look in the cell. Only other thing to check is that you have the formula looking for a text or numerical entry depending on what is in Columns A and C. It MUST be the correct format in the formula that matches what is in the spreadsheet. If that all fails, then by all means fire the spreadsheet down to me and i'll take a look, or I can send you my test worksheet with your example data working. If you send me anything you need to take the NOSPAM bit out of my email address. -- 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 ... Hi Ken, Thanks for your response. I tried using the formula you gave me for the data in the example below, however it doesn't return the correct result of 0.79. am I doing something wrong ? Please advise, Many thanks, -----Original Message----- 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 . --- 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Let's see if you can solve this. | Charts and Charting in Excel | |||
How do I solve for x? | Excel Worksheet Functions | |||
need help to solve this | Excel Discussion (Misc queries) | |||
y i cannot solve it ? | Excel Worksheet Functions | |||
I can't solve this one...can anyone help | Excel Programming |