Summing Sumproducts then dividing for average really messy formula that needs help

Hi Everyone,
This is my first post. I have been using the posts I read here for help frequently. Now I’m unsure of how to write this more effectively and efficiently. This is for a Accumulated Grade Point Average on a transcript. Each semester has to be added to the equation from the earlier equation. As you can see, they get messy quick. Any suggestions?

Year Semester 1 Equation

Year 1 Semester 1 [=SUMPRODUCT(J10:J16,I10:I16)/SUM(I10:I16)] J10:J16 arethe point values for each grade. I10:I16 are the credits earned for each grade.

J10:J16
Year 1 Semester 2 [=(SUMPRODUCT(J10:J16,I10:I16)+SUMPRODUCT(T10:T16,S10:S16))/(SUM(I10:I16)+SUM(S10:S16))]

Year 2 Semester 1 [=SUMPRODUCT(J10:J16,I10:I16)+SUMPRODUCT(T10:T16,S10:S16)+SUMPRODUCT(J23:J29,I23:I29))/(SUM(I10:I16)+SUM(S10:S16)+SUM(I10:I16))]

Year 2 Semester 2 By now its to messy for me to type it accurately enough to have it work. By the end of the Senior year I will be in a real mess. Is there anyway to simplify this process?

Hi
welcome to this forum...
if you really wanna cut it ..
then here is an alternative....

Formula:

Please Login or Register  to view this content.

moreover, you can use named ranges...
to cut it short more..

I have used array formula..
So use ctrl + shift + enter to enter the formula..
Don't use only enter..


Don't forget to click *

Hi sktneer,

I very much appreciate your willingness to help. I have hopefully attached the information. I am trying to determine the cumulative grade point average.

Transcript Mockup.xlsx

Gratefully,
jrpspayne

Hi,
Thanks for your help. I am an amateur with EXCEL and have no idea how this formula works but it did. It will still get messy as it grows but is shorter than before. If you have time, could you explain how would I use named ranges?

Gratefully,
jrpspayne

Hi..

Go to Formulas----> Define Names

select the range and give an appropriate range...
you can also refer to excel inbuilt help...

after that when you are writing the formula..

use F3 button to paste the named range....


Don't forget to click *

For explanation give me a private message..
I will be explaining you everything at your Original
Thread...

SOLVED
I'm not sure how to officially marked this as solved but it is working nicely now. Thanks to both of you for your help.
jrpspayne