one row won't calculate

[Rick]

I use a grade book which I downloaded from the Microsoft site. It works
great, except when I had to add a new student. The grades do not calculate
like in the other rows of students. I tried changing the grades of the
other students to make sure it is calculating correctly, and it does. It is
only for this new student. I also tried entering the grade directly in the
row, and then into the formula bar, all to no avail.
Any ideas?

 

[John C]

I think we'd need to see some formulas that arent' calculating, and some
sample data.

 

[Rick]

how do I upload that to you?

I think we'd need to see some formulas that arent' calculating, and some
sample data.

 

[Peo Sjoblom]

Well since many of the regulars probably won't go to MS download site and
download this
file maybe you can explain what formulas (I assume it is a lookup formula)
you are using and what does not work?
First thing I would check is if the new student's numbers are in fact text.

--


Regards,


Peo Sjoblom

 

[Joel]

Can you post URL of the Templet you downloaded? You are proabbly adding new
student incorrectly and there are no formulas in the new rows you have
entered.

 

[John C]

You don't upload, just type what formulas in which cells aren't calculating,
give us some sample data here to work with...
For example:

Say you have the Grade in cell B25, you type in 97, so in cell C25, you
would want the letter grade to show, i.e.:
C25: =IF(B25>92,"A",IF(B25>84,"B",IF(B25>76,"C",IF(B25>68,"D","F"))))

Without knowing what formulas are in cells that aren't updating, and without
knowing what data you are entering that should update these formulas, no help
can be rendered.

 

[Joel]

Why wouldn't people download files from a trusted microsoft webpage????

 

[John C]

I don't think he was picking on the trust issue, I think he was more thinking
that most people won't actually download a file if it is just a simple
formula that is in error.

 

[Peo Sjoblom]

Did I say that?

--


Regards,


Peo Sjoblom

Why wouldn't people download files from a trusted microsoft webpage????

 

[Rick]

Thanks to all of you for the quick posts. I forgot to mention I am using 2003
I admittedly know nothing about excel. I just downloaded the gradebook and
it worked great. So I'm not sure how to send you the formulas you asked
about.
The data i enter is in points. you add an assignment and give it the points
you can get. The spreadsheet converts it to the percentage and a letter
grade. I think I may have entered the grade points at the beginning (0-60 is
an F for example)
It has worked great, both entering grades and changing them.
It's this one student for which it doesn't calculate.
I downloaded it from:
http://office.microsoft.com/en-us/templates/TC010183971033.aspx?pid=CT101445101033

Thanks!!!

 

[Joel]

If it was a formula error than they would of recevied N/A or REF. It sound
more like they added a row and there were no formulas. I would like to see
the templete to find out how new rows should be added.

 

[John C]

Yes, if he has a formula that is referencing cells A2:A100, and he is
entering data in A101, and no update is happening to the formula that
references A2:A100, then no error will occur. It is why people have asked for
the formula, and the cells that the OP is entering data into. I personally
would not DL a template from any site.

 

[Joel]

Rick: Inserting Rows will not copy the formula. The best way of adding row
is as follows:

1) Insert Row like you normally would do by highlighting row and then Insert.
2) Copy a row with formulas then go to PasteSpecial and Select formulas.

A 2nd method is to highlight a Row with formulas and Copy. Then right click

 

[Rick]

unfortunately it didn't work.
I dont' know if this helps, but the gradebook has several blank rows that
appear (to me anyway) to have the green triangles in them that they already
have the formulas

 

[Rick]

HEY!!
I just tried the first suggestion again, and it worked!! Now I can get my
midsemester grades submitted.
My students probably don't thank you but I do!!

 

[Peo Sjoblom]

Text numbers? Format an empty cell as general, copy it
select the cells with green triangles and do edit>paste special and select
add.

--


Regards,


Peo Sjoblom

 

[Joel]

The green triangles indicate the cells were changed, not that they have
formulas. If you click on any Cell in columns D, E, or F. You wil lsee the
formulas in the Fx() box at the top of the worksheet. To copy the formulas:

1) find a row with the formulas. Then highlight Columns D, E, and F and
Copy (Cntl - C).

2) Then go to a row without formulas and right click on Column D. Select
PasteSpecial and choose formulas. Then press OK.

 

[Rick]

I was wrong.
When I past the formula and enter the new data everything is fine.
But when I click the save button, the data stays in the fields, but the
calculated columns for Average, Ltr Grade, and GPA disappear.

Am I doing something wrong?

 

[Joel]

You have to fix the formulas at the bottom of the page. Make sure the Range
includes all you student rows. If you Insert in the middle of the student
data the Rows of these formulas should automatically increase. You just need
to copy the formulas in columns D, E, and F on the individual rows. I
usuallly copy the entire row and then Insert copy Row, rather than insert row
and then have to copy the formulas.

Average Column D
=IF(SUM(D1352),AVERAGE(D1352),"")
Average Column E
=IF(D55<>"",HLOOKUP(D55,GradeTable,2),"")
Average Column F
=IF(SUM(F13:F52),AVERAGE(F13:F52),"")

Highest Column D
=IF(SUM(D1352),MAX(D1352),"")
Highest Column E
=IF(D56<>"",HLOOKUP(D56,GradeTable,2),"")
Highest Column F
=IF(SUM(F13:F52),MAX(F13:F52),"")

Lowest Column D
=IF(SUM(D1352),MIN(D1352),"")
Lowest Column E
=IF(D57<>"",HLOOKUP(D57,GradeTable,2),"")
Lowest Column F
=IF(SUM(F13:F52),MIN(F13:F52),"")

 

[Gord Dibben]

Suggest you have a look at error-checking in Tools>Options to see what the
green triangles really mean.


Gord Dibben MS Excel MVP