PDA

View Full Version : Max of Column if meets Criteria



MrRhodes2004
11-20-2006, 01:08 PM
Group!

I have a large group of data in the attached file. I would like to determine the maximum value of one column based on the criteria of another column.

Column 'F' has values that run up to 25 or so. Column 'I' has data based on the information based in column 'F'. How do I determine the maximum value of Column 'I' for those that fall within the range of 10 to 20?

The brute force method has told me the answer is 0.82. This was done by sorting the data then finding the maximum manually. Not a very efficient way to do things.

Second questions is about Sumif:
Sum if has a criteria ">10". What if you would like to sum the values of a group if the values are between 10 and 20?

CBrine
11-20-2006, 01:20 PM
I've added an array formula that will do as you requested to the attached copy.

HTH
Cal

PS- You need to use ctrl-shift enter to finish the formula.

Bob Phillips
11-20-2006, 03:05 PM
=MAX(IF((F3:F86>10)*(F3:F86<20),I3:I86))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter.

Shazam
11-20-2006, 05:13 PM
Non-array approach.

=MAX(INDEX((F3:F86>10)*(F3:F86<20)*(I3:I86),0))

Hit enter.


Hope it helps!

MrRhodes2004
11-22-2006, 08:17 AM
Non-array approach.

=MAX(INDEX((F3:F86>10)*(F3:F86<20)*(I3:I86),0))

Hit enter.


Hope it helps!


Thank you very much! This works as needed.

Now, I have been trying to simplify my life by creating a function MaxInRange without much luck. My code has gone through several revisions and is is in ugly disrepair and looks something like:
Function MaxInRange(SearchRange As Range, RangeMaxValues As Range, _
StartSearch As Long, EndSearch As Long)
Dim rSearch As Range
Dim rMax As Range
Dim lStart As Long
Dim lEnd As Long
Dim test
Set rSearch = SearchRange
Debug.Print rSearch.Address
Set rMax = RangeMaxValues
Debug.Print rMax.Address
lStart = StartSearch
Debug.Print lStart
lEnd = EndSearch
Debug.Print lEnd
test = Application.WorksheetFunction.Index(Worksheets("Sheet1").Range("F9:F20") > 25, 0)
Debug.Print test
'MaxInRange = Application.WorksheetFunction.Index rSearch > rStart

'Max (Index((rSearch > rStart) * (rSearch < rEnd) * (rMax), 0))

End Function


Sub testmaxin()
Call MaxInRange(ActiveSheet.Range("F9:F20"), ActiveSheet.Range("I9:I20"), _
ActiveSheet.Range("R26"), ActiveSheet.Range("R27"))
'MsgBox MaxInRange
End Sub

I have gotten a gamut of errors because this is the first time I have tried to work with the Index function in VBA. The sub was created to try and text the function.

Any suggestions or information as how to write this better would be helpful. The books that I have don't deal with Index very well as a worksheet function.

Or if this is a really bad way of approaching this, let me know that too!

Thanks a bunch!

mdmackillop
11-22-2006, 02:00 PM
Sub testmaxin()
MsgBox MaxInRange("F3:F86", 10, 20, "I3:I86")
End Sub

Function MaxInRange(SearchRange As String, RangeMinValues As Long, RangeMaxValues As Long, _
StartSearch As String)
MaxInRange = Evaluate("MAX(INDEX((" & SearchRange & ">" & RangeMinValues & _
")*(" & SearchRange & "<" & RangeMaxValues & ")*(" & StartSearch & "),0))")
End Function