• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count number of Schools having Student Teacher ratio more than 30

M

maniknandi

Member
Hello Everyone,

schcd schcat Nteacher Tstudent
19012903901 1 2 44
19012904001 3 5 95
19012904003 1 1 45
19012904101 1 5 67
19012904102 2 2 34
19012904201 2 3 49
19012904301 3 4 37
19012904401 1 0 39
19012904501 5 20 714
19012904502 4 2 54
19012904503 1 2 34
19012904601 4 4 124
19012904602 1 2 25
19012904603 1 2 67

We have some school data. schcd is school code, schcat is school category, Nteacher is number of teacher and Tstudent is total student. Now we want to find number schools under category "1" having Student Teacher ratio more than 30.
 
Hi maniknandi,

Use below formula. I had use the refrences of the file that you had uploaded.

Code: 
=SUM(IF(B2:B15=1,IF(C2:C15<>0,IF((D2:D15/C2:C15)>30,1))))

Regards!
 
Or you can use the below formula to be more specific in the results and then drag then down to your list:
=IF(B2=1,IF(C2<>0,IF(D2/C2>30,"In Ratio","Out of Ratio"),"No Teachers"),"Not in Category")

P.S.:-Not an array formula, just a regular one
 
Thank you so much Somendra and Abhijeet,

Till now it is working fine, but I am preparing a dashboard and have almost 98000 schools. If I find any problem will get back to the forum.

Best regards,
 
@maniknandi Please use this array formula

=SUMPRODUCT((SchMCData[schcat]=1)*IFERROR((SchMCData[Tstudent]/SchMCData[Tclrooms]>30),0))

CTRL+Shift+Enter it to get the result.

If you want to add any specific extra conditions to it, you can add them inside SUMPRODUCT.
 
You must log in or register to reply here.
Back
Top