A real challenge...can anyone solve this !??!?

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

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.
Therefore: 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.
 
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
 
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.
 
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
 
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.
 
Hi Bob,

Usually, though not always, the presence of Application.Volatile is a
sign of a weak design.

In this case, adapt J.E.'s otherwise excellent adaptation of Ken's
solution to something like:

Option Explicit

Public Function GetLowest(ARng As Range, A As Integer, _
CRng As Range, C As String, _
DRNg As Range, D As Integer, _
BRng As Range) As Variant
GetLowest = Evaluate("Min(If((" & ARng.Address & " = " & A & _
")*(" & CRng.Address & "=""" & C _
& """)*(" & DRNg.Address & "=" & _
D & "), " & BRng.Address & ", """"))")
End Function

This has the added benefit that we can now refer to any three ranges.
That flexibility can be further leveraged in all sorts of ways.

One is to make the ranges and values part of the ParamArray argument,
thus allowing an arbitrary number of conditions.

Another would be to move the MIN part into either an argument or
eliminate it all together. An example of the latter is:

Public Function GetAllVals(ARng As Range, A As Integer, _
CRng As Range, C As String, _
DRNg As Range, D As Integer, _
BRng As Range) As Variant
GetAllVals = _
Evaluate("If((" & ARng.Address & " = " & A & _
")*(" & CRng.Address & "=""" & C _
& """)*(" & DRNg.Address & "=" & _
D & "), " & BRng.Address & ", """")")
End Function

This function is used as an *regular* formula such as
=MIN(GetAllVals(B2:B8,B11,D2:D8,C11,E2:E8,D11,C2:C8))
or
=MAX(GetAllVals(B2:B8,B11,D2:D8,C11,E2:E8,D11,C2:C8))

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Back
Top