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