Extracting max & min values with a condition

[surya prakash]

Hi,
Wondering if it possible to get the maximum, minimum and average values from the table

Descp Max Min Avg
A 15 10 12.50
B 26 21 23.50
C 35 31 33.00

Will be thankful for any feedback...

Table:

Name Nos
A 10
A 11
A 12
A 13
A 14
A 15
B 21
B 22
B 23
B 24
B 25
B 26
C 31
C 32
C 33
C 34
C 35

[Bob Phillips]

=MIN(IF(A1:A100="A&quot,B1:B100)

etc.

which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter.Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually.When editing the formula, it must again be array-entered.

[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

=MIN(IF(A1:A100="A&quot,B1:B100)

etc.

which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter.Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually.When editing the formula, it must again be array-entered.

[surya prakash]

hi can someone help please...

[Bob Phillips]

Answer: NO

[Charlize]

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

You could create your own one.

[Bob Phillips]

Why, the array is perfectly good.

[surya prakash]

just exploring the possibility; If it is possible to get the max and min values without an array formula, I would prefer it.

thanks again..

[Charlize]

Why, the array is perfectly good.

I know that, but if he dont wants to use an array, he will have to create his own MaxIf and MinIf.

[Bob Phillips]

just exploring the possibility; If it is possible to get the max and min values without an array formula, I would prefer it.

thanks again..

Why, what is the problem with array formulae?

[johnske]

Why, what is the problem with array formulae?

possibly can't understand it cos of the superfluous characters and smiley Bob - try wrapping it in [code] tags

[surya prakash]

I know that, but if he dont wants to use an array, he will have to create his own MaxIf and MinIf.

Thank you all for all the responses; I was away and could not respond any of the post; sorry for that.

Charlize, thank you for the idea; wondering if you can help me build Maxif and MinIf function in VBa?

thanks again...

[Bob Phillips]

Pointless, but what the heck

[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) = "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]
and use llike

=MAXIF(D1:D6,"<",4)

or

=MAXIF(F1:D6,"=","abc",F1:F6)

[surya prakash]

Xld, what a speedy response !

Let me apply your code on my test problem and revert back asap...

Thanks alot

[Bob Phillips]

What do you mean, it took me 54 minutes

Note, that all I do is use the worksheet code in a UDF!

[surya prakash]

Hi Xld,
Many thanks for your response.
Can you please have a look at the table below:

Sample Table.

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%

A13.69%A8.73%A3.13%B38.93%B14.37%D13.32%D15.14%D24.68%C25.18%C0.38%C12.18%A10.10%A14.02%D29.64%D17.34%B38.93%B0.38%

[surya prakash]

Sample table:

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

[Bob Phillips]

There was a spurious comma crept into the code

[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) = "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]

but that apart

=maxif($A$1:$A$14,"=","A",$B$1:$B$14)

gives 14.02%

=maxif($A$1:$A$14,"=","B",$B$1:$B$14)

gives 38.93%

[surya prakash]

Hello XLD,
I am trying to parameterize the input "A, B, C & D"
these values are entered in cell G2.

But I am getting %Name Error, can you please check?

Formulae that I have used is:
=maxif($A$1:$A$17,"=",G2,$B$1:$B$17)

[Shazam]

Hello XLD,
I am trying to parameterize the input "A, B, C & D"
these values are entered in cell G2.

But I am getting %Name Error, can you please check?

Formulae that I have used is:
=maxif($A$1:$A$17,"=",G2,$B$1:$B$17)

May I see a sample of your workbook along with the expected results?