I'd like to create a spreadsheet that allows me to dynamically determine the
minimum exam score for the "next exam".

Example -- let's say a student participates in the following class XYZ:
- class XYZ has different categories (e.g. exams, project, homework, etc.).
For the sake of argument, let's just say though that class XYZ is comprised
of 4 exams
- let's also assume that the goal is to obtain an "A" for a particular
student. In order to get an "A", the student must have a final AVG score of
= 90.

Spreadsheet Breakout:
a) I'll enter "90" (student's goal) in cell A1
b) I'll enter "Exam 1", "Exam 2", "Exam 3", "Exam 4" in cell range B2:B5.
I don't want to just type in "4" (# of exams) in e.g. B1. Essentially, I
want a function to "scan" through a range and determine the populated cells
as the value for "# of exams". So, in another class, if I have 4 exams and
1 project, I'd additionally enter "Project 1" in B6. The dynamically
determined "# of exams value" could be stored in cell B1.

==========================

Calculation Process - e.g "Day 1":
- Student has just began the class. No exams have been taken at this time.
Hence, function should calculate (pseudo code)

Range has how many values (exams): 4
Final AVG Score (goal): 90
Calculation:
- ([AVG_SCORE]*[#_EXAMS]) = [TOTAL_ POINTS] ... or 360
- [TOTAL_ POINTS] / [#_EXAMS] ... or 360/4 ... or 90
- Cell C1 shows "90"

==========================

Calculation Process - e.g "Day 30":
- Student has completed the 1st exam and scored an "88"

Range has how many "remaining" exams: 3
Final AVG Score (goal): 90
Calculation:
- ([AVG_SCORE]*[#_EXAMS]) = [TOTAL_ POINTS] ... or 360
- [TOTAL_ POINTS] - [EXAM 1] = [NEXT_EXAM_TOTAL]... or 360 - 88 = 272
- [NEXT_EXAM_TOTAL] / [#_EXAMS] ... or 272 / 3 = 90.67
- Cell C1 now shows "90.67"

So, now, the student would know that he/she must make a "90.67" for the next
(actually all remaining classes) in order to get an overall score = 90.

==========================

Calculation Process - e.g "Day 60":
- Student has completed the 2nd exam and scored an "95"

Range has how many "remaining" exams: 2
Final AVG Score (goal): 90
Calculation:
- ([AVG_SCORE]*[#_EXAMS]) = [TOTAL_ POINTS] ... or 360
- [TOTAL_ POINTS] - ([EXAM 1] + [EXAM 2]) = [NEXT_EXAM_TOTAL]... or 360 -
(88 + 95) = 177
- [NEXT_EXAM_TOTAL] / [#_EXAMS] ... or 177 / 2 = 88.5
- Cell C1 now shows "88.5"

So, now, the student would know that he/she must make an "88.5" for the next
(actually the remaining 2 classes) in order to get an overall score = 90.

.... and so on for the remaining exams/projects.

==========================

Another spin would be to include "weights" for exams. Not sure if this be
too much to ask for... but potentially (in a class that has e.g. only 3
exams) to allocate the following %. 30% for Exam1, 30% for Exam2, 40% for
Exam2. Naturally, the functions will be more complicated.


Could anyone please provide me some help with the required functions.
Again, I'd prefer to only enter the following:
- Goal = 90 (in cell A1 or maybe different worksheet... using a lookup to
keep the 1st worksheet clean)
- Enter exams, project, homework names into a specificed cell range (from
that the total # of exams is determined)

Thanks so much for any help to solve this as smartly as possible?

Tom