90 likes | 145 Views
Anglicky v odborných předmětech "Support of teaching technical subjects in English “. Tutorial: Obchodní akademie Topic : Creating Formulas Prepared by : Mgr. Zdeněk Hrdina. Projekt Anglicky v odborných předmětech, CZ.1.07/1.3.09/04.0002
E N D
Anglicky v odborných předmětech"Support ofteachingtechnicalsubjects in English“ Tutorial: Obchodní akademie Topic: CreatingFormulas Prepared by: Mgr. Zdeněk Hrdina Projekt Anglicky v odborných předmětech, CZ.1.07/1.3.09/04.0002 je spolufinancován Evropským sociálním fondem a státním rozpočtem České republiky.
Formulas • Formulas in Excel are equationsthatperformcalculationswith data in a givensheet, workbooketc. • Eachformulabeginswith =
Contentofformulas • Formulascancontain: • constants– directlyinsertednumbers, text • cell references– fromthesameor a differentsheet, includingabsolute, relativeormixedreferences • mathematicaloperations– addition, subtraction, multiplication, division, power, brackets, comparison • functions
Formulas – additional notes • Excel workswithmathematical precedence ofoperators- (e.g. power has a higher precedence thanmultiplication, multiplication has a higher precedence thanaddition) • Onlyroundbrackets are used(unlike square orcurlybracketsetc. as in mathematics), matchingpairsofbracketssharethesamecolour
Operators Note: Rootisenteredusingthepowernotation. Itisthefollowingmathematical rule:
Enteringreferences in formulas • 1. direct input fromthekeyboard • impractical, especiallywheninsertingreferencesfromdifferentsheetsorworkbooks • 2. by clickingindividualcells on a sheet • onlybrackets and mathematicaloperators are inserteddirectly In bothcases, thecellsusedgetcoloured
Practicalusageofformulas • Calculating a percentage– whencalculating a percentage, a simpleprocedureisused – the rule ofthree Exercise: Calculatehow many pointseach person got Calculatethe sum ofallvalues The sum is 100 %, dividethepointsofthefirst person by thetotal sum Addanabsolute reference to thetotal sum, becauseitis necessary to divide by thetotal sum forallparticipants Copy to theothercells
Exercise • Use aneffectiveway to fill in thehighlightedcells, the settingfor cell formattingisgiven in thetask zadani.xlsx
Sources • BARILLA, Jiří, Pavel SIMR a Květuše SÝKOROVÁ. Microsoft Excel 2010: Podrobná uživatelská příručka. Brno: ComputerPress, 2010. ISBN 978-80-251-3077-3. • WALKENBACH, John. Microsoft Excel - vzorce a funkce. Praha: Mobil Media a.s., 2001. ISBN 80-86593-01-0.