Report - Work out average of a sum, but only divide by fields not equal to zero

[ne1gold]

Hi

Thanks for all the help ive been getting off the forum. Been helping me a lot.

So I have a little bit of a dilemma here.



So this is a report I have drawn from a query.

I now have to work out average scores of the yellow and the blue vertical columns.

The problem is that on every report these values changes based on what level the employee is on.

So if on level 11 only the light pink horizontal fields are applicable and if level 9 only the light blue horizontal fields are applicable.

Is there way to group these fields together and then write IF statement like this in the control source (not sure how to group fields?) :

IF (level(field) >=9 or <=11, sum(group1) / (numberoffieldsingroup) ----- This will then give me the average score - So im not sure how to group these fiields and also not sure how to program a count of fields in group to divide it by.

Hope this makes sense..

[ranman256]

in the query:
=iif(denom = 0,0,numerator/denom)

[ne1gold]

in the query:
=iif(denom = 0,0,numerator/denom)

Thanks!!!

Just one more thing, so im using the expression builder to try calculate values. But output stays blank :
This is the expression :
=IIf([Job Level FW]>=13,[Sum of core 13-16 FW]/5,0) Or ([Job Level FW] Between 9 And 12,[Sum of 9-12 FW]/15,0) Or ([Job Level FW] Between 7 And 8,[Sum of 7-8 FW]/10,0) Or ([Job Level FW]<=6,[Sum of 1-6 FW]/13,0)

Any help would be appreciated

[aytee111]

Each new condition must be its own "IIF", not "or". This gets very complex so be very careful, each IIF must have its own open and close bracketing.

IIf(conditon1,..,IIf(condition 2,..,IIf(condition 3,...,...)))