Microsoft Word - End of semester grading time blues.docx

TheShortcuttoCreatingWeightedGradesUsingExcel2007.

Aswereachfinalsweek,we’reallthinkingaboutDecember15

andturninginfinalgrades.Nottoolong

ago,formostofusthatmeantsittingatatablewithacalculatorfiguringoutweightsandaverages.

However,withtheuseofprogramslikeMicrosoftExcel,calculationtimecanbedrasticallyreduced–

justletthespreadsheetdothework.

ForthoseofususingBlackboard’sgradebookfeature,wecan

simplydownloadthegradesintoExcelandsetupformulasforeachcolumn.Forthoseofuswhoaren’t

yetusingBlackboardtocapturegrades,it’sstillatimesavertoenterthegradesintoaspreadsheetand

letthecolumn

formulascalculateweightsandaverages,evenifyouhaveverylittlefamiliaritywiththe

Excel2007program.

Theprocessdescribedbelowcanhelpyoufinishyourgradecalculations infoureasysteps.

1. Settingupthespreadsheet.

A. Ifyouhaven’tenteredyourgradesintoanyelectronicformat(worddocument,

spreadsheet,

blackboard),youwillneedfirsttocreateanExcelspreadsheet. JustopenExcelandbeginto

enterstudentnamesinrowsandgradedactivitiesincolumns(seefig.1).Alternatively,you

couldcopyandpastethestudents’namesfromOASIStosavetime.



Figure1:Studentnamesandgradedactivities.Notetherowfor“MaximumScore,”whichwillbeusedinthe

weightedformula.“WeightedScore”willnotcorrespondtototalpoints,butrathertoafinalweighted

percentage(i.e.thefinalgrade).



B. IfyourgradesareinExcel,great.Ifthey’reinaWordtableorBlackboardgradebook,then

youneedtoconvertthem.

i. ForBlackboard,simplyopenthegradebookandchoose“ExporttoSpreadsheet,”

reviewtheoptions(formostpeople,thedefaultvaluesarefine),choose“Export,”

then“Saveto

Disk”–youwillthenhavea.csvfilethatExcelcanuse.NOTE:Make

sureyouinclude“UserID”inyourexportedcolumns,oryouwillnotbeabletoget

yourgradesbackintoBlackboard;bydefaultthe“UserID”columnwillbe

exportedUNLESSyouhavehidden

itinthegradebook.Ifyouhavehiddenit,

eitherunhideitpriortoexportorselect“AllColumns”fromtheexportdialog

window.

ii. ForWordtables,selectthetableandcopytheentiretable;youcanthenpastethe

tableintoanExcelspreadsheet(theeasiestwaytoselecttheentiretableistohold

downthemouse

buttonandrunthecursoralongthetopofthetablecolumns).



2. Createyourformulas.Nowthatyouhavethespreadsheetrowsandcolumnssetup,it’stimeto

createformulas.ForThisexample,I’mgoingtoassumeweightedgradingwiththefollowing

values:

Activity Weight

Midterm 20%

Final 25%

Paper1 15%

Paper2 20%

Quiz1 10%

Quiz2 10%



A. Thegoodnewsisthatyouonlyhavetocreatetheformulaonce.Thebadnewsis

thattheformula’slengthisbasedontotalnumberofgradeditems;ifyou’regrading

twentyactivities,theformulacanseemverycomplex–butit’sactuallysimple.

B. Thebasicformula

foreachactivitywillbe[(studentscore/totalscore)*weight];

eachoftheseindividualcalculationsarestrungtogethertoarriveatthe“Weighted

Score”incolumnIoftheexample.Seefig.2below.(Exception:Ifallscoresareout

of100,thenskiptoD).



Figure2:Donotbefrightenedbythelengthoftheformula!It'sjustrepetition.



C. Ifwebreakdowntheformula,wefinditsimplyconsistsofthegradeditemsand

theirweights(I’vecoloredthetexttoshowtheseparateitemsmoreclearly):

=(C2/$C$6*20%)+(D2/$D$6*25%)+(E2/$E$6*15%)+(F2/$F$6*20%)+(G2/$G$6*10%)

+(H2/$H$6*10%)

Formulalookscomplex,

butit’ssimple.

i. Theequalsign(=)atthebeginningoftheformulatellsExcelthatthis

formulaisaSUMfunction.

ii. (C2/$C$6*20%)representsJohnAdams’sMidtermgrade(C2)divi dedbythe

midterm’smaximumscore(C6),thequotientofwhichisthenmultipliedby

theweight(Imanuallytypedit

in,butitwouldbepossibletouse theC7

spotintheformula).

iii. DOLLARSIGNS($)areextremelyimportantifyouonlywanttotypethis

formulaoutonce.Thedollarsigntellsexcelthatthegivenvalue(inthis

case,c6)shouldremainconstantwhilecopyingthe

formula.

iv. (D2/$D$6*25%)etc.areonlyrepetitionsofthebasicformulaforeach

activity.

v. Eachactivityformulaischainedtogetherwithanadditionsymbol(+),telling

Exceltoaddeachvaluetogethertoreachasum.



D. Ifallscoresareoutof100,thentheformula

isevensimpler:studentscore*weight.

i. Assumingthatinourexampleeverythingwereworth100points,the

revisedformulawouldread=(c2*20%)+(d2*25%)+…etc.,becauseeachitem

wouldbeofequalpointtotalvalue.

ii. Youwouldn’thavetousestep4belowtoconverttoapercentageview.



3. Copythe

formula.Oncetheformulaiscreated, weneedtocopyitdowntheWeightedScore

columntosavetime.

A. Usethe“fillhandle”inthefirststudent’sWeightedScorebox.Thefillhandleisthe

smalldarksquareatthebottomrightofaselectedcell(fig.3).Put

themouseonthe

fillhandle,holdthebutton,anddragthemousetothebottomoftheWeighted

Scorecolumn.



Figure3:Locationofthe"fillhandle"foreasycopyingdownthecolumn.Anotherwayto

accomplishtheduplicationistoselectallboxesinthecolumnandpress“CTRL”+“D”.



Fill

Handle

B. Theformulaautomaticallyduplicatesitselfandallstudentscoresarenowcalculated

(fig.4).



Figure4:Allgradesarenowcalculated.



4. Optional:Changedecimalviewtopercentageview.Foraddedelegance,wemaywantto

displaythefinalweightedscoreasapercentagevalueratherthanasadecimalvalue.That’s

easyenoughtodo:withtheweightedscorecolumnstillselected,clickthe“%”symbolonthe

“Home”tab’s

menu(fig.5).



 Figure5:Asimpleclickofthe%buttonconvertsdecimalviewtopercentageview.



Youcanthenusethedecimalbuttonsimmediatelybelowthe%symboltoincreasedecimal

placesifyouareinterestedinroundinggrades(fig.6):



 Figure6:Clickingonthedecimalplacesymbolincreasesyourpercentage'sspecificity.



Nowallyouneedtodoistransferthegrades toOASISandyouarefinished.