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

[cvercrus]

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

[daffodil11]

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.

[daddylonglegs]

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

[cvercrus]

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