Formula Not Working

[Czeszyn]

I am trying to use this formula in my report, but I keep getting #Name? in the box instead of the number.

=(IIf([rptEmployEvaluationOperator subreport].[Report].[HasData],[rptEmployEvaluationOperator subreport].[Report]![OperSP],0)+iff([rptEmployEvaluationOperatorTrainer subreport].[Report].[HasData],[rptEmployEvaluationOperatorTrainer subreport].[Report]![OperTSP],0))/2


Not sure why it is not giving me the average of the two.

Thank you for your help in advance.
Tony

 

[spikepl]

Your soup stinks, right? But it has a number of ingredients.

So take each ingredient and output it in that report, then a combination of each two and then the lot. This way you zoom in on the cause.

 

[Czeszyn]

When I took each one separate, they both worked. But when I combine them, they don't?????

Tony

 

[spikepl]

I don't know what "each one" is. What is it?
I don't know what "when I combine them" is. What is the formula then? When you combine what with what? Each bit works separately? Then combine two dummy bits and see if that works, as in 1+2.

We do not see you screen. We do not read your mind. You need to take it one step at a time and show for others what does what.

 

[Czeszyn]

You don't see that there is two data coming into this formula. Both information is coming from a subreport to create the information in the main report.

One is coming from the Operator Report, the other is coming from the Operator Trainer Report. Taking these two numbers and getting an average, to put into my main report.

=(IIf([rptEmployEvaluationOperator subreport].[Report].[HasData],[rptEmployEvaluationOperator subreport].[Report]![OperSP],0)+iff([rptEmployEvaluationOperatorTrainer subreport].[Report].[HasData],[rptEmployEvaluationOperatorTrainer subreport].[Report]![OperTSP],0))/2

 

[Czeszyn]

Hey, I went back again like you said by looking at each thing, and found I was pulling from wrong fields. Got it to work. Thank you. I do have another question, but this one is harder, and will need to be walked through this.

=(IIf([rptEmployEvaluationAuditor1 subreport].[Report].[HasData],[rptEmployEvaluationAuditor1 subreport].[Report]![Auditor1SP],0)+IIf([EmployEvaluationAuditor2 subreport].[Report].[HasData],[EmployEvaluationAuditor2 subreport].[Report]![Auditor2SP],0)+IIf([EmployEvaluationAuditor3 subreport].[Report].[HasData],[EmployEvaluationAuditor3 subreport].[Report]![Auditor3SP],0)+IIf([EmployEvaluationAuditorTrainer subreport].[Report].[HasData],[EmployEvaluationAuditorTrainer subreport].[Report]![AuditorTSP],0))/4

Is there a way to tell this formula to only divide by the number of actual fields that have numbers in it. Sometimes there will not be all 4 spots, there could be just 3 or 2. I am not sure how to do something like this.

Thanks again, Tony

 

[Czeszyn]

Is there a way to make invisible box, that would count and check each report to make sure there is a value and add these together. Then once that number was collected, say 3 or 4, with the possible of 6. Would only divide the totals by the 3 instead of the 6??

I am not sure if asking this correctly.

Tony