Calculation field in Word

[jlewis]

I have created this Word doc with form fields. As a teacher enters credits
into the first column of each term, Word form calculation fields adds up the
credits and displays the cumulative credits at the top right corner of the
page.

I would have used Excel for this project, but the end user is only receptive
to the Word document.

I would like to auto-calculate the GPA. Here’s how our district figures
that out:

Earned credit (Word FormField1c) * Grade Letter Points (WordFormField1g) =
Value
Add up all Values and then divide by number of Values

(A+, A, A-)=4 points
(B+, B, B-)=3 points
(C+, C, C-)=2 points
(D+, D, D-)=1 points
(F)=0 points

Example:

..5*4 (for A-) English 9 = 2
..5*3 (for B) Algebra = 1.5

(2+1.5)/2= 1.75 GPA

Credits and classes are added ongoing.

I would like to send someone the Word doc that I have so far - if anyone is
interested Thank you!!

 

[Doug Robbins - Word MVP]

Sent it to me if you want and I will look at it.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[jlewis]

Thanks, Doug. I don't see a way to upload it here. Can you give me your
email address please? Thanks again! Janet

 

[Doug Robbins - Word MVP]

It's in my response, but here it is (e-mail address removed)

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[jlewis]

OK.... I can't figure out a way to send the actual file. Here's a link to
it, though. If that's going outside the realm of the newsgroup, I totally
understand and I'll keep plugging away at it. Thanks!

http://www.sdb.k12.wi.us/lewis/ILP.doc

 

[Doug Robbins - Word MVP]

Running a macro containing the following code on exit from each dropdown
formfield:

Dim i As Long, j As Long, k As Long
i = Selection.Information(wdStartOfRangeColumnNumber)
j = Selection.Cells(1).Range.FormFields(1).DropDown.Value
If j = 1 Or j = 14 Then k = 0
If j > 1 And j < 5 Then k = 4
If j > 4 And j < 8 Then k = 3
If j > 7 And j < 11 Then k = 2
If j > 10 And j < 14 Then k = 1
Selection.Rows(1).Cells(i - 1).Range.FormFields(1).Result = 0.5 * k

Will populate the formfield in the cell to the left of the dropdown with the
appropriate value.

You have a bit of work ahead of you.

I really think that you should be using an Access Database for this sort of
thing.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[jlewis]

Thank you! Yes - I agree about using Access. I will give this a shot today.
Thanks again ... Janet

 

[jlewis]

Hi Doug,

Thank you for helping me with this project. Is there a way to keep the form
field that displays the credit (.5, for example in the first column) and then
add this GPA number (that is created by the macro) to a new field to the
right of the letter grade? The order isn't critical, in terms of looks - but
the teachers will need to see the credits earned by each course.

Thank you sooooooooo much!
Janet

 

[Doug Robbins - Word MVP]

Sorry, I am not following you. Can't the teachers see the 0.5? Or is the
problem that they have difficulty multiplying by 2 <G>

Are you saying that you would want the 1 that resulted in the 0.5 being
displayed as well.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[jlewis]

Hi Doug,

Yes, the teachers see the .5 initially. However, when I put the grade in
and add that macro, it erases the .5 and replaces it with the GPA value. I
need to show both values. Yes, I would want the 1 that resulted in the 0.5
being displayed as well.

Thanks again!
Janet

 

[Doug Robbins - Word MVP]

Isn't the .5 a constant by which the number corresponding to the A's, B's,
C's and D's is multiplied? Do the teachers need to be constantly reminded
of it?

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[jlewis]

Hi Doug,

The .5 is the credit value for the class. It could be a .25 or a 1 or some
other amount of credit. Students need a total of 24 credits for graduation.
That number needs to stay visible and I have it total up at each term, grade
level and then cumulatively in the upper right-hand corner. I gave each
value a bookmark name.

The GPA is determined by the letter grade times the credit.

This may be impossible in Word

Janet

 

[Doug Robbins - Word MVP]

It is not impossible, but that was not a criteria in your original post and
the document that I downloaded did not contain the necessary field in which
to display the data. You really need to work out the total requirement
before starting out.

In place of the following line of code that I gave you

Selection.Rows(1).Cells(i - 1).Range.FormFields(1).Result = 0.5 * k

You would need to use something like

Selection.Rows(1).Cells(i + 1).Range.FormFields(1).Result =
Selection.Rows(1).Cells(i - 1).Range.FormFields(1).Result * k

This assumes that the GP is to be displayed in the cells of a column that
you add to the right of the column that contains the dropdown. I would
suggest that you arrange the form that way as it makes referencing the
formfields possible in a way that will allow you to use the same macro on
exit from all of the dropdown formfields.
--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[jlewis]

Hi Doug,

File location: http://www.sdb.k12.wi.us/lewis/ILP.doc

Thank you for your continued help with this project. I made the first round
of modifications by adding cell in a column that to the right of the column
that contains the dropdown.

The last step is to have the AVERAGE of the individual class GPA fields
displayed in the GPA field in the upper right hand corner of the document.

Is this possible?

Thank you,

Janet