Counting clients and averaging client scores with different conditions (age, area, date)

[Vera1234]

Hi everyone!

I am attaching a workbook that includes all of the details of my question, but basically, I am trying to write two different formulas (described in the orange and pink boxes in the workbook) that will generate the number of clients given different conditions, as well as the average scores of clients given different conditions. Everything is outlined and described thoroughly in the workbook but please let me know if you have any questions!

Thank you so much!

Vera

[amit.wilson]

V,

try this in for 'Client n' in I10:

Please Login or Register  to view this content.

And this for 'Domain 1' in C10:

Please Login or Register  to view this content.

Cheers

[José Augusto]

Hi
You can see the new sheet 'timepoints' I modified quite a few things. So you can organize information more easily.
As for the formulas that you ask, to obtain the means (using the new sheet) use the following formula in C10 and copy it to the range C10:H14

Please Login or Register  to view this content.

For the Client n in I10 use in the formula and copy it to the range I10:I14

Please Login or Register  to view this content.

See the file

[Vera1234]

Hello Amit and Jose! Thank you so much for your replies The formulas you created are great--the client count formulas have been working for me. However, I am still having trouble with the average scores formula. It seems that, when I plug in Child/Adolescent or one of the Area #s, it will take the average of those scores, but treat the other scores as zeros, so the averages I'm getting are much lower than they should be.

For example, if I plug in Area # 6, date range 6/1/2016-6/30/16, and Age Group Child, the average score I should get for Domain 1 is 1.67, but I'm getting 0.30, because I believe the formula is treating the rest of the scores as zeros and driving the average way down. Does that make sense? Any idea how I can prevent this?

Thank you again for your help!!

[José Augusto]

Hi
I have some inconsistency in formula to define Client n.
The correction for I10:I14 - Formula in I10 and copy down

Please Login or Register  to view this content.

The correction for C10:H14 - Formula in C10 and copy down and forward

Please Login or Register  to view this content.

I delete the Date Complete for Clients that has not scores!
See the new file