Spreadsheets:Formulas
OpentheTwins2017.xlsxfile.Thenmakethefollowingchanges.Eachitemisonepoint.25pointstotal.
1.SetthecolumnwidthforcolumnAto12.00.
2.SetthecolumnwidthforcolumnsB:R(BthroughR)to8.00.
3.Settheheightofrow1to45.00
4.MergeandcenterA1acrosscolumnsAthroughR.
5.SettheCellStyleofcellA1toTitle.
6.AlignA1sothatitisalsocenteredverticallyinthecell(Excelcallsthismiddlealign).
7.Center-aligntheheadingsinB2:R2(cellsB2throughR2).
8.SettheCellStyleofA2:R2toHeading3.
9.Right-align the headings in C2:N2.
10.Center B2:B18.
11.Put formulas in D19:N19 that will compute the sum of each column.
12.Putformulasincolumn O (O3:O19) that will compute the batting average (AVG). The batting average is the hits (H) divided by at-bats (AB).
13.Put formulas in column P(P3:P19) that will compute the on-base percentage (OBP). This is the hits (H) + walks (BB) + hit-by-pitch (HBP) all divided by the sum of the at-bats (AB) + walks (BB), + hit-by-pitch (HBP) + sacrifice flies (SF).
14.Put formulas in column Q (Q3:Q19) that will compute the slugging average (SLG). This is the total bases divided by at-bats: each single (H) is 1 base, each double (2B) is 2 bases, each triple (3B) is 3 bases, and each homerun (HR) is 4 bases. However, the hits (H) column includes ALL hits, so doubles, triples, and homeruns are already counted in this column. Therefore, you only need to multiply doubles (2B) by 1, triples (3B) by 2, and homeruns (HR) by 3 to compute the total bases. If you are doing this correctly, the slugging percentage for Mauer should be 0.407.
15.Put formulas in column R (R3:R19) that will compute the on-base-plus-slugging percentage (OPS). This is the sum of the on-base percentage (OBP) + slugging percentage (SLG).
16.SettheCellStyleforthenumbersinrow 19totheTotalstyle.
17.Format the numbers in D19:N19 with a comma and zero decimal places.
18.SettheNumberFormatforthenumbersincolumns O, P, Q, and R to theCommastyle. and increase the number of decimal places to 3.
19.In B21, put a formula that will count the number of batting averages (AVG) column. Do NOT include row 19.
20.InB22,putaformulathatwilldeterminethelargestnumberintheaverage(AVG)column.
21.InB23,putaformulathatwilldeterminethesmallestnumberintheaverage (AVG)column.
22.Format A3:A22 with the Heading 4 cell style.
23.Setthepageorientationtolandscape.
24.Addyournametotherightpartoftheheader.
25.Scale the printout so that all columns fit on a single page.