Extracting max & min values with a condition

[vba]

Public Function MAXIF(rng1, op, cond, Optional rng2)
Dim sFormula As String
If IsMissing(rng2) Then
Set rng2 = rng1
End If
If TypeName(cond) = "Range" Then
If Not IsNumeric(cond.Value) Then
sFormula = """" & cond & ""","
Else
sFormula = "" & cond & ","
End If
ElseIf TypeName(cond) = "String" Then
sFormula = """" & cond & ""","
Else
sFormula = "" & cond & ","
End If
sFormula = "MAX(IF(" & rng1.Address & op & sFormula & rng2.Address & "))"
MAXIF = rng1.Parent.Evaluate(sFormula)
End Function
[/vba]

Hello Shazam, Here the results expected and the sample table

Quote:

---

Desired Results
maxs values
A = 14.02%
B = 38.93%
C = 25.18%
D = 29.64%

Min Values
A = 3.13%
B = 3.13%
C = 0.38%
D = 13.32%

---

Sample Table

Quote:

---

A 3.1
A 8.7
A 10.1
A 13.7
A 14.0
B 3.1
B 14.4
B 38.9
B 38.9
C 0.4
C 12.2
C 25.2
D 13.3
D 15.1
D 17.3
D 24.7
D 29.6

---

Thanks XLD, let me check and revert back...

Have you try using Database Functions? Like DMAX and DMIN because these are non arrays solutions.

Look at the sample workbook below.

Quote:

---

Originally Posted by surya prakash

Wondering if there are any non-array formulaes in Excel; I have used Sumif function and countif function to get averages.
are there MaxIF and MinIF ?

thanks

---

I know this is a old post but never to late.

=SUMPRODUCT(MAX((A1:A17=G2)*(B1:B17)))

=SUMPRODUCT(MIN((A1:A17=G2)*(B1:B17)))

The problem with that is that the MIN formula returns zero if An=G2 but Bn is blank, which is rarely wanted, and an extra test for blank doesn't help, whereas

=MIN(IF((A1:A17=G2)*(B1:B17<>""),B1:B17))

works great.

Anyway, SP is an array formula, it just isn't array entered.

Quote:

---

Originally Posted by xld

The problem with that is that the MIN formula returns zero if An=G2 but Bn is blank, which is rarely wanted, and an extra test for blank doesn't help, whereas

=MIN(IF((A1:A17=G2)*(B1:B17<>""),B1:B17))

works great.

Anyway, SP is an array formula, it just isn't array entered.

---

Yes you're right I should've test it. How about evaluates the MIN function?

Like this...

=SUMPRODUCT(MIN((10^5-(A1:A17=G2)*(B1:B17<>"")*(10^5-B1:B17))))

Two problems that I see.

If none of A1:A17 equals G2, it returns 100,000.

It is getting a tad contrived don't you think from where we started, not exactly self-documenting.

Quote:

---

Originally Posted by xld

Two problems that I see.
It is getting a tad contrived don't you think from where we started, not exactly self-documenting.

---

Your right.:)

I know array entering is naff, why Excel can't figure it out for itself, but sometimes you just have to go with it :dunno