I've got a REAL puzzler for you experts out there!
I'm pretty sure I've seen people use Excel to create a dynamic form. I've been trying different formulas for days to no avail. This is regarding schools across the US. I've removed the identifying information to protect the privacy of those schools.
Attachment 564346
I've used Data Validation to only allow data points present for each column on the Main tab of the workbook. (e.g., Year End in Form!K5 only permits 2013, 2014, 2015, 2016, 2017, or 2018.
Now, people can choose what they'd like to specify from Form!E5 through Form!K5.
I'd like to write a formula that considers their choices, and returns: a) a median for those values in the array (which are found on the Main tab), and b) an Average for those values in the array (which are found on the Main tab).
Attachment 564349
Example:
form2.PNG
Here's my working thought process:
1. The above photo means I don't care what City the school is in, as well as the Type of school, and whether or not they are a Member of our program. I DO want to count ONLY schools in the Southeast region that are between 201-300 in Size, and only for the 2018 school Year.
2. I DON'T want to consider any school that has "0" (zero) in column H of the Main tab.
3. I'd like the Median and Average for these schools.
4. The following formulas are grossly wrong, but it's all I can think up right now.
=MEDIAN(IFS(Main!$D:$D=E5,Main!$E:$E=F5,Main!$F:$F=G5,Main!$G:$G=H5,Main!$H:$H=I5,Main!$I:$I=J5,Main!$J:$J=K5, Main!K:K))
=AVERAGEIFS(Main!K:K,Main!$D:$D,E5,Main!$E:$E,F5, Main!$F:$F,G5, Main!$G:$G,H5,Main!$H:$H,I5,Main!$I:$I,J5,Main!$J:$J,K5)
5. I know I should add "Main!K:K<>0" in order to preclude the entries that have zero entered into Main!K, but I don't know how.
I'd love any assistance, please! If I can get this, it'll save me weeks of work.
Bookmarks