Calculating a Temporary Average

[Gail Gurman]

I'm creating a spreadsheet to keep track of my GPA. The grades don't all
come out at the same time, so one or more of the grade cells may be
blank. Each class has a different number of credits and the GPA depends
on multiplying the grade values by the number of credits, adding them all
together, and dividing by the total number of credits:

(G1*C1+...+Gn*Cn)/Sum(C1:Cn) = GPA

My worksheet (for now) looks like this (these grades are samples, of
course):

Class Credits Grade Value* Weighted Value
Subject1 2.0 B 3 9
Subject2 3.0 C 2 6
Subject3 3.0
Subject4 4.0 A 4 12

*This is returned by looking up the grade in an array of grade values.

I don't have the grade for Subject3 yet, but I want to figure out my GPA
so far. So I want to get the sum of the numbers in the "Weighted Value"
column and divide that by the sum of the numbers in the "Credits" column
that are in rows that contain a value in the "Grade" column. For example,
the answer for the above sheet would work out like this:

(9+6+12)/(2+3+4)=3

If this were a computer program, I could do this by having a variable
that gets incremented in a for() loop or something like that, but I'm not
sure how to create an Excel function to do what I want to do.

How can I create a function to get the sum of the numbers in a column for
which there is a corresponding value in another column?

Thanks in advance!

 

[Gail Gurman]

How can I create a function to get the sum of the numbers in a column
for which there is a corresponding value in another column?

I figured out the answer to my own question. The solution is to use SUMIF
(), which sums a range depending on the values in another range.

 

[Dave Peterson]

I think I'd just add another column:
Earned Credits
=if(c3="","",b3)

C3 holds the grade, B3 holds the potential credits.

But you can use an expression like:

=SUMPRODUCT(B2:B5,--(C2:C5<>""))
that will sum the values in B2:B5 only if the corresponding values in C2:C5 are
not empty.

So
=SUM(E2:E5)/SUMPRODUCT(B2:B5,--(C2:C5<>""))

would seem to work.

 

[Dave Peterson]

And the =sumif() solution is nicer.

I think I'd just add another column:
Earned Credits
=if(c3="","",b3)

C3 holds the grade, B3 holds the potential credits.

But you can use an expression like:

=SUMPRODUCT(B2:B5,--(C2:C5<>""))
that will sum the values in B2:B5 only if the corresponding values in C2:C5 are
not empty.

So
=SUM(E2:E5)/SUMPRODUCT(B2:B5,--(C2:C5<>""))

would seem to work.

 

[Gail Gurman]

And the =sumif() solution is nicer.

Yup. That's what I ended up using.