+ Reply to Thread
Results 1 to 4 of 4

HELP!! I need to find MIN, MAX, AVG... based on multiple criteria - what is the best way?

  1. #1
    Registered User
    Join Date
    01-25-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    19

    HELP!! I need to find MIN, MAX, AVG... based on multiple criteria - what is the best way?

    First and foremost, I know that all my stats can be easily identified using PIVOT tables (used to guide me on my number for COUNTIFS formulas), but I will be pulling this report every 2 weeks AND will have to provide the same charts each time. I have already created a macro to create my report template, now I'm just creating the formulas so that when I run my macro off my report, it will generate the numbers based on my formulas.

    I have spent days researching IF, SUMPRODUCT, INDEX, MATCH, COUNTIF to use with MIN, MAX, AVERAGE, MEDIAN and MODE as it relates to the following criteria, but just can't get what I need; if mentioned on the web, forums, tutorials, I've looked into it! If you refer to the "Enrollment Charts" tab from my source file, you will see what I am trying to accomplish.

    Any guru's out there that can help with my dilemma would be most appreciated! Thx in advance!!! Cris


    CRITERIA FOR A SPECIFIC COHORT TO DERIVE MIN, MAX, AVERAGE, MEDIAN and MODE:

    Enter Term: (Col B)= "13F*" or "13U*" -- several term prefixes, but need those that start with "13F" and "13U" (original terms looks like 13Faz, 13FAJ, 13U2z, 13U2Z, etc. (13F prefix for Fall; 13U prefix for Summer terms)
    Cr_Att: (H) >= 12 -- represents Full-time
    Type: (J) = "TU" -- represents a Traditional student
    Enroll_Stat: (K) = "FF" -- represents First-time freshman
    Sx: (T) = "F" or "M"
    Athlete: (X) = "" -- represents (BLANK); not an athlete

    HS_GPA: (AL) > 0 AND <= 3.20 -- NEED MIN, MAX, AVERAGE, MEDIAN and MODE - based on above criteria
    ACT: (AM) > 0 AND <= 27 -- NEED MIN, MAX, AVERAGE, MEDIAN and MODE - based on above criteria
    SAT: (AN) > 0 AND <= 1220 -- NEED MIN, MAX, AVERAGE, MEDIAN and MODE - based on above criteria
    Attached Files Attached Files
    Last edited by cvercrus; 08-12-2013 at 04:20 PM. Reason: added column identification

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: HELP!! I need to find MIN, MAX, AVG... based on multiple criteria - what is the best w

    You could probably get by nesting a number of IFs.

    =MIN(IF(OR(CRITERIARANGE1="13F*",CRITERIARANGE1="13U*"),IF(CRITERIARANGE2=CRITERIA2, ...(A LOT MORE IFS, RANGES, CRITERIA).......IF(SATSCORE>0,IF(SATSCORE<=1220,SATSCORES))))))))))

    Repeat as appropriate for all 45 cells, altering formula to accommodate each range.

    I believe each of these formulas would need to be entered as an array using Control+Shift+Enter instead of just hitting enter.

  3. #3
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: HELP!! I need to find MIN, MAX, AVG... based on multiple criteria - what is the best w

    You have this formula in L15

    =COUNTIFS('Student Life Report'!$B$2:$B$10000,"13F*",'Student Life Report'!$J$2:$J$10000,"TU",'Student Life Report'!$K$2:$K$10000,"FF",'Student Life Report'!$X$2:$X$10000,"",'Student Life Report'!$H$2:$H$10000,">=12",'Student Life Report'!$T$2:$T$10000,"F",'Student Life Report'!$AL$2:$AL$10000,"<=3.20",'Student Life Report'!$S$2:$S$10000,"=17")+COUNTIFS('Student Life Report'!$B$2:$B$10000,"13U*",'Student Life Report'!$J$2:$J$10000,"TU",'Student Life Report'!$K$2:$K$10000,"FF",'Student Life Report'!$X$2:$X$10000,"",'Student Life Report'!$H$2:$H$10000,">=12",'Student Life Report'!$T$2:$T$10000,"F",'Student Life Report'!$AL$2:$AL$10000,"<=3.20",'Student Life Report'!$S$2:$S$10000,"=17")

    Rather than repeating the COUNTIFS function for 13U and 13F you can simplify with this version

    =SUM(COUNTIFS('Student Life Report'!$B$2:$B$10000,{"13F*","13U*"},'Student Life Report'!$J$2:$J$10000,"TU",'Student Life Report'!$K$2:$K$10000,"FF",'Student Life Report'!$X$2:$X$10000,"",'Student Life Report'!$H$2:$H$10000,">=12",'Student Life Report'!$T$2:$T$10000,"F",'Student Life Report'!$AL$2:$AL$10000,"<=3.20",'Student Life Report'!$S$2:$S$10000,17))

    ....but for MIN/MAX/MEDIAN etc. you need a revised syntax - the main difference is that you can't use a wildcard so you need to use LEFT function - try this version for MAX GPA given those conditions

    =MAX(IF((LEFT('Student Life Report'!$B$2:$B$10000,3)={"13F","13U"})*('Student Life Report'!$J$2:$J$10000="TU")*('Student Life Report'!$K$2:$K$10000="FF")*('Student Life Report'!$X$2:$X$10000="")*('Student Life Report'!$H$2:$H$10000>=12)*('Student Life Report'!$T$2:$T$10000="F")*('Student Life Report'!$AL$2:$AL$10000<=3.20)*('Student Life Report'!$S$2:$S$10000=17),'Student Life Report'!$AL$2:$AL$10000))

    confirmed with CTRL+SHIFT+ENTER
    Audere est facere

  4. #4
    Registered User
    Join Date
    01-25-2013
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: HELP!! I need to find MIN, MAX, AVG... based on multiple criteria - what is the best w

    Thx daddylonglegs! The reason for my lengthy formula is because I cannot seem to figure out how to put the array brackets around the "13F*","13U*" (array in the middle of a formula) as you have shown in the 1st formula the =SUM(COUNTIFS...(the shorter version of mine). I tried highlighting the two and did CTRL+SHIFT+ENTER, but it puts the array around the WHOLE formula. How did you get the array to show in the middle of the formula? I've tried everything.

    I do however see that in the MAX formula that you provided, you have an = before the array. What are the differences in the two formulas; just trying to wrap my head around this? As you can figure, I've not worked with arrays like you have entered. If you can guide me on what I am doing wrong would be most appreciated for I would really like to clean up my formula as you have it laid out and much easier to read!

    Lastly, I'm just now getting back to my project I was working on when I put out this request. I'm grateful and excited about trying your MAX formula. I'm crossing my fingers that I get numbers to appear. Will let you know! Thx much!!! Cris

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Formula to find MAX value based on multiple criteria
    By coach.32 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-27-2014, 02:04 AM
  2. Find cell and paste based on multiple (two) criteria
    By conlinuk in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-28-2012, 12:25 PM
  3. Find rows and delete them based on multiple criteria
    By kostas in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-31-2012, 11:59 AM
  4. Find Matching Value based on multiple criteria
    By Sisonkemdala in forum Excel General
    Replies: 2
    Last Post: 05-09-2011, 07:26 AM
  5. Find the sum of a range based on multiple criteria
    By Jonsocks in forum Excel General
    Replies: 11
    Last Post: 01-13-2011, 07:20 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1