Excel_for_beginners.pptx

Microsoft Excel Functions & Shortcuts for Beginners Parts of a Formula 1.Functions 2.References 3.Constants 4.Operators  Formula Example ~ Single Argument =SUM(A1:A10) is an example of a single argument. Mathematical Formulas Sum Count Counta Sumif Average Round Product...

Microsoft Excel Functions & Shortcuts for Beginners Parts of a Formula 1.Functions 2.References 3.Constants 4.Operators  Formula Example ~ Single Argument =SUM(A1:A10) is an example of a single argument. Mathematical Formulas Sum Count Counta Sumif Average Round Product Roman SUMIF What it does? sumsitemsin a list matching a condition Syntax: sumif(in thisrange,valuesthat meet thiscriteria,[sum-this-range]) Example: =sumif(A1:A20,10) =sumsthe cellswith the value of "10" SUMIF =sumif(condition range,condition, sumrange) Sumalternate Rows/Columns Number Amount Condition Alt. Row Sum1: 183 1 56 0 =sumif(E15:E21,1,D15:D21) 2 35 1 Alt. Row Sum0: 285 3 66 0 =sumif(E15:E21,0,D15:D21) 4 23 1 5 98 0 6 125 1 7 65 0 SUMIF We want to know how many HP Color Printers Dept Quanity Laser Jet Printers we have. HP Laser Jet Admin 4 =sumif(a2:a14,”HP Laser HP M553 Construction 5 HP Laser Jet Construction 1 Jet”,c2:c14) Which equals 11 Epson WF2750 Police 4 HP Laser Jet Pro Fire Prevention 1 We want to know how many HP Canon MF634 Human Svcs 2 Laser Jet Printers the POLICE HP Laser Jet Police 3 have. Canon MF634 Recreation 3 =sumifs(c2:c14,a2:14,”HP HP Laser Jet Parks 1 Laser Jet”,b2:b14,”Police”) HP M553 Clerk 1 Which equals 3 HP Laser Jet Purchasing 1 Epson WF2750 OEM 1 HP Laser Jet Golf Course 1 AVERAGE What it does? averagesa group of numbers Syntax: average(of thisnumber range) Example: =average(2,4,6) 4 5 10 =average(c9:d11) 12.4166667 3.5 20 6 30 ROUND What it does? roundsa number to the nearest decimal you specify Syntax: round(thisnumber, to thismany digitsafter decimal) Example: =round(12.416667,2) 12.42 Other: =rounddown(12.416667,2) 12.41 =roundup(12.416667,2) 12.42 Using Rounding for Budgeting AssessedValue 7,189,343,350 RATEno RATE Actual Amount to be RATEno round with Rate TAX Raised round expanded round Struck Municipal 42,778,836.00 0.60 0.5950 0.595 0.595 Municipal Open Space 1,437,869.00 0.02 0.0200 0.020 0.020 Library 2,871,017.00 0.04 0.0399 0.040 0.039 County 21,720,889.77 0.30 0.3021 0.302 0.303 County Open Space 761,633.70 0.01 0.0106 0.011 0.011 School 132,965,271.00 1.85 1.8495 1.849 1.850 Total 2.82 2.8172 2.8170 2.818 UGH!!! ROMAN/ARABIC What it does? convertsa number to roman numeral format or visa versa Syntax: roman(number) arabic("text") Example: =roman(65) LXV =arabic(LXV) 65 Logical Formulas If And Or Not Choose Iferror Istext IF What it does? checkswhether a condition ismet and returnsonevalueif TRUEand another if FALSE Syntax: if(is-this-true,then do this, or this) Example: =if(25