Conditional Count

[Charles Deng]

I have a table with two variables: Days_Between_Tests and
Gain. I need to calculate the average gain for those
students whose Days_Between_Tests larger than 0. I wrote
the following coding but got Error. Could you tell me what
is wrong with my codings:

=Sum([Gain])/Abs(Sum([Days_Between_Tests]>0))

Thanks a lot!


Charles

 

[Marshall Barton]

I have a table with two variables: Days_Between_Tests and
Gain. I need to calculate the average gain for those
students whose Days_Between_Tests larger than 0. I wrote
the following coding but got Error. Could you tell me what
is wrong with my codings:

=Sum([Gain])/Abs(Sum([Days_Between_Tests]>0))

You can not use an aggregate function (Count, Sum, Avg, etc)
as an argument to another aggregate function.

Off the top of my head, I think this will do what you ask:

=Avg(IIf([Days_Between_Tests]>0, Gain, Null))