analysing frequency of entries (averages, sums etc.)

Hi all


Having trouble with analysing this data. Can't determine which functions, if any, would identify the following examples for the attached sheet

Find out the average number of mental health vulnerability per Entry number(1,1,0,1,0 so answer would be 0.6)
Find out how many times a vulnerability appears. i.e. learning disability appears 4 times but there are 3 in total for each unique entry
Find out of the mental health vulnerabilities, how many ended up with Brief intervention (answer 0.5)

If you could ppoint me in the right direction, cheers

Try

=COUNTIF(C2:C16,"mental health")/SUMPRODUCT(--(A2:A16<>"")/COUNTIF(A2:A16,A2:A16&""))

=SUM(--(FREQUENCY(IF(C2:C16="learning disability",MATCH(A2:A16,A2:A16,0)),ROW(INDIRECT("1:"&ROWS(A2:A16))))>0))

=SUMPRODUCT(--(C2:C16="mental health"),--(D2:D16="Brief intervention"))/COUNTIF(C2:C16,"mental health")

The second is an array formula.

Oh thanks Bob.

Does anyone know whether there is a better program, to perform these type of analysis?

Better? Than Excel? Are you kidding?