Help with formula Grade book

[David]

I hope I phrase everything correctly. I have created a teacher grade
workbook. The rows A3 - A33 are student’s names. The columns C3 - C???? (???
= want to add additional grades (columns) when needed) are numeric test
scores.

B3 is my counter. I can’t seem to get my formula in cell B3 to update when I
add a column to include additional test score. B3 is my counter used when I
average the sum of the test scores. (see below) If I insert a column K for
example, the counter does not update. Is this the formula I should be using?
What formula should I be using?

=COUNT(C3:J3)

When I average a cell (all numeric grades) I use the formula below which
does not update either. This formula is always located in the column next to
the last numeric test score. When I insert a column to add another test score
I would like this formula to update. Example: J3 should read K3 when I insert
a column. How can I get the formula to update? Is this the correct formula I
should be using?

=SUM(C3:J3)/$B$3

Here is the formula I use when I want to convert a numeric value (average of
all test scores) to a letter grade for the averages. When I insert a column
the reference K3 should read L3 (this is the column I have my averages of all
the tests showing). How do I get the formula to update? Is there a better
formula I should be using?


=IF(K3>=93.5,"A",IF(K3>=86.5,"B",IF(K3>=78.5,"C",IF(K3>=69.5,"D","F"))))

Thanks,

 

[Don Guillett]

Easiest would be to have a dummy column at the end and insert BEFORE that
column.
or look for a number larger than in your data
=COUNT(c3:OFFSET(c3,0,MATCH(99999,3:3)))

 

[Toppers]

Assume grades are in C3 to J3:

in K3: =Average(C3:J3)

If you add an additional column by highlighting column K and doing
Insert=>Column then i.e. average calculation moves to column L, then when you
enter new grade in K3, L3 will show =AVERAGE(C3:K3)

In the same way your grade formula
[=IF(K3>=93.5,"A",IF(K3>=86.5,"B",IF(K3>=78.5,"C",IF(K3>=69.5,"D","F"))))]
will change to reflect the new data.

In my testing the latter formula was in cell C5

HTH

 

[David]

Topper,

Perfect, Thank you.

I am laughing here. You solution is so logical. And very easy. You would
not believe how long I worked at this problem before I asked for help.

Works like a charm. Perfect...

David

Assume grades are in C3 to J3:

in K3: =Average(C3:J3)

If you add an additional column by highlighting column K and doing
Insert=>Column then i.e. average calculation moves to column L, then when you
enter new grade in K3, L3 will show =AVERAGE(C3:K3)

In the same way your grade formula
[=IF(K3>=93.5,"A",IF(K3>=86.5,"B",IF(K3>=78.5,"C",IF(K3>=69.5,"D","F"))))]
will change to reflect the new data.

In my testing the latter formula was in cell C5

HTH

I hope I phrase everything correctly. I have created a teacher grade
workbook. The rows A3 - A33 are student’s names. The columns C3 - C???? (???
= want to add additional grades (columns) when needed) are numeric test
scores.

B3 is my counter. I can’t seem to get my formula in cell B3 to update when I
add a column to include additional test score. B3 is my counter used when I
average the sum of the test scores. (see below) If I insert a column K for
example, the counter does not update. Is this the formula I should be using?
What formula should I be using?

=COUNT(C3:J3)

When I average a cell (all numeric grades) I use the formula below which
does not update either. This formula is always located in the column next to
the last numeric test score. When I insert a column to add another test score
I would like this formula to update. Example: J3 should read K3 when I insert
a column. How can I get the formula to update? Is this the correct formula I
should be using?

=SUM(C3:J3)/$B$3

Here is the formula I use when I want to convert a numeric value (average of
all test scores) to a letter grade for the averages. When I insert a column
the reference K3 should read L3 (this is the column I have my averages of all
the tests showing). How do I get the formula to update? Is there a better
formula I should be using?


=IF(K3>=93.5,"A",IF(K3>=86.5,"B",IF(K3>=78.5,"C",IF(K3>=69.5,"D","F"))))

Thanks,

 

[David]

Don,

Thank you, I am off to add your formula to my grade book.

--
David

Easiest would be to have a dummy column at the end and insert BEFORE that
column.
or look for a number larger than in your data
=COUNT(c3:OFFSET(c3,0,MATCH(99999,3:3)))