MIN/MEDIAN/MAX/MODE/AVERAGE Functions with Criteria

/BUMP still need assistance with this issue

Your file opened with the cursor in J28, where you have this "formula":

|IF(D28=0,0,(SUM(COUNTIFS('Group _ Details'!$X$6:$X$31, {"Employment", "Apprenticeship", "Further Learning", "Own Business", "Other"}, 'Group _ Details'!$R$6:$R$31, "=Mixed/Asian")
+COUNTIFS('Group _ Details'!$X$6:$X$31, "=Apprenticeship", 'Group _ Details'!$R$6:$R$31, "=Mixed/Asian")
+COUNTIFS('Group _ Details'!$X$6:$X$31, "=Further Learning", 'Group _ Details'!$R$6:$R$31, "=Mixed/Asian")
+COUNTIFS('Group _ Details'!$X$6:$X$31, "=Own Business", 'Group _ Details'!$R$6:$R$31, "=Mixed/Asian")
+COUNTIFS('Group _ Details'!$X$6:$X$31, "=Other", 'Group _ Details'!$R$6:$R$31, "=Mixed/Asian"))
/SUM(COUNTIFS('Group _ Details'!$X$6:$X$31, {"Employment","Apprenticeship","Further Learning","Own Business","Other","Jobseeking","Unknown"}, 'Group _ Details'!$R$6:$R$31, "=Mixed/Asian")
+COUNTIFS('Group _ Details'!$X$6:$X$31, "=Apprenticeship", 'Group _ Details'!$R$6:$R$31, "=Mixed/Asian")
+COUNTIFS('Group _ Details'!$X$6:$X$31, "=Further Learning", 'Group _ Details'!$R$6:$R$31, "=Mixed/Asian")
+COUNTIFS('Group _ Details'!$X$6:$X$31, "=Own Business", 'Group _ Details'!$R$6:$R$31, "=Mixed/Asian")
+COUNTIFS('Group _ Details'!$X$6:$X$31, "=Other", 'Group _ Details'!$R$6:$R$31, "=Mixed/Asian")
+COUNTIFS('Group _ Details'!$X$6:$X$31, "=Jobseeking", 'Group _ Details'!$R$6:$R$31, "=Mixed/Asian")
+COUNTIFS('Group _ Details'!$X$6:$X$31, "=Unknown", 'Group _ Details'!$R$6:$R$31, "=Mixed/Asian")))

I've told you before that you do not need the = sign in the criteria, so "=Mixed/Asian" can become "Mixed/Asian", and you can remove those other = signs.

It is not very clear what you are trying to do here, as you've got a big COUNTIFS expression divided by the same COUNTIFS expression.

The criteria for the second part of the COUNTIFS all refer to:

'Group _ Details'!$R$6:$R$31, "=Mixed/Asian"

but you have "Mixed/Asian" in cell C28, so you could just refer to that cell instead of the text explicitly, and that will help you in copying the formula down.

I don't know what values you can have within the range 'Group _ Details'!$X$6:$X$31, but it strikes me that maybe you want to take any value, in which case that part of the formula is redundant.

Hope this helps.

Pete

Hi Pete, since this was posted I rehashed the long formulae into shorter ones.

I have updated the attached file as a result.

How would I go about using the MIN/MAX formulae in this case?

The functions MINIFS and MAXIFS were introduced in XL version 2016, so you don't have access to those if your profile is correct.

However, you can still get the equivalent by using an array formula.

Where is the formula going (i.e. which cell), what are the criteria to be applied, and which data do you want to get the MIN/MAX from ?

Pete

Unfortunately, I do not have those formulae on my version. The MIN will be in 'Group _ Data Report'!D66, MEDIAN in 'Group _ Data Report'!D67, MAX in 'Group _ Data Report'!D68, AVERAGE in 'Group _ Data Report'!D69 and MODE in 'Group _ Data Report'!D70.

Their target is 'Group _ Details'!$G$6:$G$31 and the criteria a data input should only be considered if the respective 'Group _ Details'!$B$6:$B$31 is listed as {"Achiever", "Units", "Nonachiever", "*Learner*"}

/BUMP this thread as I'd like to resolve this today

An array formula for the MIN

=MIN(IF(ISNUMBER(SEARCH({"Achiever","Units","Nonachiever","*Learner*"},DATable1[STAGE])),DATable1[AGE]))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

The same method applies to all of the functions you listed, just change MIN to MEDIAN, MAX etc. Note that MODE will return #N/A! if you don't have at least 2 people of the same age that match the criteria.

Originally Posted by jason.b75

An array formula for the MIN

=MIN(IF(ISNUMBER(SEARCH({"Achiever","Units","Nonachiever","*Learner*"},DATable1[STAGE])),DATable1[AGE]))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

The same method applies to all of the functions you listed, just change MIN to MEDIAN, MAX etc. Note that MODE will return #N/A! if you don't have at least 2 people of the same age that match the criteria.

Awesome, exactly what I was looking for.

/SOLVED Thanks