X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> How To Find The Maximum Value In A Column With Multiple Conditions?, Office 2007    
 
   
Javiator
post Feb 22 2012, 12:25 PM
Post #1

UtterAccess Addict
Posts: 270
From: USA



Hello,
need to find the maximum value in a column of a worksheet, where certain values are present in other columns. For example, I would need to find the maximum value in column F among rows where column C = "John Doe", column D = "Assessment", and column E = "1/1/2012". Could someone please suggest the proper strategy to accomplish this?
Thanks so much for your assistance!
Go to the top of the page
 
+
MedicalServices
post Feb 22 2012, 04:36 PM
Post #2

UtterAccess Enthusiast
Posts: 55



I was browsing around and saw this code by John Walkenbach. I thought it may be of some use to you regarding your question. It uses the .Find function to go to the maximum value...
!--c1-->
CODE
Sub GoToMax()
'   Activates the cell with the largest value
    Dim WorkRange as Range
'   Exit if a range is not selected
    If TypeName(Selection) <> "Range" Then Exit Sub
'   If one cell is selected, search entire worksheet;
'   Otherwise, search the selected range
    If Selection.Count = 1 Then
        Set Workrange = Cells
    Else
        Set Workrange = Selection
    End If
'   Determine the maximum value
    MaxVal = Application.Max(Workrange)    
'   Find it and select it
    On Error Resume Next
    Workrange.Find(What:=MaxVal, _
        After:=Workrange.Range("A1"), _
        LookIn:=xlValues, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False _
        ).Select
    If Err <> 0 Then MsgBox "Max value was not found: " & MaxVal
End Sub

If you sorted your sheet by "C", "D", then "E" that should group them to where you can make a selection to pass to the 'WorkRange' variable.
Go to the top of the page
 
+
dflak
post Feb 23 2012, 02:55 PM
Post #3

Utter Access VIP
Posts: 4,841
From: North Carolina



It gets ugly.
Here is the single criteria version of MAXIF(). Enter it as an array formula (CTRL-SHIFT-ENTER).
=MAX(IF($F$1:$F$15=A12,IF(NOT(ISBLANK($G$1:$G$15)),$G$1:$G$15)))
In this case, the thing you want the maxium of is in Range G1:G15. What you want to evaluate is in Range F1:F15. What you want to match to is in Cell A12.
To extend the formula to more criteria, you would have to nest the first if statement.
=MAX(IF($F$1:$F$15=A12,IF(SecondRange = Second Criteria, IF (Third Range = Third Criteria , ...IF(NOT(ISBLANK($G$1:$G$15)),$G$1:$G$15)))
Oleave it as an exercise to the student to figuer out where to close all those extra parenthesis. I think they'd come at the end. Any formula that closes with six parenthesis can't be pretty.
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 21st December 2014 - 03:48 PM