PDA

View Full Version : Extracting max & min values with a condition



surya prakash
05-09-2007, 10:19 PM
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
05-10-2007, 02:34 AM
=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.

surya prakash
05-10-2007, 02:42 AM
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"),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
05-10-2007, 10:14 PM
hi can someone help please...

Bob Phillips
05-11-2007, 02:28 AM
Answer: NO

Charlize
05-11-2007, 02:35 AM
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 ?

thanksYou could create your own one.

Bob Phillips
05-11-2007, 03:36 AM
Why, the array is perfectly good.

surya prakash
05-11-2007, 04:16 AM
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
05-11-2007, 04:46 AM
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
05-11-2007, 06:15 AM
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
05-11-2007, 06:34 AM
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
05-16-2007, 12:21 AM
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
05-16-2007, 01:15 AM
Pointless, but what the heck



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

and use llike

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

or

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

surya prakash
05-16-2007, 01:22 AM
Xld, what a speedy response !

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

Thanks alot

Bob Phillips
05-16-2007, 01:27 AM
What do you mean, it took me 54 minutes :)

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

surya prakash
05-16-2007, 01:49 AM
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%A 10.10%A14.02%D29.64%D17.34%B38.93%B0.38%

surya prakash
05-16-2007, 01:56 AM
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
05-16-2007, 02:03 AM
There was a spurious comma crept into the code



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


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
05-16-2007, 02:13 AM
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
05-16-2007, 09:31 AM
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?

Bob Phillips
05-16-2007, 11:37 AM
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

surya prakash
05-16-2007, 09:40 PM
Hello Shazam, Here the results expected and the 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%



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

surya prakash
05-16-2007, 09:47 PM
Thanks XLD, let me check and revert back...

Shazam
05-17-2007, 11:06 AM
Have you try using Database Functions? Like DMAX and DMIN because these are non arrays solutions.

Look at the sample workbook below.

Shazam
03-03-2008, 05:36 PM
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)))

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

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

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

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

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