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

Interesting problem, I thought I had a bit of a solution but found out that
it only worked properly when the students average on the tests was below 90
(as in the formulas failed if the student had averaged more than 90%) In
anycase I'll post the formulas that I had and you may be able to tweak them
to react correctly.

In A1 I had 90
In B1 I had 90 (as that is what you need to average to get 90)
In C1 I had =(ABS(B1-A1))/3+90
In D1 I had =((ABS(AVERAGE(B1:C1)-A1))/2*2)+90
In E1 I had =(ABS(AVERAGE(B2:D2)-A2)*3)+90

In the example above as an exam was taken I would put the actual exam score
in the actual cell (thus knocking out the formula) For example if the
student got an 88 on exam 1 i put that in B1 (C1 would then tell you what
they had to average on the next 3 exams.) After the 2nd exam i put the
result in C1 and D1 should calculate what you need to average in the next 2
exams. etc.

Not by any means a complete or best case solution for you but perhaps it
will help in some way.

"EEH" wrote:

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

B | C | D
-----------------------------------------------------------
Goal Score 90
Total Weight 100.00%
Graded Categories Remaining 2
Avg Score Needed 88.50

Graded Categories Weight Score
Exam 1 25.00% 88
Exam 2 25.00% 95
Exam 3 25.00%
Exam 4 25.00%

Hopefully the above posts with the formats.

Inputs:
Goal Score
Grade Categories (col B), Weight (col C), Score (col D) -- I set
up ranges for up to 25 entries

Outputs:
Total Weight:
=SUM($C$7:$C$26)

Following entered as array formulas (ctrl-shift-enter) instead of just
enter after typing the formula... see help menu for more details on
"array formulas"

Graded Categories Remaining:
{=SUM(IF($B$7:$B$26<"",1,0)) - SUM(IF($D$7:$D$26<"",1,0))}

Avg Score Needed:
{=($D$1-SUM(IF($B$7:$B$26<"",IF($D$7:$D$26<"",$D$7:$D$26 *$C$7:$C$26,""),"")))
/ SUM(IF($B$7:$B$26<"",IF($D$7:$D$26="",$C$7:$C$26, ""),""))}

If you need more explanation let me know.

Regards.

EEH wrote:
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

ImpulseBlue,

that is totally awesome!!! It works fabulously.

Thousand thanks for this very smooth solution. 8)

Tom



"ImpulseBlue" wrote:


B | C | D
-----------------------------------------------------------
Goal Score 90
Total Weight 100.00%
Graded Categories Remaining 2
Avg Score Needed 88.50

Graded Categories Weight Score
Exam 1 25.00% 88
Exam 2 25.00% 95
Exam 3 25.00%
Exam 4 25.00%

Hopefully the above posts with the formats.

Inputs:
Goal Score
Grade Categories (col B), Weight (col C), Score (col D) -- I set
up ranges for up to 25 entries

Outputs:
Total Weight:
=SUM($C$7:$C$26)

Following entered as array formulas (ctrl-shift-enter) instead of just
enter after typing the formula... see help menu for more details on
"array formulas"

Graded Categories Remaining:
{=SUM(IF($B$7:$B$26<"",1,0)) - SUM(IF($D$7:$D$26<"",1,0))}

Avg Score Needed:
{=($D$1-SUM(IF($B$7:$B$26<"",IF($D$7:$D$26<"",$D$7:$D$26 *$C$7:$C$26,""),"")))
/ SUM(IF($B$7:$B$26<"",IF($D$7:$D$26="",$C$7:$C$26, ""),""))}

If you need more explanation let me know.

Regards.

EEH wrote:
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