Exercise 0
Contents
1. B merme Ca aa i Figure 9 The Data_array is simply the data you want to summarize given in cells B2 B11 The Bins_array is cells C2 C5 Instead of clicking OK press lt Control gt lt Shift gt lt Enter gt on Windows machines Excel will return your frequencies On Macs type the formula in by hand then press Return After you ve obtained your results examine the formu las in cells D2 through D6 Figure 10 Every cell will have a formula that looks like this FREQUENCY B2 B11 C2 C6 The symbols indicate that the formula is part of an array rather than a standard formula 12 Introduction Ehrh Cal Gach Ejer mii pe pee ea Dead oh cat fever Hele Coe ee a ga Erai ene BS ER Sh ax WA Ee DL fie PEN Y E E1 1 0 OB A m T F p H Aisiak Wight gi Ber F kapi i a a 3 r r 2 F Ci 1 a 1 5 1 4 rT J E 4 q T E L B F 5 g a 3 1 E 4 Figure 10 Relative and Absolute Cell Addresses Cell addresses are said to be either relative or absolute It s critical that you know the difference between these two kinds of addresses A relative address refers to the position of a cell relative to the position of the currently selected cell For example if you enter the formula 2 B2 into cell C3 the cell address B2 does not really refer to cell B2 it refers to a cell one column to the left and one row up from the cell you re typing into cell C3 If you copy this formula into cell D2. INTRODUCTION SPREADSHEET HINTS AND TIPS This introduction covers procedures that you ll use in the exercises throughout this book It is intended to be a ready reference and as such it has a different for mat than the exercises The first two exercises Mathematical Functions and Graphs and Spreadsheet Functions and Macros apply some of the procedures discussed here to the exercise format and give you an opportunity to practice them If you are already familiar with spreadsheets you may want to skip this chap ter or perhaps just check out any unfamiliar topics To help you find what you re interested in here s an outline Starting Up p 2 Menus and Commands p 2 Spreadsheet Structure p 4 Selecting Highlighting Cells p 4 Copying Cell Contents p 5 Cutting Cell Contents p 5 Pasting Into a Cell p 5 Cell Addresses p 5 Entering Literals p 5 Entering Formulae p 7 Calculation Operators in Formulae p 7 Entering Functions p 9 Array Functions p 10 Relative and Absolute Cell Addresses p 12 Filling a Series p 12 Formatting Cells p 13 Creating a Graph p 14 Editing a Graph p 16 Automatic and Manual Calculation p 16 Macros p 16 Glossary of Terms and Symbols p 18 Three warnings First this chapter is not a substitute for your spreadsheet user s manual We base our instructions throughout the book on Microsoft Excel and most will work as written in other spreadsheets but the
3. or Return Macs to enter the formula for all of the cells you have selected For example the FREQUENCY function is an array function that calculates how often values occur within a range of values and then returns a vertical array of num bers Suppose you want to construct a frequency distribution for the weights in grams of 10 individuals Figure 8 Spreadsheet Hints and Tips 11 ENB Ed ver yon fma ook aofork ec Weie jie Dol Seay Ft a o g E Ae 3 si cee b ed oe he a i a a Dh ie ii lh Li Sh a i Figure 8 In Figure 8 the column labeled Bins tells Excel how you want your data grouped You can think of a bin as a bucket in which specific numbers go The bins may be very small hold only a few numbers or very large hold a large set of numbers For exam ple suppose you want to count the number of individuals that are 1 g 2 g 3 g 4 g and 5 g The numbers 1 through 5 represent the five bins If we want Excel to return the num ber of individuals of given weights in cells D2 D6 then we need to first select those cells rather than a single cell before using the paste function key to summon the frequency procedure The dialog box in Figure 9 will appear Hre Omi SRS Pte cja eE a w aia woe le a el FER CM tiers A biha vap pi Ae L J 5 queer fate arep Je ien ava s aaeeea a a eee Feaareac erected aren eb eer a pimi Dikin kiray De ee te ta E EEI E
4. Insert Function to guide you through entering a function Either way the dialog box headed Paste Function will appear Figure 6 Paste function key ME iroda Height fom P 10 E T aui count a ilerdia dragon kega smail rund tree nord Coun Figure 6 Look at the column on the left side of the dialog box labelled Function category It asks what kinds of functions you want to examine In the figure the Most Recently Used cat egory was selected so a list of the most recently used functions appears in the right side of the dialog box Note that the function SUM is selected and the program displays a 10 Introduction brief description of the SUM function at the bottom of the window If you choose the Function category All you ll see every function available listed in alphabetical order Use your mouse to select the function you want and a brief description of the func tion will appear Click OK when you ve got the function you want When you select a function a new dialog box will appear Figure 7 In Figure 7 we selected the SUM func tion Excel asks you to specify the cells you want to sum There are two handy features in this dialog box First notice the small figure with the arrow pointing upward and left ward located to the right of the blank space labeled Number 1 If you click on this arrow the dialog box will shrink exposing your spreadsheet so that you can use your mouse to select th
5. left of the insertion point or delete the selection Delete Right delete Delete the character to the right of the insertion point or delete the selection Arrow keys Arrow keys Move one character up down left or right Home Home Move to the beginning of the line End End Move to the end of the line Control Home Home Move to the beginning of a worksheet Control end End Move to the last cell on the worksheet Control x x Cut the selection Control v iv Paste the selection Control c ic Copy the selection Control z Gz Cancel or undo an entry in the cell or formula bar Control y G y Repeat the last action Control f f Open the Find dialog box Control s s Save your work Control d Gd Fill down Control r r Fill to the right Control F3 1 Define a name F1 G Opens Help menu F4 t Makes cell reference absolute or relative in the formula bar F9 G Calculate or re calculate all sheets in all open workbooks Tools Options Tools Preferences Set manual versus automatic calculation Calculation Calculation The Calculate key F9 is used extensively throughout these exercises The F9 function key will work on Macintosh machines provided the Hot Function Key option in the Keyboard Control dialog box is turned OFF If the F9 key does not work on your Mac use the alter native menus and or options as shown in Figure 1 Your mouse may have one two or three buttons All operations describe
6. 1 two cells down from cell C1 is cell C3 Figure 2 Addresses Columns Figure 2 Selecting Highlighting Cells To enter information into a cell you must first select it by placing the cursor the on screen arrow init and clicking the mouse button You can move the cursor either with the mouse or with the arrow keys You can tell a cell has been selected because it will be highlighted either the entire cell or its outline will be shown in a different color from other cells You can simultaneously select more than one cell by any of the fol lowing procedures Spreadsheet Hints and Tips 5 If the cells are in a contiguous block e Move the cursor to one corner of the block of cells e Click and hold the mouse button as you drag the cursor to the opposite corner of the block Release the mouse button when the cursor is in the cell at the opposite corner of the block or e Select a cell at one corner of the block of cells e Move the cursor to the opposite corner of the block e Hold down the lt Shift gt key and click the mouse button If the cells are not in a contiguous block e Use either procedure above to select some of the cells e Select additional cells by holding down the lt Control gt key while clicking and dragging e Continue selecting rows columns or blocks until you have selected all the cells you want Copying Cell Contents Copy the contents of a cell or of multiple cells by selecting t
7. 4 This dialog box is very important because it is your opportunity to label the graph its axes and legend It is extremely important to label your graphs thoroughly including units when appropriate eigin fel asdf Aggi Figure 14 16 Introduction In the final dialog box Chart Location Figure 15 you will be asked to specify where to save the graph Figure 15 Most commonly and by default we choose to save the graph on the spreadsheet but in some circumstances you may want to save it on a sep arate sheet Click on the Finish button and your chart will appear on your spreadsheet Chart Wicard Step 4 of 4 Chart Location Fy Place chart 1 As ran sheet twtr SS ita Gaern LT I Figure 15 Editing a Graph After you have created a graph you can change its appearance by editing it in various ways To begin select the graph by clicking anywhere in it To change a feature of the graph double click two mouse clicks in rapid succession on the feature you want to change and choose the desired options from those offered in the resulting dialog box es When you have finished changing that feature click on OK For example to change an axis to a logarithmic scale double click on the axis click in the box for log arithmic scale and click OK Alternatively you may open the Chart menu after selecting the graph The submenus within the Chart menu will allow you to modify nearly any feature of the gra
8. 5 the program will automatically change the formula into 2 C4 which is one column to the left and one row up from cell D5 In Excel the dollar sign indicates an absolute address An absolute address always refers to the same cell even if you copy or move the formula to a new cell For example if you enter the formula 2 B 2 into cell C3 the cell address B 2 really does refer to cell B2 regardless of which cell holds the formula If you copy this formula into cell D5 it will still read 2 B 2 Addresses without dollar signs are relative addresses Other programs may use symbols other than to indicate an absolute address You can mix relative and absolute references in one address In the address B2 the column reference is absolute and the row reference is relative In the address B 2 the column is relative and the row is absolute In the Windows version of Excel you can quickly add dollar signs to cell addresses by pressing the F4 button at the top of your keyboard Filling a Series In many exercises you will be told to create or fill a series of values usually in a col umn What we mean is to create a sequence of numbers like the one shown in col umn A Cells A5 A9 of Figure 11 You can do this in either of two ways The first is e Give the program an example of what you want e g enter 1 into cell A5 and 2 into cell A6 e Tell the program to extend this series by selecting the example cells A3 and A4 then plac
9. B1 Operator Meaning Example Arithmetic operators plus sign Add 3 3 hyphen Subtract 3 1 hyphen Negation negative value 1 asterisk Multiply 3 3 forward slash Divide 3 3 percent sign Percent 20 caret Exponentiation 1043 10 to the third power or 1 000 Comparison operators equal sign Equal to A1 B1 gt right angle Greater than A1 gt B1 lt left angle Less than A1 lt B1 gt Greater than or equal to A1 gt B1 lt Less than or equal to A1 lt B1 lt gt Not equal to A1 lt gt B1 A1 amp A2 becomes ATA B5 B15 Produces one reference to all the cells between B5 and B15 including those two cells SUM B5 B15 D5 D15 Combines multiple references into one reference Recall that the equal sign is also a start signal that tells Excel to consider what follows and the carat represents exponentiation raising to a power Other arithemetic operators include the standard and e Comparison operators compare two values for example whether two values are equal or one is greater than the other and return a logical value either true or false for specified calculations e The ampersand amp is the text concatenation operator It joins or concate nates two strings of text to produce a continues text string e Reference operators are the colon and the comma These operators com bine ranges of cells for calcula
10. alue to appear in cell B16 so we entered B15 B14 into cell B16 Although the result 6 0 is shown in the cell the formula bar shows the formula Formula bar a File Edit Vire Inzert Form Tools Data Windew Help EEFI ear she PE AG OR aloe amp F Hetestica i B J EE 3s E EA i a i i Or amp A Als Example lads 1 Sprecher Hnis and Tips zl 2 Spractstect Example iiia Heig om i igi ion k iD i aD aD r AT Sampie Size 5D Sum m0 Mean BE Minimum 40 Maimam iD Flange S Gew Figure 5 Calculation Operators in Formulae Spreadsheet operators are keyboard entries that specify the type of calculation that you want to perform on the elements of a formula Microsoft Excel has four different types of calculation operators arithmetic comparison concatenation and reference These are listed in Table 2 e Arithmetic operators perform basic operations such as addition subtraction or multiplication combine numbers and produces numeric results The asterisk is used to specify multiplication the forward slash represents division 8 Introduction Table 2 Calculation Operators in Microsoft Excel Formulae Text concatenation operator amp ampersand Join two values to produce one continuous text value Reference operators colon Range operator comma Union operator as a formula as in A1
11. d in this section are performed with the left button In current Macintosh and Windows operating systems a single mouse click will open a menu and keep it open To execute a command from a menu move the cursor over the available commands until the one you want is highlighted and then click the mouse a second time On Macintoshes running older operating systems you must click the 4 Introduction mouse button and hold it down as you move the cursor down the menu options Release the mouse button when the command you want is highlighted The command will flash when it is successfully invoked For instance if you wanted to record a macro in your spreadsheet to carry out a set of instructions you would open the Tools menu select the Macro submenu and choose the Record New Macro Option Throughout this book we will use the vertical bar 1 and sans serif type Menu to indicate a menu submenu or option Thus the instruction above would read Open Tools Macro Record New Macro The results of this opera tion are shown in Figure 1 and discussed in more detail on p 16 Many menu commands also have keyboard shortcuts key combinations that you can press to execute the command without having to open a menu and sort through its submenus and options Shortcuts are listed next to the commands in the menus and always begin with lt Control gt in Windows and with ona Macintosh followed usu ally by a single letter see Table 1 To
12. e presented with a series of dialog boxes that take you through the process of creating a graph After finishing each dialog box move to the next by clicking on the OK button Chet Siren ep 1 m 4 Chai l pp Ei Eg Figure 12 In the first dialog box Chart Type click on the kind of graph you want to create Figure 12 You will frequently choose an X Y axis scatterplot XY Scatter or sometimes a line graph Line or a vertical bar graph Column or other Spreadsheet Hints and Tips 15 We strongly advise you to avoid chart junk Three dimensional graphs lots of colors and bizarre chart types usually detract from the readability of a graph Keep in mind that your purpose is to communicate clearly and immediately not to impress with fancy graphics In the second dialog box Chart Source Data you will be given some choices about the data to be graphed Figure 13 Most often the default settings will work but some times you may have to tell the program that your data are arranged in rows rather than columns or vice versa The Series tab provides additional options This window enables you to name a series of values such as weight and to specify the x and y val ues to be used in the chart if the default values are not appropriate T E E La m bi a Figure 13 In the third dialog box Chart Options you will be presented with a variety of choices for formatting your graph Figure 1
13. e range of cells you want to sum After you ve selected the cells you want to sum in this case cells B2 B6 click on the arrow again and the SUM dialog box will reappear Click OK and Excel will return the calculated value Figure 7 Note that although the box is labeled Number 1 it is not limited to a single cell address but can and often should hold a range of cell addresses You can also type cell addresses or ranges of cell addresses into the boxes if that s easier The second handy feature of all paste function dialog boxes is the question mark located at the bottom left corner of the window If you don t know how the function works click on the question mark and Excel will provide more information After you ve become familiar with some frequently used functions you may find it faster to type them into a cell directly Like formulae functions begin with an equal sign to alert the program that they are not literals Array Functions In some exercises you will use an array function rather than a standard function An array function acts on two or more sets of values rather than on a single value These sets of values are called array arguments You create array formulae in the same way that you create other formulae with this major exception Instead of selecting a single cell to enter a formula you need to select a series of cells then enter a formula and then press lt Control gt lt Shift gt lt Enter gt Windows
14. e steps can be used to adjust several columns to a uniform width A second procedure is 14 Introduction e Open Format Column AutoFit Selection Excel will adjust the column width to permit display of the widest element in the selected block or column A third alternative e Place the cursor at the right hand edge of the space around the letter at the top of the column to be adjusted The cursor will change to a vertical bar with arrows pointing to the right and the left Click and hold down the mouse button e While holding down the mouse button drag to the right to widen the column or to the left to narrow it e When the column width is appropriate release the mouse button Creating a Graph Most spreadsheet programs call graphs charts We will follow scientific usage and call them graphs In these exercises you ll make lots of graphs To create a graph chart you must tell the program e Which data to graph e To start a graph e Which kind of graph to use e Other details of how to set up the graph Select data to graph by selecting the appropriate cells see p 4 5 Excel will always place the leftmost column or topmost row of data on the horizontal axis of the graph If you want to change this move columns or rows using the cut and paste proce dures described on page 5 To start a graph click on the Chart Wizard button the little bar graph in the toolbar Figure 11 or open Insert Chart You will b
15. he cell or cells and using either the Edit Copy command or the keyboard shortcut c or lt Control gt c Cutting Cell Contents Cutting is similar to copying except that copying leaves the original cell s unchanged whereas cutting deletes the contents of the cut cell s once they have been pasted into another cell The Cut command is Edit Cut under the Edit menu the shortcut is x or lt Control gt x Pasting into a Cell Paste information that you copied or cut from one cell into another cell by executing the Edit Paste command or the keyboard shortcut v or lt Control gt v Cell Addresses Every cell has an address consisting of its column letter and row number The top left cell s address is A1 two cells to the right is cell C1 two cells down from C1 is cell C3 see Figure 2 When you carry out spreadsheet operations such as finding the sum of two cells or the mean of a column of cells you must tell the program the addresses of the cells to operate upon You use addresses rather than entering the values to oper ate upon because this allows you use a principal advantage of spreadsheet programs their ability to update calculations when you change cell contents You can type single cell addresses A1 C3 etc or you can type a range of cell addresses in the form A1 C3 The latter designates a contiguous block of cells with its top left corner at cell Al and its bottom right corner at cell C3 You can designate any con
16. indows platforms most however were developed in Windows Table 1 gives some alternative commands and keystrokes that may help if the instructions are not tailored to your machine Menus and Commands Most spreadsheet programs have graphical user interfaces in which you use a mouse to choose commands from menus across the top of the screen Many menus have sub Submenu 4 Menu File Edit View Insert Format PCLS Data Window elp FOS eR SAS y maa te Share Warkbok EEIE F E Heleeticn 7 i B Ff JU TEA g o o ajo E Verpe Workbooks iH Protection bj z aal isnt 1_Spexshoni Hnis and Tips TEL 2 Gpeamhai Exam ple Auditing b 3 r i Tobarra emu Hagi Wieg i i 5 1 ing ii hitro A Macros 6 E 40 15 Add jig Recan Mew Morro al 3 60 22 Custam Visual Basie Editor B 4 60 2 Preferences 5 ra a Wizard 10 DALA Analy si 11 Sanpie Se 50 am ae 12 Sum 330 1180 13 Mn AA 235 14 Birinu 40 150 15 Mamam 100 46 0 18 Farge BO 20 0 17 Sender Day 22 75 Figure 1 Spreadsheet Hints and Tips 3 Table 1 Some Commonly Used Keyboard Commands in Microsoft Excel Windows Macintosh Action Enter Return Complete a cell entry and move down in the selection Tab Tab Complete a cell entry and move to the right in the selection Control Shift Enter Return Enter a formula as an array formula Esc Esc Cancel a cell entry Backspace Delete Delete the character to the
17. ing the cursor at the bottom right corner of the last cell in the example cell A6 e The cursor will turn into a bold cross Click and hold the mouse button while dragging down the column to cell A9 e The program will extend the series down the column showing you the current value in a small box as it goes e When the series reaches the maximum desired value release the mouse button The alternatetive way to fill a series is e Enter the first value of the series in the first cell enter 1 into cell A5 Spreadsheet Hints and Tips 13 e Enter a formula to calculate the next value in the series into cell A6 A3 1 e Copy the formula in cell A6 select the cell and press lt control gt c or c e Select the cells to hold the rest of the series select cells A7 A9 e Paste the formula into the selected cells lt control gt v or v You can also just click on the bottom right hand corner of cell A6 the cursor will change to a bold cross and then drag the formula down to cell A9 Any of these proce dures will work with series in rows as well as in columns tite Edit view beceri ermal Tanis Gata Windaw aig foe BRS shee he ERT A stiestion i Be 7 uU RPE ex SS ER L S A Example tals Sveti Eyl i 16 a al 7a s0 vAN Figure 11 Formatting Cells The appearance of a cell s contents depends on how the cell is formatted To access all the options for formatting a cell or range of cell
18. ll mimic that entire sequence of actions whenever you press the shortcut key or issue the macro command Obviously planning pays off when recording a macro If you re creating your own macro go through the sequence of actions at least once in preparation to make sure it actually achieves the desired result Write down each action so that you can repeat and record them correctly If you re following our instructions to create a macro be care ful to execute each step precisely as given Remember the computer doesn t know what you want to do it records everything faithfully mistakes and all Exercise 2 Spreadsheet Functions and Macros provides exercises to help you mas ter creating macros GLOSSARY OF TERMS AND SYMBOLS Absolute address A cell address see Cell address that refers to a specific loca tion in the spreadsheet regardless of its position relative to the selected cell see p 12 An absolute address does not change if copied to a new loca tion In Excel an absolute address is indicated by preceding the column let ter or row number or both by a dollar sign Cell address The location of a cell in the spreadsheet The cell address consists of a letter representing the column and a number representing the row see p 5 Addresses may be relative see Relative address or absolute see Absolute address Formula A symbolic representation of a set of operations to be carried out by the spreadsheet see p 7 Us
19. or in the for mula bar Then use the backspace or delete key to erase the mistake or highlight the mistake using click and drag and retype The text will appear in the selected cell after you press lt Return gt If you discover an error later you can simply select the cell again and correct your mistake as above 2 Be bit ee jesi kpa fee fondo ues aati Ge alal CFR ri a e E baer Bid os nay ESIE ee AARRE LTA Al r E Ay you dape ae E D E ic H i J E E a ou ta 2 Figure 3 Highlighted cell Formula bar Sometimes strange things happen when you enter a literal depending on your pro gram and how it is set up For instance if you enter 5 10 meaning a range of values from 5 to 10 the cell may show May 10 This is because the program interprets some entries as dates To force the program to treat your entry as a literal precede it with an apostrophe 5 10 or open Format Cells General Another potentially confusing aspect of entering literals is spill over If the text you enter is too long to fit into a single cell it may spill over into adjacent cells if they are empty as does the text Spreadsheet Hints and Tips in cell A1 of Figure 4 The entire text is actually in cell A1 although it appears to occupy cell B1 as well because cell B1 is empty If the adjacent cell holds information the text is truncated rather than spilling over Note that the same text is present in cell A2 as you can see in
20. ph to suit your needs Automatic and Manual Calculation By default the spreadsheet program re calculates all formulae and functions every time you press the lt Return gt or lt Enter gt key or perform certain other actions This is called automatic calculation In some circumstances you will want to prevent this and take direct control of when calculations are updated This is called manual calculation You can choose whether calculation is automatic or manual by opening Tools Options Calculation on Windows machines or Tools Preferences Calculation on Macs After you set calculation to manual you can update all formulae and functions by pressing the recalculate key F9 on Windows or on Macs Macros Amacro is a miniature program that you create to run a sequence of Excel actions For example suppose you wanted to perform the same fairly long tedious series of actions many times Typing and mouse clicking your way through them over and over would not only be time consuming and boring but also error prone A macro allows you to achieve the same results with a single command You create a macro using Excel s built in macro recorder Start the recorder by open ing Tools Macro Record New Macro The program will prompt you to name the macro and create a keyboard shortcut Then a small window will appear with the macro recorder controls Figure 17 If this button does not appear go to View Toolbars Stop Recording and
21. r digit In a cell address the dollar sign indicates that the following column or row reference is absolute rather than relative See Cell address Absolute address and Relative address A In a formula the carat represents exponentiation That is 3 2 is equiva lent to 3
22. re may be differences in the details If you follow our instructions carefully and they don t work con 2 Introduction sult your spreadsheet user s manual Second you should already be familiar with some basic computer skills such as booting up your computer starting your spreadsheet program saving files and printing If you re not consult your operating system user s manual Third save your work frequently to disk Few things are as frustrating as spending hours building a model then losing all your hard work when the computer crashes Starting Up How you start up your spreadsheet program will depend on whether you use a Mac intosh an IBM compatible computer or a UNIX computer whether the computer is on a network or not and which spreadsheet program you choose Consult your oper ating system manual your spreadsheet program manual or a local computer expert All of the exercises in this book were developed with Microsoft Excel version 98 or higher which utilizes the Visual Basic for Applications code If you are using an older version of Excel or a different spreadsheet program make sure the basic functions used in the exercise are available Some exercises require the use of the Solver function an optimization function that is within the spreadsheet s Add In Pak Your system admin istrator may need to help you install the Solver These exercises were written by several authors using either Macintosh or W
23. s select the cell s and then open Format Cells You can also use toolbar shortcuts to format font size alignment num ber of decimal places borders shading or color With some exceptions an important one is formatting column width formatting cells is a matter of taste Our guiding principles have been to keep fancy formatting to a minimum and to format cells to enhance readability In the exercises in this book you will see cells with borders shading bold type and other formats Unless otherwise noted you need not reproduce these unless you wish to However some aspects of formatting cells are not just a matter of appearance If a num ber is too large to fit in the space provided by a cell it will be represented by hashmarks GHHHHH To see the number you must either reduce the number of decimal places which may not be applicable or desirable or expand the column width to accommodate the num ber There are several ways to format column width All begin with the same first step e Select the column to be formatted either by clicking in a cell in the column or by clicking on the column letter at the top of the column You can then follow one of three procedures The first procedure is e Open Format Column Width e Type a number in the dialog box e The relationship of the number to the column width is obscure i e we don t understand it so you ll have to experiment until you get the result you want The abov
24. the Stop Recording figure will appear The square on the left side of the button is the Stop Recording button Figure 17 When you press this square you will stop recording your macro The button on the right Spreadsheet Hints and Tips 17 a a File Edit Wiew Insert Format BPD Data Windew Help Doe ee OS Ge art a iA ah oe Share Werkbeak i2 BF aleei gt Ma eel ce eA Wenge Workbooks Protection Ld EE Figure 16 is the relative reference button By default this button is not selected so that your macro recorder assumes that the cell references you make in the course of developing your macro are absolute In other words if you select cell A1 as part of a macro Excel will interpret your keystroke as cell A 1 There are cases for example the survival analy sis exercise in which you will want to select the relative reference button as you create your macro a Foe Eit View Insert Format Teols Data Window Help OSM SRT sR Ho Oe Fh Bo a tertia BSD ESIM ex AA RRt Relative Reberence button EH birmu Madrum 100 0 Stop Recording burton 46 Fanga 60 20 17 Senda Dew 22 75 TH Figure 17 18 Introduction From this point on Excel will record every action you take Carry out the entire sequence of operations you want the spreadsheet to do and then press the Stop Record ing button in the macro recorder control window The program wi
25. the formula bar but because cell B2 holds the text Example the text in cell A2 is truncated a File Edit View insert Format Toa Pawia Window Help Oe SOT RET none pE A BEA oe a f Hiieri es i BS DUPRE Bm aA EELA az T Exemp he lxs i Figure 4 Spreadsheet Hints and Tips 7 Entering Formulae A very important part of spreadsheet programming is entering formulae A formula tells the spreadsheet to carry out some operation s on the contents of one or more cells and to place the result into the cell where the formula is A formula usually contains one or more cell addresses and operations to be performed on the contents of the ref erenced cells A formula must begin with a symbol to alert the spreadsheet that it is a formula rather than a literal In Excel the symbol is typically the equal sign but other symbols such as may work in this or other spreadsheet programs Two useful tips to remember regarding formulas e The formula appears in the formula bar as you type it and it will appear there again if you select the cell later But once you press lt Return gt only the result of the formula appears in the cell itself e A formula may not refer to the cell in which it resides therefore e g do not enter the formula 2 B2 into cell B2 This will generate an error message com plaining about a circular reference In Figure 5 we wanted the range of height values the maximum value minus the minimum v
26. tiguous block of cells by entering the addresses of any two opposite corners sepa rated by acolon A block may also consist of a single column e g A1 A10 or single row e g B3 B20 Other spreadsheet programs may use different symbols than the colon so consult your spreadsheet user s manual if the colon doesn t work Entering Literals The titles headings notes and other pieces of text or numbers that you want to appear on your spreadsheet are called literals because the program does not interpret them but represents them literally i e exactly as you type them To enter a literal select the cell in which you want the text to appear and type Press the lt Return gt or lt Enter gt key only when you have finished entering text The lt Return gt key ends text entry it does not give you a second line of text If you want a 6 Introduction label of more than one line one way is to type the first line press lt Return gt or the down arrow key place the cursor in the cell below if it s not already there and type the sec ond line Another way is to type all the text into a single cell and then format the cell to turn on text wrapping see p 13 for how to format cells As you type text or numbers into a cell what you type will appear in the cell and in the formula bar above the spreadsheet column headings Figure 3 If you make a mis take use your mouse to place the cursor on the mistake either in the cell
27. tions If you combine several operations in a single formula Microsoft Excel performs the operations in the order shown in Table 3 If a formula contains multiple operators with the same precedence i e if a formula contains both a multiplication and a division oper ator the program evaluates the operators from left to right You can change the order of evaluation by enclosing the part of the formula to be calculated first in parentheses Spreadsheet Hints and Tips 9 Table 3 Order of Operation in Microsoft Excel Formula Precedence Description Operator of calculation 1 Reference operators Ey 2 Negation 3 Percent 4 Exponentiation 5 Multiplication and division 6 Addition and subtraction 7 Concatenation 8 Comparison lt gt lt gt lt gt Entering Functions A function is similar to a formula but it usually carries out a more complex operation or set of operations and it has been prewritten for you by the spreadsheet program mers We use functions extensively many of the exercises in this book rely on them Excel has over 100 functions and you will probably not remember them all Fortunately most spreadsheet packages provide a simple means of entering functions so that you don t need to memorize them Functions are entered by pasting them into the formula bar You can use the Paste Function button on the toolbar f indicated by an arrow in Figure 6 or you can open
28. ually a formula contains one or more cell addresses and one or more mathematical operations to be carried out on the contents of those cells The result of the operation s appears in the cell in which the formula is entered In Excel formulae begin with the equal sign Function A prewritten formula or set of formulae see p 9 Enter a function by typing it in by opening Insert Function and choosing from the list or by clicking the Paste Function button f and choosing from the list In Excel functions begin with the equal sign Literal Text or a number that is not interpreted or manipulated by the spread sheet program see p 5 Row labels column labels and model constants are literals To force the program to treat an entry as a literal begin it with an apostrophe Macro A sequence of commands to be executed automatically see p 16 Relative address A cell address that refers to a location in the spreadsheet relative to the position of the selected cell see p 12 A relative address changes if copied to a new location preserving the original relationship Cell address es are relative by default in Excel and require no special symbol Series A column or row of values in sequence Most frequently these will be a simple linear series 0 1 2 3 See p 12 for shortcuts to enter a series In a formula the asterisk represents multiplication In text it represents a wildcard a stand in for any letter o
29. use a shortcut press and hold the lt Control gt or the key while simultaneously typing the indicated letter We will represent this simul taneous key pressing like this c on Macs or lt Control gt c Windows This is the shortcut for Edit Copy Many people use shortcuts for frequently used commands and you may find it worthwhile to memorize a few of these such as the one for copy and v Macs lt Control gt v Windows for Edit Paste Don t be afraid to thrash around in the menus In other words if you re not sure how to do something try opening menus and submenus searching for a command that looks like it might work Try different commands and see what happens This is how we learned most of what we know about spreadsheets However be sure to save your work before you start to thrash then just in case you do something that messes up your work you can close the file without saving any of the changes you made and the file will revert to what it was before you started thrashing Spreadsheet Structure A spreadsheet consists of a matrix or grid of cells Any cell can contain information text a number a formula or a function The columns of a spreadsheet are identified by letters the rows are identified by numbers although this may vary in different pro grams Each cell has an address consisting of its column letter and row number For example the top left cell s address is A1 two cells to the right is cell C
Download Pdf Manuals
Related Search