averaging school grades by number and letter

[joemuskrat]

Averaging by number is a no-brainer. I've also figured out how to average letter grades. My dilemma is how to use the same fields for both solutions (some schools use letter grades, others use number grades)

[ESpringer]

You could have an "AverageForLetterGrade" field that produces a letter average ONLY after checking whether there's alphabetic data, and an "AverageForNumericGrade" field that produces the numeric average if there's no alphabetic data, and then a "UnifiedAverage" calc field that takes the letter grade only if the number field is empty...

Would it not be more efficient to reduce the letters to numbers at an early stage, so that data across recodrs is commensurable? (This would require work if different schools have different grade conversion algorithms, but wouldn't be impossible...)

-ESpringer

[Jim McKee]

Hi joe ...

Why not have a preferences screen where the user selects the grading scheme used in their school -- i.e., numbers or letters. If letters, the user could also enter equivalent number values for each letter grade so your calculations would exactly match a particular school's grading method. Your solution could then branch to the specific calculations needed to handle number grades vs. letter grades.

I'd standardize on Text fields for both data entry and the display of calculation results. If the school uses number grades, use the TextToNum() function, perform the math calculations, then display the results using the NumToText() function. I can't think of a reason to maintain both Number and Text fields in your solution.

[joemuskrat]

Hi Jim,

Thanks for your response. I created text fields and worked it out for letter grade averages. But I'm having trouble using the same text fields for numbers. I know about the TextToNum()function, but can't seem to get it to work within the calculations that I'm using to get the letter grade averages. Any suggestions.

Thanks,

Joe

[Jim McKee]

Hi Joe ...

My thinking on this was that you would have a single text entry field for the grade, and a single calculation field of type Text to display the student's average, regardless of what grading method a school uses.

If in Preferences, the selected grading system is "letter" grades, then averages would be computed based on values you've assigned to various letter grades. You said you've already worked this out, probably with a calculation field containing Case statements to assign a numeric value to each letter grade. I'm assuming this because you can't average alpha characters

If the school's Preference is set to "numeric" grades, the numeric value would be entered into the same text field, and you'd include a TextToNum(grade) translation to compute a student's average, and then a NumToText(average) to display the result via the same calculation field used to disply a letter grade average.

So, the following steps would be performed.

First, a calculation of type Number to produce a numeric test grade:

Case(

preference = "letter",

Case(

grade = "A", 100,

grade = "A-", 97,

grade = "B+", 94,

grade = "B", 91,

grade = "B-", 88,

grade = "C+", 85,

grade = "C", 82,

grade = "C-", 79,

grade = "D+", 76.

grade = "D", 73,

grade = "D-", 70,

grade = "F", 60

),

TextToNum(grade)

)

Next, the grades from several tests would be used by a calculation to compute the student's average. Something like:

average = (grade1 + grade2 + grade3 + grade4) / 4

Next, the following would be used to display the average (numeric or letter) within a single calculation field of type Text:

Case(

preference = "letter",

Case(

average > 97, "A",

average > 94, "A-",

average > 91, "B+",

average > 88, "B",

average > 85, "B-",

average > 82, "C+",

average > 79, "C",

average > 76, "C-",

average > 73, "D+",

average > 70, "D",

average > 67, "D-",

average < 68, "F"

),

NumToText(average)

)

Using this structure, you would not have to maintain text and number fields for different schools' grading methods. Essentially, you're using text to enter grades and display a student's average. Numeric values are used only in the background to perform the calculations necessary to produce a student's average.

Hope this is clear.

[CobaltSky]

Hi Jim, Hi Joe,

I think Jim's suggestion provides the basis of a neat way to provide for letter or number grading within the one system. However it does require that the preference be specified in each case.

There might be value in finessing the concept to have it automatically detect the method used for the original grade(s) and return the average in the corresponding format. There are a few ways to do this - however based on Jim's calc model, an explicit test for letter grades would be easiest to incorporate. For instance:

PatternCount("A+B+C+D+E+A-B-C-D-E-F", Grade)

- could be used as a direct substitute for 'preference = "letter"' in the suggested calcs.

This approach has the advantage of being transparent to the users - ie they won't have to set any preferences in order for it to work - merely entering results will be a sufficient act to cause the system to configure itself...

[Jim McKee]

Ray ...

Excellent! Works perfectly.

Another benefit of your PatternCount() approach is that if a school changes their grading method at some point in the future, historical averages are not affected.

Thanks, Ray.

[joemuskrat]

Hi Jim and Ray,

Wow. You guys sure are on the ball. I already have it set up so that the user inputs a number value for each grade (A+ through D). I then use those fields to get the numerical value for each letter. But because your way is so transparent, it will far less confusing to the user. I'll give it a try and let you know.

Thanks much for your help.

Joe

[joemuskrat]

Hi Jim and Ray,

OK. Here

[Jim McKee]

Hi Joe ...

When I checked out Ray's suggestion (above) I didn't take into consideration that we still need a way to determine how the average grade will be displayed. Ray's solution is fine as far as it goes -- i.e., it automatically determines how to handle the grade entry (letter or "numeric"), but it doesn't provide a way to branch to the correct display of the average grade. So, you'd be stuck with two "display average" fields.

I decided to return to the original idea of a preferences field, which is stored in a Utility file. This way, I only need one averaging calc, and one display calc. The display calc contains the necessary Case statements to determine how the average should be displayed, i.e., "letter" or "numeric", based on the user's selected preference.

I'm attaching a demo. It's designed only to show you the calculation for grade migration from TextToNum for calculating the average grade, and the calculation to display the average grade, which requires a NumToText function. The demo is not a working grade entry system. E.g., creation of example "letter" and "numeric" Test records (including random-grade entry) is scripted and hard-wired to your 15-test per grading period requirement. But, it will give you some idea of the basic calcs, allow you to see them in action, and then you can use them in your own system. Also, be sure to check out the relationships which are used in the average grade calculation, and for display of grades and average grade on each student's record.

If you have any more questions, let us know.

And, if I'm ever in NYC, I'll definitely take you up on that offer Happy New Year!

Test Grading demo.zip

[joemuskrat]

Hi Jim,

Thanks for the info. It will take me awhile to sort it out, but methinks you've got it. Looks like you spent a bit of time creating those files. I really appreciate your patience and effort, especially for one of those G-D New York drivers!!

Joe

[CobaltSky]

Hi Jim,

Must say I'm a little puzzled by your last post.

Handling the inputs (grade entry) and handling the outputs (display of an average or a grade) can be done in exactly the same way, each being determined according to the input method used.

In your earlier post, you provided suggestions for two formulae - one to render the input grades as averages, and the other to return the combined averages to grade/number format. Both of them utilized the preference = "letter" test to determine the format. My suggestion was that preference = "letter" be replaced with PatternCount("A+B+C+D+E+A-B-C-D-E-F", Grade) in both formulae in order to deliver a system in which formats throughout are cast dynamically.

The logic as I see it is that if the user-driven preferences approach can work, then the data-driven approach must also be able to work, simply by referencing all the conversion calcs to the same input determinants (instead of referencing them to a preference field)?!

[Jim McKee]

Hi Ray ...

So you're saying I should be able to use the PatternCount function in returning the displayed average. Hmmm ...

The PatternCount method is a great for handling the input field. If PatternCount is true, then translate the letter grade into a numeric according to the values within the nested Case statement. If the PatternCount returns a false, just do a TextToNum translation. Fine so far.

However, when moving from the numeric average calc

Sum(self_studentID::grade) / Count(self_studentID::constant)

to the displayed average calc (text), I couldn't figure out what to refererence the PatternCount to. That's why I decided to go the route of a global preference field.

So how would you construct the displayed average calculation using the PatternCount?

[CobaltSky]

Hi Jim,

I would reference them both to the same input grades for the current record.

This is assuming that if a school enters grades as letters, they will want averages returned as letters and vice versa.

You made the same assumption in your earlier post, where you have both calculations referencing a single preference field.

If some schools want to be able to enter grades in one format but have the averages returned in another (or enter results in an indiscriminate mix of formats) then that's a different story - and both your suggestions and mine would need revision, but otherwise all the flow-on inputs and outputs should be able to be referenced to the raw data entered by the school, using the same PatternCount (or equivalent) expression.

[Jim McKee]

Hi Ray ...

I never thought of referencing the PatternCount to the grade field on each record. I assumed it wouldn't work because the display average field references a calculation based on multiple records per student. But, I tried your suggestion, and it works fine. So, no need for the preference field. A simpler, less clutterd solution.

Thanks a lot for your help