Friday, October 12, 2007

A Good Nerdy Excel Week

functions i successfully used in excel this week include
sum, sumif, if, vlookup, isna, min, small, column, address, indirect, concatenate, fixed, countif... plus some regular multiplication, addition, subtraction, division and what not.

result: a super set of spreadsheets which takes care of my overly complicated grades for g chem. i can excuse quizzes and not have the points count for or against the student (essentially average it it). when students do the extra credit that replaces the lowest and second lowest quiz and homework scores it figures it out for me once i type in the extra credit score. the homework is imported from another system and my spreadsheet will lookup the values each time i paste in the new data so i don't have to constantly wonder if people i've given extensions to have the proper score recorded. thanks to keith for help with the isna and some nested ifs.

after the next test i will also add in the function of dropping the lowest exam score (kinda pointless with only one exam so far though). but overall i think i am ready for just about anything... ahahha ya right. but i am ready to enter most stuff, i think.

2 comments:

k2h said...

heres a few functions i've used that i'm not sure i can edit them cause they only make sense when i write them

=CONCATENATE(IF(ISTEXT(E58),CONCATENATE("(",E58," ",F58,(IF(ISTEXT(G58),", ","")),G58,") in "),""),D58)

=IF(S58,"Done",(IF(AND((K58<=$V$1),NOT(ISBLANK(K58))),"Expired",(IF(AND((R58<=$V$1),NOT(ISBLANK(R58))),"Stock","Scheduled")))))

even some super crappy 2 line VB
Private Sub CheckBox3_Click()
If TextBox3.BackStyle = fmBackStyleOpaque Then TextBox3.BackStyle = fmBackStyleTransparent Else TextBox3.BackStyle = fmBackStyleOpaque
End Sub

Private Sub CommandButton1_Click()
Range("D4").Value = Range("D4").Value + 1
End Sub

palegreenhorse said...

ooo i like that concatenate

here's my 'best not have to edit it because it is a bit of a mess' cell

=INDIRECT(ADDRESS(4,IF($AD5=$AB5,
SMALL(IF(($AF5:$AZ5)=SMALL
($AF5:$AZ5,2),COLUMN($AF5:$AZ5)),2),
MIN(IF(($AF5:$AZ5)=SMALL
($AF5:$AZ5,2),COLUMN($AF5:$AZ5)))),2))