Calculating Total and Average Fields

[Lamon Small]

I have created a db to import an ASCII file of evaluation forms scanned by a Scranton Optical Reader. Each file has numerous fields that contains either a 0,1,2,3,4,or 5 (0 is for no response).

Example:
Small, L 0 1 2 3 4 0
4 5 2 0 4 4
2 0 0 1 4 1
Total=
Average=

The problem I am having is when I create a Report and use the Summary Field Genie to create a field to calculate the total and average for that field.

The records which contain 0�s are counted in the total and also used to create an average. How can I have it exclude 0�s from the count and from the average?

Thanks,

Lamon

[Stan Mathews]

RE: Calculating Total and Average Fields

Create a calculated field(s) for your report, named whatever, and make the expression for the calculated field be

if(fieldname"0,1,0)

substituting the name of the field holding the 0-5 data. Place this field in the report footer and when the dialog appears, choose "total". This will give you the count of items with valid responses.

[Lamon Small]

RE: Calculating Total and Average Fields

My example did not come out right. It looked really good in MS Word with tabs. See if this works.

Small, L
0 1 2 3 4 0
4 5 2 0 4 4
2 0 0 1 4 1

Total=
Average=

[Lamon Small]

RE: Calculating Total and Average Fields

Stan
Thanks
This works great for the total but I am still having trouble creating an average. Any helpful hits there.
Thanks

[Tom Cone Jr]

RE: Calculating Total and Average Fields

Lamon,

I think Stan's idea gives you a count, not a total.

Create another calc field, take your original total value and divide it by the new count calc field.

-- tom

[Lamon Small]

RE: Calculating Total and Average Fields

Tom,

You�re right it gives me a total number of entries that are not 0�s. I guess a better term would be count. This was one of the things I needed.

Not being a programmer I am struggling with this average thing. I would appreciate an example of how to do this.

Thanks for your help.

[Lamon Small]

RE: Calculating Total and Average Fields

Tom,

I have been playing with what you suggested and it works. I finally figured it out.

Thanks for your suggestion and help.

[Lamon Small]

RE: Calculating Total and Average Fields

Now that I have gotten over these hurdles, I have one more problem. Hopefully the last one!

How do I now count only the number of 0�s, 1�s, etc. in each field of the report?

[Tom Cone Jr]

RE: Calculating Total and Average Fields

I'd like to help but don't understand your question.

How about giving us the fieldnames, representative field values in each, and the manually calculated totals and average figures you are shooting for?

-- tom

[Lamon Small]

RE: Calculating Total and Average Fields

Tom,

What I need is a way to count each occurrence of a number in a field.
Example:

Field 2
1
2
3
1
4
5
0

On a report I need to create a calculated field that will count how many 1�s there are in this field and display that amount on the report. Then I need to do the same thing for each of the other numbers.

Example:
Number of 0�s in Field 2=
Number of 1�s in Field 2=
Number of 2�s in Field 2=
Number of 3�s in Field 2=
Number of 4�s in Field 2=
Number of 5�s in Field 2=

I hope this makes sense.

Thanks

[Tom Cone Jr]

RE: Calculating Total and Average Fields

Lamon,

One approach would be to use the same pattern you're already familiar with.

Create a new calc field like this: IF(Field2 = 0, 1, 0)

Then create another new calc/summary field to total the values of the first new calc field.


Use the same pattern for the ones....

IF(Field2 = 1, 1, 0)

Then total it.


and so on.

I suggest you read up on the IF() function. The docs will explain how it works.

--tom

[Lamon Small]

RE: Calculating Total and Average Fields

Tom,

Thanks, so much for your help. It works. I now have a count for each number but I do have one more small problem.

I followed your instructions but when I create a new calc/sum field it gives me the total for all the files. It does not create it for the group. I did not think to mention this before because when I created the one to count entries without 0�s it automatically did it for the group.
Example:
total(Calc-"Count,GRP-"Faculty)

Count is if(field_2"0,1,0)

How do I add GRP-"Faculty in the formula? I have tried numerous things but nothing is working.

As you suggested I created this-
Count1 if(field_2=0,1,0)

Then when I drug a new calc/sum field it created this-
total(if(field_2=1,1,0))

Also do you have any suggestions on reading materials? I would love to learn more.

[Tom Cone Jr]

RE: Calculating Total and Average Fields

Lamon,

Perhaps you dropped the new calc / sum field (for the total count) in the group details band, instead of the group footer?

I think you need to add the third parameter, specifying the group break within which the total should be computed.

why not post a sample database here so we can look over your shoulder and tell you for certain?

-- tom

[Lamon Small]

RE: Calculating Total and Average Fields

Tom,
Another good suggestion, thanks. I have been working feverishly on this since your last suggestion.

I created another group and it worked fine. I then had to rethink my report layout. I created a group for each question and every thing went well, and boy has it been a learning experience!

But with all solutions also sometimes comes new problems. Luckily only one problem left. I am now unable to create an average of the averages (Section Average). I thought this would be a simple division problem, but for some reason I am not having any luck. Any more great suggestions.

I am attaching a PDF file of a sample report printout. As you will see I have totaled each of the separate fields and the number of responses. I can not figure out how to now create an average.

[Tom Cone Jr]

RE: Calculating Total and Average Fields

Lamon,

Nesting calculated fields sometimes fails.

I'd try creating a new calc field and define it to be the section total divided by the section count. In the expression reference the calc fields by name that give you the section total and the section count.

If that fails, then substitute the underlying expressions for their respective calc field names.

-- tom