Extracting max & min values with a condition

Printable View

05-09-2007, 10:19 PM
surya prakash

Extracting max & min values with a condition

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

Quote:

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:

Quote:

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
05-10-2007, 02:34 AM
Bob Phillips

=MIN(IF(A1:A100="A"),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.
05-10-2007, 02:42 AM
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

Quote:

Originally Posted by xld

=MIN(IF(A1:A100="A"),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.
05-10-2007, 10:14 PM
surya prakash

hi can someone help please...
05-11-2007, 02:28 AM
Bob Phillips

Answer: NO
05-11-2007, 02:35 AM
Charlize

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

You could create your own one.
05-11-2007, 03:36 AM
Bob Phillips

Why, the array is perfectly good.
05-11-2007, 04:16 AM
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..
05-11-2007, 04:46 AM
Charlize

Quote:

Originally Posted by xld

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.
05-11-2007, 06:15 AM
Bob Phillips

Quote:

Originally Posted by 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..

Why, what is the problem with array formulae?
05-11-2007, 06:34 AM
johnske

Quote:

Originally Posted by xld

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 :)
05-16-2007, 12:21 AM
surya prakash

Quote:

Originally Posted by Charlize

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...
05-16-2007, 01:15 AM
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)
05-16-2007, 01:22 AM
surya prakash

Xld, what a speedy response !

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

Thanks alot
05-16-2007, 01:27 AM
Bob Phillips

What do you mean, it took me 54 minutes :)

Note, that all I do is use the worksheet code in a UDF!
05-16-2007, 01:49 AM
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%

Quote:

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%
05-16-2007, 01:56 AM
surya prakash

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
05-16-2007, 02:03 AM
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%
05-16-2007, 02:13 AM
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)
05-16-2007, 09:31 AM
Shazam

Quote:

Originally Posted by 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)

May I see a sample of your workbook along with the expected results?
05-16-2007, 11:37 AM
Bob Phillips

[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]
05-16-2007, 09:40 PM
surya prakash

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
05-16-2007, 09:47 PM
surya prakash

Thanks XLD, let me check and revert back...
05-17-2007, 11:06 AM
Shazam

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

Look at the sample workbook below.
03-03-2008, 05:36 PM
Shazam

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)))
03-04-2008, 01:55 AM
Bob Phillips

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.
03-04-2008, 06:52 AM
Shazam

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))))
03-04-2008, 07:32 AM
Bob Phillips

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.
03-04-2008, 08:33 AM
Shazam

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.:)
03-04-2008, 09:43 AM
Bob Phillips

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

All times are GMT -7. The time now is 03:29 AM.