I'm trying to calculate the number of columns that are not null so I can accurately calculate the average of our student's grades per record. I've included an attachment from the query. Sometimes I get numbers that don't make any sense to me at all.
The expressing I used in the Not_Null column is:
Not_Null: Sum(IIf(
[A1] Is Null,0,1)+IIf(
[A2] Is Null,0,1)+IIf(
[A3] Is Null,0,1)+IIf(
[A4] Is Null,0,1)+IIf(
[A5] Is Null,0,1)+IIf(
[A6] Is Null,0,1)+IIf(
[A7] Is Null,0,1)+IIf(
[A8] Is Null,0,1)+IIf(
[A9] Is Null,0,1)+IIf(
[A10]Is Null,0,1))
The Average column is incorrect because it takes the sum of A1-A10 and divides by 10 regardless of if any 1 or more columns are null or not.
Any assistance is greatly appreciated
The expressing I used in the Not_Null column is:
Not_Null: Sum(IIf(
[A1] Is Null,0,1)+IIf(
[A2] Is Null,0,1)+IIf(
[A3] Is Null,0,1)+IIf(
[A4] Is Null,0,1)+IIf(
[A5] Is Null,0,1)+IIf(
[A6] Is Null,0,1)+IIf(
[A7] Is Null,0,1)+IIf(
[A8] Is Null,0,1)+IIf(
[A9] Is Null,0,1)+IIf(
[A10]Is Null,0,1))
The Average column is incorrect because it takes the sum of A1-A10 and divides by 10 regardless of if any 1 or more columns are null or not.
Any assistance is greatly appreciated