3 Replies Latest reply on Dec 18, 2013 9:56 AM by philmodjunk

# newbie needs help with average calculation

### Title

newbie needs help with average calculation

### Post

Hi there,

sorry to annoy anyone with this question, but I do need help.

I have a school database for students reports. Each student has a unique ID number. In the reports table, the student has a new record for each subject they are doing (eg 1 record for Mathematics, 1 for Science etc, all with the common ID Number). For each subject the teacher allocates grades for different outcomes (eg 5 outcomes for Maths, such as A, A, B, D, C). I have a calculation that converts those grades to numbers (A = 5, B = 4 etc) and then averages those numbers and converts the average to an Overall Grade (eg a students' Overall Grade for Mathematics is a B).

End result is that a student doing 9 subjects has 9 Overall Grades.

What I need help with is averaging (or even summarising?) these Overall Grades for something like a GPA. Looking at other posts online here, I think the sticking point I have is that the data is all in the one table (and will remain that way!) and I need it based on the ID Number.

Thanks

Chris

• ###### 1. Re: newbie needs help with average calculation

I think the sticking point I have is that the data is all in the one table (and will remain that way!)

That does make this data much harder to work with. Related tables are an available feature in FileMaker for a reason--to make things easier when this type of situations arises.

But that's not exactly what I see here. I see one record for each subject. I don't see the individual grades in your screen shot so I don't know how you've set up that detail. Since you apparently did not use a related table for the individual grades for each subject, I don't know if you used a repeating field for the individual grades or a set of individiual fields in the record.

The basic calculation for computing an average is the same, but the syntax is different.

Sum ( repeatingField ) / Count (repeatingField )

Individual fields:

Sum ( Field1 ; field2 ; Field3 ; field4 ) / Count ( Field1 ; Field2 ; Field3 ; Field4 )

Note: Count counts fields that are not empty. Thus, you will get a different average when a field is left blank than if a zero is entered into that field.

• ###### 2. Re: newbie needs help with average calculation

Hi there

thanks for your reply. I've attached another screen shot that shows the different outcomes as different fields (i.e. not a repeating field)

Individual fields:

Sum ( Field1 ; field2 ; Field3 ; field4 ) / Count ( Field1 ; Field2 ; Field3 ; Field4 )

seems to me that it would work well in calculating the original Overall Grade for a subject (and is very similar to my calculation). But what I need is the average of the student's Overall Grades - i.e. it is one field ("Overall Grade"), but from about 8 or 9 records (that student's subjects)

hope this helps?

• ###### 3. Re: newbie needs help with average calculation

The same aggregate functions work for related records.

You can set up a self join by StudentID and then