Conditional Count

  • Thread starter Thread starter Charles Deng
  • Start date Start date
C

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
 
Charles said:
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))
Click to expand...

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))
 
Back
Top