Hidden Count box for dividing a set of values

[Czeszyn]

Okay, here is a hard one. I am trying to get my report to divided numbers that are there. I get this data from subreports, then want it divided by the appropriate number. Example:

23
0
10
15
0
0
this would equal 48, but I want it to be divided by 3, not by 6. Or say the next report would have:

10
10
5
0
10
10
which would equal 45 and this would be divided by 5, not by 6.

I was wondering is there a way by creating a hidden box that would check each value to see what it would be divided by. Example:

iff(report.hasdata, control +=1)

Not sure how to set this up. I also hope that I am explaining this correctly too.

Thank you in advance. Tony

 

[jdraw]

It appears that you are looking for the Average of NonZero values. Your samples suggest positive numbers???

Sum (X) where x >0
Count(X) where x>0

You want Avg = Sum(x)/Count(x)

Forget reports for the moment. Tell us about your database --what is it about? And your tables.
I'd recommend you try this with query(s) until you get results you are looking for.

 

[Czeszyn]

So for example for one box, this is what I have. This data comes in from 4 subreports. Sometimes one of the reports will not have a number, and I want this new box on my main form to only divide by the number that has data in it.

So say these four subreports, only 3 have a number 1 or higher, So I want that total to be divided by 3 because that was because 3 subreports had numbers 1 or higher. After the calculation it will display this number in the new box on the main report. So basically, I want the total to be divided by the number of subreports that have a number 1 or greater. There are 4 subreports, so the max number would be either 1 to 4 divided by the totals.

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


You mentioned:
Sum (X) where x >0
Count(X) where x>0

You want Avg = Sum(x)/Count(x)

Which I think is what I want, but not sure where to put that, or to make it work???


I hope that I explained this a little better, I am just not sure how to make it do what I want it to do. I sure hope that you can help.

Thank you in advance, Tony
:banghead:

 

[Czeszyn]

Would you do something like this????


= Sum[Report] where x>0
Count([Report] where x>0
(IIf([rptEmployEvaluationOperator subreport].[Report].[HasData],[rptEmployEvaluationOperator subreport].[Report]![OPTotal],0)+IIf([rptEmployEvaluationOperatorTrainer subreport].[Report].[HasData],[rptEmployEvaluationOperatorTrainer subreport].[Report]![OPTotal],0))/Count


Thank you in advance.

Tony:banghead::banghead::banghead:

 

[Czeszyn]

Hello, Can anyone help me on this issue.

 

[Czeszyn]

Hello, I really need help on this.

 

[jdraw]

Tony,

Data exists in tables. It is displayed, formatted and manipulated in reports.
I suggested you use a few queries against some tables until you get some results for what you are attempting to achieve.

Can you post a copy of your database and some explicit instructions of what to do with what?

 

[Czeszyn]

Hello jdraw

So would I use what you mentioned above in my query?

 

[jdraw]

???We need to see some data or your database. If you post the database we need explicit instructions to help you solve the issue.

 

[jdraw]

cross posted http://www.accessforums.net/reports...rt-using-subreport-data-54496.html#post288822