SOLVED

excel for statistics. ¿how to compute some descriptive statistics with case selection?

New Contributor

I'm teaching statistics using Excel.

I want to compute some descriptive statistic, for instance, the first quartile. The following data shows sex (male of female) and age (in years) of the members of a population. It is stored in a excel table called "mydata". I want to compute the first quartile of age, but only for men.

I write the formulae =quartile.exc(if(mydata[sex]="M";mydata[age]);1)

This works fine with excel 365. But the problem is that computers at university classroom have office professional plus 2016 and it does not work there.
Please, does anyone have any idea of how to do this with a formula that works in office 2016?
(It can be done filtering the data, copy paste the data for men and compute the quartile with the pasted data, but it is impractical if there is a big amount of data)example.png 

 

Thank you very much

 

2 Replies
best response confirmed by franpere (New Contributor)
Solution

@franpere  wrote:  ``this works fine with excel 365. But the problem is that computers at university classroom have office professional plus 2016 and it does not work there.``

 

Array-enter the formula (in Excel 2016 at least) by pressing ctrl+shift+Enter.

 

Office 365 Excel does not require that.  I do not know if it allows, nevertheless.

@Joe User Thank you so much for your answer. It solved the problem and works fine. You have help me a lot. Thank you!