Minifs, maxifs, averageifs, medianifs, coeffvarifs

[qwertyjjj]

I need to put in some descrptive stats.
I have used SUMIFS and COUNTIFS successfully from other posts but now I need:

MINIFS, MAXIFS, AVERAGEIFS, MEDIANIFS, COEFFVARIFS

These functions don't seem to exist in Excel so what's the best way to recreate the following formula?
=COUNTIFS(EVENTS!$E$2:$E$282,">0",EVENTS!$L$2:$L$282,"<>10",EVENTS!$I$2:$I$282,"=0",EVENTS!$C$2:$C$282,"="&Descr.Stats!B6)

 

[joeu2004]

I have used SUMIFS and COUNTIFS successfully from other posts but now I need:
MINIFS, MAXIFS, AVERAGEIFS, MEDIANIFS, COEFFVARIFS

These functions don't seem to exist in Excel so what's the best way to recreate the following formula?

=COUNTIFS(EVENTS!$E$2:$E$282,">0",EVENTS!$L$2:$L$282,"<>10",EVENTS!$I$2:$I$282,"=0",EVENTS!$C$2:$C$282,"="&Descr.Stats!B6)

AVERAGEIFS does exist in versions of Excel that support COUNTIFS. The form is similar to COUNTIFS. But you can simplify both. Assuming you want to average Events!A2:A282, write:

=AVERAGEIFS(EVENTS!$A$2:$A$282,EVENTS!$E$2:$E$282,">0",
EVENTS!$L$2:$L$282,"<>10",EVENTS!$I$2:$I$282,0,EVENTS!$C$2:$C$282,Descr.Stats!B6)

Other functions require an array-entered formula (press ctrl+shift+Enter instead of just Enter). Assuming you want the max of Events!A2:A282, write:

=MAX(IF(EVENTS!$E$2:$E$282>0,IF(EVENTS!$L$2:$L$282<>10,
IF(EVENTS!$I$2:$I$282=0,IF(EVENTS!$C$2:$C$282=Descr.Stats!B6,EVENTS!$A$2:$A$282)))))

 

[qwertyjjj]

AVERAGEIFS does exist in versions of Excel that support COUNTIFS. The form is similar to COUNTIFS. But you can simplify both. Assuming you want to average Events!A2:A282, write:

=AVERAGEIFS(EVENTS!$A$2:$A$282,EVENTS!$E$2:$E$282,">0",
EVENTS!$L$2:$L$282,"<>10",EVENTS!$I$2:$I$282,0,EVENTS!$C$2:$C$282,Descr.Stats!B6)

Other functions require an array-entered formula (press ctrl+shift+Enter instead of just Enter). Assuming you want the max of Events!A2:A282, write:

=MAX(IF(EVENTS!$E$2:$E$282>0,IF(EVENTS!$L$2:$L$282<>10,
IF(EVENTS!$I$2:$I$282=0,IF(EVENTS!$C$2:$C$282=Descr.Stats!B6,EVENTS!$A$2:$A$282)))))

Great...that works. However, for MIN it just gives a zero even though I have no zeros in the data?

Also, can I use the same formula for MEDIAN?

 

[joeu2004]

Other functions require an array-entered formula (press ctrl+shift+Enter instead of just Enter). Assuming you want the max of Events!A2:A282, write:

=MAX(IF(EVENTS!$E$2:$E$282>0,IF(EVENTS!$L$2:$L$282<>10,
IF(EVENTS!$I$2:$I$282=0,IF(EVENTS!$C$2:$C$282=Descr.Stats!B6,EVENTS!$A$2:$A$282)))))

for MIN it just gives a zero even though I have no zeros in the data?

Most common mistake is pressing just Enter instead of ctrl+shift+Enter. Be sure the entire formula appears in the Formula Bar surrounded by curly braces ( {=formula} ).

Also, of course you might need to change Events!$A$2:$A282 to whatever range you want the minimum of. That was a wild guess on my part, since you gave no indication of what the range should be

Also, can I use the same formula for MEDIAN?

Sure. Simply replace the word MAX with MEDIAN, STDEV, STDEVP, etc.

[EDIT] I don't know what COEFFVAR is, but it sounds like PEARSON or CORREL. Since those have two array parameters, the array-entered conditional form would be very different. If you don't know what to do, please provide a concrete example that we can modify to demonstrate.

But again, you might need to change one or more ranges to fit the situation. And again, be sure to press ctrl+shift+Enter instead of just Enter.

 

[Peter_SSs]

Great...that works. However, for MIN it just gives a zero even though I have no zeros in the data?

My guess is that you have blanks in the range which would count as zeros.
You would need to insert another term in the formula to say column A values <>"".

Also, can I use the same formula for MEDIAN?

How hard would it be to give it a try and see?


A non CSE-entered alternative for the MAXIFS and MINIFS, if you are using Excel 2010 or later, would be as follows.
I've used smaller ranges to test so you'll need to adjust those.

MAX:
=AGGREGATE(14,6,EVENTS!$A$3:$A$15/((EVENTS!$E$3:$E$15>0)*(EVENTS!$L$3:$L$15<>10)*(EVENTS!$I$3:$I$15=0)*(EVENTS!$C$3:$C$15=Descr.Stats!$B$6)),1)

MIN (note the additional term to exclude blanks):
=AGGREGATE(15,6,EVENTS!$A$3:$A$15/((EVENTS!$A$3:$A$15<>"")*(EVENTS!$E$3:$E$15>0)*(EVENTS!$L$3:$L$15<>10)*(EVENTS!$I$3:$I$15=0)*(EVENTS!$C$3:$C$15=Descr.Stats!$B$6)),1)

There isn't a similar alternative for MEDIAN.

 

[XOR LX]

There isn't a similar alternative for MEDIAN.

Perhaps there is after all?

Was just thinking about this and wondering whether, in general:

=MEDIAN(Range)

is identical to:

=PERCENTILE.INC(Range,0.5)

in all cases?

It seems to be, and from the brief look I've had at the description of PERCENTILE.INC I can't see anything that would suggest that using this function with a k parameter of 0.5 would not be equivalent to performing the median.

In which case, since the k parameter for PERCENTILE.INC in AGGREGATE is greater than 13, we can happily use it in such conditional constructions to obtain the median, i.e.:

=AGGREGATE(16,6,EVENTS!$A$3:$A$15/((EVENTS!$A$3:$A$15<>"")*(EVENTS!$E$3:$E$15>0)*(EVENTS!$L$3:$L$15<>10)*(EVENTS!$I$3:$I$15=0)*(EVENTS!$C$3:$C$15=Descr.Stats!$B$6)),0.5)

Regards

 

[Peter_SSs]

Perhaps there is after all?

Was just thinking about this and wondering whether, in general:

=MEDIAN(Range)

is identical to:

=PERCENTILE.INC(Range,0.5)

in all cases?

It seems to be, and from the brief look I've had at the description of PERCENTILE.INC I can't see anything that would suggest that using this function with a k parameter of 0.5 would not be equivalent to performing the median.

In which case, since the k parameter for PERCENTILE.INC in AGGREGATE is greater than 13, we can happily use it in such conditional constructions to obtain the median, i.e.:

=AGGREGATE(16,6,EVENTS!$A$3:$A$15/((EVENTS!$A$3:$A$15<>"")*(EVENTS!$E$3:$E$15>0)*(EVENTS!$L$3:$L$15<>10)*(EVENTS!$I$3:$I$15=0)*(EVENTS!$C$3:$C$15=Descr.Stats!$B$6)),0.5)

Regards

That seems a good argument to me. Good thinking, & thanks!

 

[XOR LX]

That seems a good argument to me. Good thinking, & thanks!

You're welcome!

Now, as for that COEFFVARIFS...??!

 

[Swordfishtrombone]

=MAX(IF(EVENTS!$E$2:$E$282>0,IF(EVENTS!$L$2:$L$282<>10,
IF(EVENTS!$I$2:$I$282=0,IF(EVENTS!$C$2:$C$282=Descr.Stats!B6,EVENTS!$A$2:$A$282)))))

Just to be clear, in this example, the four IF statements are being used as "conditionals" or "filters" and if they are all TRUE then the MAX function is being applied to the records in the range EVENTS!$A$2:$A$282, which is the final "True" condition of the final If.
It's just a little unfamiliar to me because it looks like we are using a range where the [value_if_true] goes...
Then the Max (or in my case the Median function) would then work against that range.
Is that all correct?
Thanks!

 

[KevCarter]

My guess is that you have blanks in the range which would count as zeros.
You would need to insert another term in the formula to say column A values <>"".

How hard would it be to give it a try and see?


A non CSE-entered alternative for the MAXIFS and MINIFS, if you are using Excel 2010 or later, would be as follows.
I've used smaller ranges to test so you'll need to adjust those.

MAX:
=AGGREGATE(14,6,EVENTS!$A$3:$A$15/((EVENTS!$E$3:$E$15>0)*(EVENTS!$L$3:$L$15<>10)*(EVENTS!$I$3:$I$15=0)*(EVENTS!$C$3:$C$15=Descr.Stats!$B$6)),1)

MIN (note the additional term to exclude blanks):
=AGGREGATE(15,6,EVENTS!$A$3:$A$15/((EVENTS!$A$3:$A$15<>"")*(EVENTS!$E$3:$E$15>0)*(EVENTS!$L$3:$L$15<>10)*(EVENTS!$I$3:$I$15=0)*(EVENTS!$C$3:$C$15=Descr.Stats!$B$6)),1)

There isn't a similar alternative for MEDIAN.

I was having the same problem needing to replace MINIFS and was going to post a question. I found this post, and it worked beautifully.

Thank you!