Chapter 4 - Formula Techniques

2y ago
107 Views
2 Downloads
655.53 KB
10 Pages
Last View : 1m ago
Last Download : 5m ago
Upload by : Bria Koontz
Transcription

Microsoft Excel 2010 - Level 2CHAPTER 4FORMULA TECHNIQUESINFOCUSWPL E818Most people are familiar with simple formulas such as B2 B3 andsimple functions such as SUM, but there is so much more that youcan do with formulas and functions. Most functions requireparameters – extra bits of information – to perform their task. Youcan actually use other functions or formulas as the parameters for afunction – effectively nesting functions within functions.In this session you will: Watsonia Publishinggain an understanding of how to scope a formulagain an understanding of how to develop a nestedfunctionlearn how to create a nested functionlearn how to edit a nested functionlearn how to copy nested functionslearn how to use concatenation to join text and valueslearn how to switch to manual recalculationlearn how to force a recalculationlearn how to paste values from formulas.Page 27Formula Techniques

Microsoft Excel 2010 - Level 2SCOPING A FORMULAA nested function is a function within a function,and they can be quite tricky to read and buildunless you plan ahead. Scoping refers to theprocess of defining the problem or calculation sothat you can identify the different parts of theformula you need to create. The order in whichthese parts need to be calculated determineswhere each part is nested in the formula.Scoping & Building StepsFormulas that use nested functions are best built in several stages and should be thoroughly testedat each stage to ensure that they achieve the correct result. The steps are:1. determine what the overall formula will calculate2. break down the formula into its component parts3. create the base function and ensure that it works4. add the additional functions and elements of the formula one by one, testing each one as itis added to the overall formula.Nesting Functions Workshop ExampleThe objective of the workshop example is to create a formula that calculates a dividend payable toten superannuation investors. The spreadsheet includes the following information:The dividend is calculated by applying a percentage to the original investment. The percentage istaken from a sliding scale and is determined by: the amount of the investment the investment scale that the investor chose the date of initial investment: as an incentive, a 5% bonus was offered to investors who signedup on or before June 30, 1998.The base function in the formula will be a VLOOKUP function, which is written as follows:VLOOKUP(lookup value, lookup table, return value)The lookup value is the amount of the investment. The lookup table is the dividend table. Thereturn value is the percentage that will be applied to the investment amount to calculate thedividend.The return value, however, causes a problem as it depends upon the scale chosen by the investor.This problem is overcome by using an IF function to ascertain which scale was chosen and to usethis information to select the appropriate value from the table.Another IF function can be used to test whether the investor signed up within the bonus period andto apply the appropriate bonus amount. Watsonia PublishingPage 28Formula Techniques

Microsoft Excel 2010 - Level 2DEVELOPING A NESTED FUNCTIONThe best way to develop a complex formula isby developing each of the components first andthen combining them. By writing each of the partsin sentence form, you will be able to understandthe logic of each more easily. You can thenestablish where the individual parts go in theoverall scheme of your formula, create a basefunction, and then build your formula from thereDeveloping the Workshop ExampleThe overall formula for the workshop example can be stated as: Investment Amount * (Dividend Percentage Bonus)The Dividend PercentageThe logic of the Dividend Percentage calculation can be stated as:Look up the investment amount in the dividend tableIf the investor chose scale A, then return the percentage from column 2 in the tableIf the investor chose scale B, then return the percentage from column 3Otherwise, return the percentage from column 4The base function used to calculate the Dividend Percentage can be written as:VLOOKUP(investment amount, dividend table, return column)The Investment ScaleWe then need to add IF functions within the VLOOKUP to allow for the three scales. If there wereonly two scales, the IF function could be written as:IF(scale chosen A, return column 2, otherwise return column 3)Because there are three scales, we need to add the second IF function to provide the extra choice.The final IF functions can be written as:IF(scale chosen A, return column 2, IF(scale chosen B, return column 3, return column 4))The BonusFinally, we need to determine whether or not to pay the Bonus. The logic of the Bonus calculationcan be stated as:If the joining date is earlier than the bonus date, then add 5%, otherwise add nothingThis can also be done using an IF function, written as:IF(the joining date is earlier than the bonus date, pay 5%, otherwise don’t pay anything)Now all we need to do is translate these into Excel terminology and add cell references. We will startby creating the base function, so that it can be tested, and then we will add the additional parts of theformula. Watsonia PublishingPage 29Formula Techniques

Microsoft Excel 2010 - Level 2CREATING NESTED FUNCTIONSA nested function is created by placing afunction as a parameter within another function.When you create nested functions the placementof brackets is especially important and should be3OpenFileTry This Yourself: checked carefully. Excel helps you with this task byusing different colours for matched pairs ofbrackets. You can also improve the readability ofthe formula by placing the parts on separate lines.Before starting this exerciseyou MUST open the file E818Formula Techniques 1.xlsx.Click in cell G12Type VLOOKUP(E12, D 3: G 8,4Press to create anew line – splitting the parts ofthe formula up makes it easierto readType IF(F12 "A",2,7Pressnew line to create aType IF(F12 "B",3,The final part of the formula isthe ‘false’ part of the last IFstatement 8Type 4)))Pressformulato complete theThe value 0.02 should appear– the rate taken from G5 whichis the 10,000 investment atScale C 9Click in cell F12, type A thenpressto see the dividendrate for scale AFor Your Reference Handy to Know To create a nested function:1. Type the function as required2. Use to create new lines Excel uses colour in formulas to indicate cellreferences. When you edit an existingformula, the cells referred to will appearoutlined in a specific colour, and thecorresponding cell references in the formulawill appear in matching colours.3. Check the placement of brackets carefully Watsonia PublishingPage 30Formula Techniques

Microsoft Excel 2010 - Level 2EDITING NESTED FUNCTIONSOnce the base function has been created andtested, the additional parts of the nested functioncan be added by editing the formula. Nestedfunctions are edited the same way as any otherSameFileTry This Yourself: Excel formula. When you click on the cell, theformula is displayed in the formula bar and canthen be modified. Excel also uses colouredreferences to help you understand the formula.2Continue using the previous filewith this exercise, or open the fileE818 FormulaTechniques 2.xlsx.Click on G12 to select itClick immediately after the firstequal sign in the Formula barThis places the formula in editmode and displays matchedcoloured references. For example, D 3: G 8 appears in green inthe formula and is outlined ingreen on the worksheet 4Type E12*(This ensures that the investmentamount will be multiplied by theDividend percentage Click onat the right end of theFormula bar to expand it, thenclick at the end of the formula andpress 5On the new line, type IF(D12 B 3,5%,0))This adds the bonus percentage ifthe start date is earlier than thebonus date 6Pressto complete theformula then click onto restorethe formula bar to one lineFor Your Reference Handy to Know To edit a nested function:1. Click on the cell to select it2. Click in the formula in the Formula bar andedit as required3. Pressto complete the changes When you have finished editing a formula,you can click on the Enter buttonto enterthe formula. If you make a mistake, you canclick on the Cancel buttonor presstoexit from Edit mode. Watsonia PublishingPage 31Formula Techniques

Microsoft Excel 2010 - Level 2COPYING NESTED FUNCTIONSYou can copy formulas with nested functionsthe same way that you copy any formula.However, you should be especially careful of thecell addresses. Relative cell addresses will adjustautomatically when you copy files, while absoluteaddresses will remain unchanged. These can bedifficult to identify in a complex formula, so it is wiseto check the results thoroughly after copying.SameFileTry This Yourself: Continue using the previous filewith this exercise, or open thefile E818 FormulaTechniques 3.xlsx.Click on G12 then click onExpand Formula Barandexamine the formulaThe only absolute referencesare D 3: G 8, and the Bonusdate B 3. The rest are relativewhich will adjust as you copy theformula. These include the datejoined, investment amount andscale 1Click on Collapse Formula Barto return it to one line3Move the mouse pointer to thefill handle at the bottom rightcorner of G12 then double-clickto copy the formula down thecolumnThis technique for copying worksas long as you have acontinuous list on the left of theoriginal cell Click on G22 then double-clickon the AutoSum tooltocalculate the total of thedividends4AutoSum will create a Sumfunction for the cells immediatelyabove itFor Your Reference Handy to Know To copy a nested function:1. Check the cell references2. Copy the formula using your preferredcopying technique When you double-click on a formula, it willplace it in Edit mode. Cells referred to in theformula will appear with coloured outlines.If you want the formula to use exactly thesame cells as those outlined, no matterwhere the formula is copied to, the cellreferences must be absolute. Watsonia PublishingPage 32Formula Techniques

Microsoft Excel 2010 - Level 2USING CONCATENATIONconcatenation is the ampersand &. All text must beenclosed in quotation marks. The TEXT() functioncan be used to convert dates and values into textso that they can be included in the result too.Concatenation is the process of joining textand/or values together. This can be used to forma phrase or expression from existing data in theworksheet. The main character used forSameFileTry This Yourself: Continue using the previous filewith this exercise, or open thefile E818 FormulaTechniques 4.xlsx.Click on H11 and type Report,then pressIn cell H12, type the following,using to create newlines, and including the spacesbefore and after the words C12 & " " & B12 &" joined the fund on " & 23Complete the rest of the functionas shownPressto create the formulaCopy the formula to the rangeH13:H21 and then clickelsewhere, to deselect itA report has been created forevery investor5For Your Reference Handy to Know To concatenate text and values:1. Use ampersand (&) to add items2. Enclose actual text in quotation marks,including spaces3. Convert dates and values using TEXT() You could use the report created by theconcatenation in a mail merge usingMicrosoft Word to produce individual reportletters for each client. Watsonia PublishingPage 33Formula Techniques

Microsoft Excel 2010 - Level 2SWITCHING TO MANUAL RECALCULATIONRecalculation refers to processing the formulasin a spreadsheet to calculate new results.Formulas are usually recalculated each time avalue in a dependent cell changes, but you canSameFileTry This Yourself: turn off automatic recalculation and instead setExcel to manual. This means that no formulas willbe recalculated unless you specifically requestExcel to perform the calculations.1Continue using the previousfile with this exercise, or openthe file E818 FormulaTechniques 5.xlsx.Click on F13 and type B thenpressThe dividend and total willupdate to reflect the change.Click on the Formulas tab inthe Ribbon and click on theCalculation Options toolto display the menu2You can see that it’s set toAutomatic Select Manual, then click inF16Type A and pressYou’ll notice that nothingchanges, even though scale Agives a different percentageand should result in a higherdividend4For Your Reference Handy to Know To switch to manual recalculation:1. Click on the Formulas tab The Calculation settings are global and willaffect every spreadsheet you work with. Youcan’t force only one workbook to requiremanual recalculation without affecting others(unless you’ve done some fancyprogramming, but that’s another story).2. Click on Calculation Options3. Select Manual Watsonia PublishingPage 34Formula Techniques

Microsoft Excel 2010 - Level 2FORCING A RECALCULATIONIf you turn off manual recalculation, you will needto force recalculation at some stage to updatethe formulas. There are several options tochoose from for recalculation. You can1OpenFileTry This Yourself: recalculate any changed formulas in the currentworksheet or all open workbooks, recalculate allformulas in all workbooks irrespective of changes,or check all formulas before recalculating.Before starting this exerciseyou MUST open the file E818Formula Techniques 6.xlsx.Click on F20 and examine thescale, dividend and totalamountsType A and pressNothing happens.On the Formulas tab, click onCalculate Now2The formulas will berecalculated and the resultswill be updated.Click on Calculation Optionsand select AutomaticNow recalculation will happenautomatically when values arechanged3For Your Reference Handy to Know To force a recalculation:1. Click on the Formulas tab To recalculate the current worksheet, click onCalculate Sheeton the Formulas tab orpress . To recalcuate all workbooksirrespective of changes, press . To check the formulas as well asrecalculate, press .2. Click on Calculate Now Watsonia Publishingor pressPage 35Formula Techniques

Microsoft Excel 2010 - Level 2PASTING VALUES FROM FORMULASSometimes it’s useful to be able to grab theresults of a calculation and use the valueelsewhere in a spreadsheet without keeping theformula. For example, you may want to keep aSameFileTry This Yourself: 1Continue using the previousfile with this exercise, or openthe file E818 FormulaTechniques 7.xlsx.Select the range B12:B21 thenhold downand selectG12:G21The first part of the rangecontains text and the secondcontains formulas. copy of some data at a certain point in time,knowing that it won’t change at a later date. Youcan do this by copying formulas and pasting onlythe values.3Click on the Home tab on theRibbon and click on CopyClick on B24, then click on thedrop arrow for Pasteandclick on Values Pressto hide the copymarquees, then click on C24and check the entry in theFormula barYou’ll see that only the value500 has been pasted – theformula isn’t retained4For Your Reference Handy to Know To paste values from formulas:1. Click on the formula2. Click on Copy3. Click in the destination cell4. Click on the drop arrow for Paste When you copy formulas, you have theoption to paste formulas, values and links. Alink is a reference to the cell containing theformula. For example, if the cell containingthe copied formula is G12, the link created bypasting will be G 12.andclick on Values Watsonia PublishingPage 36Formula Techniques

The overall formula for the workshop example can be stated as: Investment Amount * (Dividend Percentage Bonus) The Dividend Percentage The logic of the Dividend Percentage calculation can be stated as: Look up the investment amount in the dividend table If the investor chose scale A, then return t

Related Documents:

Part One: Heir of Ash Chapter 1 Chapter 2 Chapter 3 Chapter 4 Chapter 5 Chapter 6 Chapter 7 Chapter 8 Chapter 9 Chapter 10 Chapter 11 Chapter 12 Chapter 13 Chapter 14 Chapter 15 Chapter 16 Chapter 17 Chapter 18 Chapter 19 Chapter 20 Chapter 21 Chapter 22 Chapter 23 Chapter 24 Chapter 25 Chapter 26 Chapter 27 Chapter 28 Chapter 29 Chapter 30 .

TO KILL A MOCKINGBIRD. Contents Dedication Epigraph Part One Chapter 1 Chapter 2 Chapter 3 Chapter 4 Chapter 5 Chapter 6 Chapter 7 Chapter 8 Chapter 9 Chapter 10 Chapter 11 Part Two Chapter 12 Chapter 13 Chapter 14 Chapter 15 Chapter 16 Chapter 17 Chapter 18. Chapter 19 Chapter 20 Chapter 21 Chapter 22 Chapter 23 Chapter 24 Chapter 25 Chapter 26

DEDICATION PART ONE Chapter 1 Chapter 2 Chapter 3 Chapter 4 Chapter 5 Chapter 6 Chapter 7 Chapter 8 Chapter 9 Chapter 10 Chapter 11 PART TWO Chapter 12 Chapter 13 Chapter 14 Chapter 15 Chapter 16 Chapter 17 Chapter 18 Chapter 19 Chapter 20 Chapter 21 Chapter 22 Chapter 23 .

Table 68: Shirt Laundry Formula 04: White (No Starch) Table 69: Shirt Laundry Formula 05: Colored (No Starch) Table 70: Shirt Laundry Formula 06: Delicates Table 71: Shirt Laundry Formula 07: Stain Treatment Table 72: Shirt Laundry Formula 08: Oxygen Bleach Table 73: Shirt Laundry Formula 09: Stain Soak Table 74: Shirt Laundry Formula 10 .

the empirical formula of a compound. Classic chemistry: finding the empirical formula The simplest type of formula – called the empirical formula – shows just the ratio of different atoms. For example, while the molecular formula for glucose is C 6 H 12 O 6, its empirical formula

A Note about Array formulas (not for Excel 365 / Excel 2021) Sometimes, you will need to enter a formula as array formula. In Excel 365/Excel 2021, all formulas are treated as Array formula, hence you need not enter any formula as Array formula. Only for older versions of Excel, you might need to enter a formula as Array formula.

The F1 FORMULA 1 logo, F1 logo, FORMULA 1, FORMULA ONE, F1, FIA FORMULA ONE WORLD CHAMPIONSHIP, GRAND PRIX and related marks are trade marks of Formula One Licensing BV, a . FORMULA 1 HEINEKEN DUTCH GRAND PRIX 2022 - Zandvoort Race History Chart. LAP 6 GAP TIME 1 1:16.350 16 1.051 1:16.213 . Race History Chart. LAP 11 GAP TIME 1 1:16.671 16 .

The F1 FORMULA 1 logo, F1 logo, FORMULA 1, FORMULA ONE, F1, FIA FORMULA ONE WORLD CHAMPIONSHIP, GRAND PRIX and related marks are trade marks of Formula One Licensing BV, a . FORMULA 1 HEINEKEN AUSTRALIAN GRAND PRIX 2022 - Melbourne Race History Chart. LAP 6 GAP TIME 16 2:24.953 . Race History Chart. LAP 11 GAP TIME 16 1:23.356 1 3.085 1:24 .