Excel Set up for a Transcript
 

I'm working out my midterm grades right now and want to set up an excel spread sheet that I'll be able to put in my grades and it'll factor everything for me. Right now this is what I have:

http://static.flickr.com/31/54033900_564317ef62_o.jpg

What I'm wanting to do is be able to change the A, B, C, and D (well, A, B, and C) on there so that when I change it, it makes Pts equal to A*(A-Hrs)=pts

And A=4, B=3, C=2, and D=1.

So essentially in a 3 hour class that I have a B in it'll do the math for me and under Pts it'll show up at 9.0 for me without having to do the math.

That way I'll have an automated way to do my midtrm grades and always have an up to date system with minimal work.

I think my wording is slightly confusing, so if you have questions please ask.

Thanks in advance

Easist thing to do would be to add a hidden column with a big IF statement.
=if(c5="A",4,if(c5="B",3,if(c5="C",2,if(c5="D",1,"error"))))

I don't know if I closed those parentheses properly, and I don't particularly care :D . There's more elegant methods, but this would be the quick'n'dirty method.

Why don't you just go to Microsoft's office site and download a template for grade tracking?

This is the direct link. You will probably find what you need here.
http://office.microsoft.com/en-us/te...377131033.aspx

Bendsley's right, tons of ready made solutions.

Then again if you want to do it... Redlemon's works fine in the pts field with a couple changes.

Or something like...

=(5-(CODE(UPPER(grade_cell))-64))*hours_cell

Edit: You didn't see that.

Thanks. I didn't know they had those kinds of things on Microsoft's we bsite. :)

Plus

"=if(E3="A",4,if(E3="B",3,if(E3="C",2,if(E3="D",1,"error"))))*G3" worked. Thanks a lot.

Elegance is dead. :(

I could've spent a few minutes and made an elegant solution, but I consider my time to be more important than the computer's calculation time (and that is why your computer now requires a full gig of memory to tie its own shoes).

I do a similar, but more complex setup for my student's grades.

The command that is your friend is VLOOKUP().

The function will go to a 2-column table, lookup the value in the first column, and return the value in the second column.

So if you make a table:

|letter | value
---------------
A | 4
B | 3


and so on, it will work.

Granted, for only 4 values the if statement is probably faster/easier. But when you're dealing with more complex grade setups (A, A+, A-, B+, etc down to F) the lookup table is a life-saver.

Agreed.

I had to whine. You ignored my CODE version.

I understood his code... I didn't quite get yours when I looked at it.