### Create a StudySoup account

#### Be part of our community, it's free to join!

Already have a StudySoup account? Login here

# Outline for EMSE 271 with Professor Dorp at GW

### View Full Document

## 21

## 0

## Popular in Course

## Popular in Department

This 26 page Class Notes was uploaded by an elite notetaker on Saturday February 7, 2015. The Class Notes belongs to a course at George Washington University taught by a professor in Fall. Since its upload, it has received 21 views.

## Popular in Subject

## Reviews for Outline for EMSE 271 with Professor Dorp at GW

### What is Karma?

#### Karma is the currency of StudySoup.

#### You can buy or earn more Karma at anytime and redeem it for class notes, study guides, flashcards, and more!

Date Created: 02/07/15

Excel Tutorial to Improve Your Efficiency Introduction Our purpose with this Excel tutorial is to illustrate some Excel tips that will dramatically improve your ef ciency We make no attempt to be as encyclopedic as some of the 800page Excel manuals available We concentrate on common tasks not every last thing that can be done in Excel Also we presume that you have some Excel knowledge We assume you know about rows and columns values labels and formulas relative and absolute addresses and other basic Excel elements If you know Virtually nothing about Excel you probably ought to work through an Excel for Dummies book and then work through this tutorial The style of this tutorial should be easy to follow Main topics appear in bold black type Speci c direction headings are in bright yellow and these are followed by detailed directions in bright red Additional comments about the directions appear in blue A key feature of this tutorial is that we have embedded numerous sample Excel spreadsheets so that you can try out the directions right awayiwithout switching into Excel When you doubleclick on one of these spreadsheets you launch Excel and the spreadsheet comes alive The menus and toolbars even change to those for Excel By clicking outside one of these spreadsheets you re back in Word A few of the topics are best carried out on your own PC as opposed to your school s networked PCs and we haven t included sample spreadsheets for these The reason is that they change the way a speci c copy of Excel is set up Ifyou do one ofthese exercises on your school s networked PCs the chances are that they won t take effect at least not permanently because of the way Excel is set up on the network These topics are preceded by asterisks Try them on your own home PC where you have complete control The easiest way to maneuver around this tutorial is to take advantage of builtin bookmarks Each main section is bookmarked To go to any section use Word s EditG0 To menu item select Bookmark under Go To What and choose from the dropdown list of bookmarks You can do this at any time to nd a topic you want to explore In fact try it right now to get a quick feel for the topics covered in this tutorial Finally we suggest that you save this leiRIGHT NOWias MyXLTutorialdoc or some such name and work with the copy That way if you mess anything up as you try the exercises you can always go back and retrieve the original le ExcelTutoriald0c Have fun Moving to the top ofthe sheet Often you want to reorient yourself by going back to the home position on the worksheet Press CtrlHome both keys at once Try it Down in the midst of the worksheet Using End arrow key combinations Press the End key then the appropriate arrow key For example press End and then right arrow to go to the right edge of a range Try it Starting at a comer a bordered cell move around to the other comers 1 1 5 5 7 6 1O 5 3 1O 1O 1O 5 9 5 4 3 4 7 4 5 1 8 9 The action of an Endarrow combination depends on where you start It takes you to the last nonblank cell if you start in a nonblank cell If there aren t any nonblank cells in that direction it takes you to the far edge ofthe sheet Ifyou start in a blank cell it takes you to the rst nonblank cell Splitting the screen It is often useful to split the screen so that you can see more information Click on the narrow screen splitter bar just to the right of the bottom scroll bar for vertical splitting or just above the righthand scroll bar for horizontal splitting and drag this to the left or down Splitting gives you two panes or four if you split in both directions Once you have these panes practice scrolling around in any of them and see how the others react Try it Split the screen either way and then remove the split 77 74 52 93 12 9 38 4 73 3 3O 31 37 98 O 45 35 3 Screen splitter barsl 34 23 1 1 8O 45 26 64 1 33 13 55 31 53 64 55 99 5 87 Selecting a range Usually in Excel you select a range and then do something to it such as enter a formula in it format it delete its contents and so on Therefore it is eXtremely important to be able to select a range ef ciently It s easy if the whole range appears on the screen but it s a bit trickier if you can t see the whole range In the latter case the effect of dragging the method most users try can be frustratingithings scroll by too quickly Try one of the methods below instead Click on one comer of the range and drag to the opposite comer Or Click on one comer hold down the Shift key and click on the opposite comer Try it Select the range B2D7 9 2 1O 1 3 8 8 1 1O 7 5 4 5 1 1 5 1O 7 Click on one comer of the range say the upper left comer Then holding the Shift key down use the Endarrow combinations End and right arrow then if necessary End and down arrow to get to the opposite comer Or Split the screen so that one comer shows in one pane and the opposite comer shows in the other pane Click on one comer hold the Shift key down and click on the opposite comer Try it Select the range B2C100 or the range E2N5 Try both of the methods suggested above 7 13 5 6 11 1 2 9 13 1 CONNNbCD NCD NLNLLWNW Selecting more than one range Say you want to format more than one range in a certain way as currency for example The quickest way is to select all ranges at once and then format them all at once Select the rst range press the Ctrl key select the second range press the Ctrl key select the third range and so on For example to select the ranges B2D5 and F2H5 click on B2 hold down the Shift key and click on D5 so now the rst range is selected hold down the Ctrl key and click on F2 and nally hold down the Shift key and click on H5 Try it Select all three numerical ranges shown 1O 13 7 9 1 9 12 7 4 1 1 6 12 15 2 13 4 1O Copying and pasting Copying and pasting usually formulas is one of the most frequently done tasks in Excel and it can be a real timewaster if done inefficiently Many people do it as follows They select the range to be copied often in an inefficient manner then select the EditCopy menu item then select the paste range again often inef ciently and nally select the EditPaste menu item There are much better ways to get the job done Select the copy range using one of the efficient selection methods described above press Ctrlc for copy select the paste range again efficiently and press CtrlV for paste The copy range will still have a dotted line around it Press the Esc key to get rid of it Try it Copy the formula in cell C2 down through cell C8 using Ctrlc and CtrlV Athth U lN Q Q Proceed as above but use the copy and paste toolbar buttons on the top toolbar instead of the Ctrlc and CtrlV key combinations Try it Copy the formula in C2 down through cell C8 using the Copy and Paste buttons QQ U1lgt U IU IWLU l w Buttons or key combinations It s a matter of personal taste but either is a lot quicker than menu choices A frequent task is to enter a formula in one cell and copy it down a row or across a column There are several very efficient ways to do this Starting with the top or lefthand cell select the range where the results will go Use the selection methods described earlier especially if this range is a long one Type in the formula and press CtrlEnter instead of Enter Try it Fill up the range C2C8 with CtrlEnter Formula should multiply the value in column A by the corresponding value in column B COODNNLCOCD COU1WCD O l Pressing CtrlEnter enters what you typed in all of the selected cells adjusted for relative addresses so in general it can be a real time saver For example it could be used to enter the number 10 in a whole range of cells Just select the range type 10 and press CtrlEnter Try it Fill up the range B2D8 with the value 10 by using CtrlEnter Enter the formula in the top or lefthand cell of the intended range Place the cursor on the drag handle at the lower right of this cell the cursor becomes a plus sign and drag this handle down or across to copy Try it Copy the formula in C2 down through C8 with the drag handle 7O NOOD OCON Enter the formula in the top or lefthand cell of the intended range Doubleclick on the drag handle This method uses Excel s builtin intelligence but it only works in certain situations Let s say you have numbers in the range A3B100 You want to enter a formula in cell C3 and copy it down to cell C100 Since this is a common thing to do Excel does it for you if you doubleclick on the drag handle It senses the lledup range in column B and gures you want another lledup range right neXt to it in column C If there were no adjacent lledup range double clicking on the drag handle wouldn t work Try it Copy the formula in C2 down through C8 by doubleclicking the drag handle 7 8 56 2 1 1O 2 4 1 4 5 3 8 7 8 Copying and Pasting with the SpecialValues option Often you have a range of cells that contains formulas and you would like to replace the formulas with the values they produce Usually you paste these values onto the copy range that is you overwrite the formulas with values However you could also select another range for the paste range Select the range with formulas press Ctrlc to copy and select the range where you want to paste the values which could be the same as the copy range Then since there is no keyboard equivalent select the EditPaste Special menu item and select the Values option Try it Copy the range D2 D8 to itself but paste values 45 28 64 80 42 IOJOJNLONA OOJJgtU1WA You might want to experiment with the other options on the EditPaste Special dialog box For example if you have a set of labels entered as a row and you want this same set of labels entered somewhere else as a column try copying and pasting special with the Transpose option Moving cutting and pasting Often you would like to move information from one place in the sheet to another Select the range to be cut press Ctrlx for cutting select the upper left comer of the paste range and press Ctrlv As with copying and pasting toolbar buttons can be used instead of key combinations but either is more ef cient than selecting menu items Also note that you only need to select the upper left cell of the paste range Excel knows that the shape of the paste range is the same as the shape of the cut range Try it Move the range A2C8 to the range D2 F8 Watch how relative addresses affect the eventual formulas in column F 2 3 6 7 4 28 3 6 18 3 9 27 6 7 42 6 6 36 1 4 4 Using absoluterelative references Absolute and references are indicated in formulas by dollar signs or the lack of them and they indicate what happens when you copy or move a formula to a range You typically want some parts of the formula to stay xed absolute and others to change relative to the cell position This is a crucial concept for ef ciency in spreadsheet operations so you should take some time to understand it thoroughly Here are two keys 1 The dollar signs are relevant only for the purpose of copying or moving they have no inherent effect on the formula For example the formulas 5B3 and 5B 3 in cell C3 say produce exactly the same result Their difference is relevant only if cell C3 is copied or moved to some range 2 There is never any need to type the dollar signs This can be done with the F4 key Enter a cell reference such as B3 in a formula Then press the F4 key In fact pressing the F4 key repeatedly cycles through the possibilities B3 neither row nor column xed then B 3 both column B and row 3 xed then B3 only row 3 xed then B3 only column B xed and back again to B3 Try it Enter the appropriate formula in cell B7 and copy across to E7 Scroll to the right to see the correct answer Figtlted cost 50 Variable cost 2 Month Jan Feb Mar Apr Units produced 224 194 228 258 Total cost I Try it again Enter one formula with appropriate absoluterelative addressing in cell C5 that can be copied to C5F9 Scroll to the right to see the correct answer Table of revenues for various unit prices and units sold Units sold 50 100 150 200 Unit price 325 3 50 3 75 400 4 25 Inserting and deleting rows or columns Often you want to insert or delete rows or columns Note that deleting a row or column is not the same as clearing the contents of a row or column that is making all of its cells blank Deleting means wiping it out completely Click on a row number and drag down as many rows as you want to insert and then press Alti and then r the menu equivalent of InsertRow The rows you insert are inserted above the rst row you selected For example if you select rows 8 through 11 and then insert four blank rows will be inserted between the old rows 7 and 8 Try it Insert blank rows for the data for Feb Apr and May Month Price Unitssold Revenue Jan 300 100 30000 Mar 325 50 16250 Jun 350 200 70000 Columns are inserted in the same way except that the key sequence is Alti and then c Try it Insert blank columns for sales reps Baker Miller and Smith so that the sales reps are in alphabetical order from left to right Sales rep Allison Jones Taylor Commission rate 54 65 43 Sales 15000 12000 17000 Commission 810 780 731 Click on a row number and drag down as many rows as you want to delete and then press Alte and then d the menu equivalent of EditDelete Columns are deleted in exactly the same way Try it The company no longer carries products K322 and R543 so get rid of their rows Product Code Units sold Unit price J645 1 48 1 5 00 K322 278 1750 L254 384 25 00 M332 13 30 50 R543 247 22 40 S654 315 35 00 Filling a series Say you want to ll column A starting in cell A2 with the values 1 2 and so on up to 1000 There is an easy way Enter the rst value in the rst cell 1 in cell A2 With the cursor in the starting cell A2 use the menu item EditFill Series to obtain a dialog box Change the Row setting to Column make sure the Type setting is Linear make sure 1 is in the Step Value box enter the nal value 1000 in the Stop Value box and click on OK As you can guess from this dialog box many other options are possible Don t be afraid to experiment with them Try it The series of days in colunm A should go from 1 to 25 in column D it should go from 26 to 50 Day Sales Day Sales 227 167 157 107 143 255 129 113 102 186 116 124 269 271 111 288 210 273 Using the summation button The SUM function is used so often to sum across rows or columns that a toolbar button the 2 button is available to automate the procedure To illustrate its use suppose you have a table of numbers in the range B3E7 You want the row sums to appear in the range F3F7 and you want the colunm sums to appear in the range B8E8 It s easy Select the ranges where you want the sums F3 F7 and B8 E87remember how to select multiple ranges and click on the summation button Note that if you select multiple cells you get the sums automatically If you select a single cell such as when you have a single column of numbers to sum you ll be shown the sum formula for your approval and you ll have to press Enter to actually enter it Why does Excel do it this wayiyour guess is as good as ours Try it Use the summation button to ll in the row and colunm sums 51 94 15 7 37 6 2 41 13 83 29 88 73 64 46 32 38 1 1 3 8O Using range names Range names are extremely useful for making your formulas more understandable After all which formula makes more sense B20B21 or RevenueCost Ef cient use of range names takes some experience but here are a few useful tips Select a range that you want to name Then type the desired range name in the upper left name box on the screen This box is just above the column A heading It usually shows the cell address such as E13 where the cursor is You could go through the InsertNameDe ne menu item but typing in the name box is quicker and more intuitive By the way range names are not case sensitive For example Revenue revenue and REVENUE can be used interchangeably Try it Name the rectangular range containing the numbers Data 71 31 9 69 5 15 74 46 84 27 14 49 25 38 83 4O 43 2O 75 83 28 72 3O 92 75 41 56 9O 89 73 28 81 43 81 61 Use the InsertNameDe ne menu item This shows a list of all range names in your workbook Click on the one you want to delete and then click on the delete button Try it The numerical range is currently named Data Delete this range name and then rename the range Database 15 86 50 41 21 63 18 10 1 9 14 52 1 6 18 65 89 88 28 72 7 15 28 57 92 2 29 90 86 100 83 10 82 100 65 4 Suppose you have the labels Revenue Cost and Pro t in cells A20 A21 and A22 and you would like the cells B20 B21 and B22 which will contain the values of revenue cost and pro t to have these range names Here s how to do it quickly Select the range consisting of the labels and the cells to be named A20B22 Then use the InsertNameCreate menu item make sure the appropriate box in this case Left Colunm is checked and click on OK Excel tries usually successfully to guess where the labels are that you want to use as range names If it guesses incorrectly you can always override its guess Try it Name the ranges A3A8 B3B8 and so on according to the labels in row 2 Month UnitsSoId UnitPrice Revenue Jan 100 125 12500 Feb 150 125 18750 Mar 200 140 28000 Apr 230 140 32200 May 200 150 30000 Jun 300 150 45000 Sometimes you have entered a formula using cell addresses such as B20B21 Later you name B20 as Revenue and B21 as Cost The formula does not change to RevenueCost automatically However you can make it change and hence become more readable Select the cell or range of cells with the formulas Then use the InsertNameApply menu item highlight any relevant range names for the formulas involved and click on OK Try it Apply the names of the cells B2 and B3 to the formulas in row 7 Figtlted cost 50 Variable cost 2 Month Jan Feb Mar Apr Units produced 224 194 228 258 Total cost 498 438 506 566 Click on the down arrow at the right of the name box and click on any of the range names you see That range will then be selected automatically Try it There are ve named ranges below Locate them 73 4 28 92 29 10 31 38 35 60 2 40 49 45 5 22 12 44 39 6 21 88 98 Junk Junk Junk Junk Sometimes it is straightforward to use range names in formulas For example if B20 is named Revenue and B21 is named Cost then entering the formula RevenueCost in say cell B22 is a natural thing to do But consider this situation The range B3B14 contains revenues for each of 12 months and its range name is Revenues Similarly C3C14 contains costs and its range name is Costs For each month you want that month s revenue minus cost in the appropriate cell in column D You will get it correct if you select the range D3 D14 type the formula Revenues Costs and press CtrlEnter If you click on any cell in this range you ll see the formula RevenuesCosts This can be confusing How does Excel know that the formula in D3 for example is really B3 C3 Let s just say that it s smart enough to gure this out If it confuses you however you can always enter B3C3 and copy it down Then you re safe but you ve lost the advantage of range names Try it Enter the formula for all of D3D14 using range names If you like calculate pro ts again in column E in the usual way without range names Revenues Costs Profits 1 600 1 400 2000 1 800 2 1 00 1 800 2900 2 800 500 400 1 700 1 500 2000 1 900 2500 2 300 Useful functions There are many useful functions in Excel You should become familiar with the ones most useful to you for example nancial analysts should learn the nancial functions but here are a few everyone should know By the way we capitalize the names of these functions just for emphasis However they are not case sensitive You can enter SUM or sum for example with the same result Enter the formula SUMrange where range is any range This sums the numerical values in the range Actually it is possible to include more than one range in a SUM formula so long as they are separated by commas This can also be done with the COUNT COUNTA AVERAGE MAX and MIN functions discussed below For example SUMB5C10D12Revenues is allowable where Revenues is a name for some range The result is the sum of the numerical values in all of these ranges combined Note that if any cells in any of these ranges contain a label not a number it is ignored in the sum Try it Use the SUM function in cell B10 to calculate the total of all costs Table of costs for units produced in one month along side for use in another month along top Feb Mar Apr May Jan 5000 5500 4400 3900 Feb 6100 5400 4700 Mar 4300 6900 Apr 4 900 Total cost Enter the formula COUNTrange where range is any range This produces the number of numerical values in the range There is a similar function COUNTA which counts all of the cells numerical or otherwise in the ranges For example if cells Al A2 and A3 contain Month 1 and 2 respectively then COUNTA1A3 yields 2 whereas COUNTAA1A3 yields 3 Try it Use the COUNT and COUNTA functions to ll in cells El and E2 Note that there are students below the visible portion of the spreadsheet Student ID Exam score Number enrolled 3416 62 Number who took exam 6125 73 1535 74 2323 Absent 577 77 9044 57 8403 67 5892 90 4242 77 Enter the formula AVERAGErange where range is any range This produces the average of the numerical values in the range Be aware that the AVERAGE function ignores labels and blank cells in the average So for example if the range C3C50 includes scores for students on a test but cells C6 and C32 are blank because these students haven t yet taken the test then AVERAGEC3C50 averages only the scores for the students who took the test It doesn t automatically average in zeroes for the two who didn t take the test Try it Use the AVERAGE function to calculate the averages in cells B1 and B2 For B2 you ll have to replicate the exam scores in column C and make some changes Average exam score for students who took the exam Average exam score if absent students get zeroes Student ID Exam score 1533 68 8031 74 9859 80 9106 63 3535 72 8192 Absent Enter the formula MAXrange or MINrange where range is any range These produce the obvious results the maximum or minimum value in the range Try it Use the MAX and MIN functions to ll in the range B8C9 For example you want the values 2300 and 3600 in cells B8 and C9 Sales rep Allison Baker Jones Miller Smith Taylor Jan sales 3700 2400 2300 3000 3800 3700 Feb sales 2600 2200 2400 2800 3600 2300 Min sales Max sales Jan Feb Using the paste function 26 button in the top toolbar If you haven t used this button you should give it a try It not only lists all of the functions available in Excel by category but it also leads you through the use of them As an example suppose you know there is an Excel function that does net present value but you re not sure what its name is or how to use it You could proceed as follows Select a blank cell where you want the function to go Press the button and click on the category that seems most appropriate Financial in this case Scan through the list for a likely candidate and select it try NPV At this point you can get help or you can press the Next button and enter the appropriate arguments for the function discount rate and one or more ranges of values Try it Use the function wizard to help you determine the function in cell B6 Use the range names in cells B3 through B5 for improved readability Scroll to the right to see the correct formula Payments for Mr Jones whojust bought a new car Amount financed 15000 Annual interest rate 890 Term number of months financed 36 Monthly payment E Using IF functions IF functions are very useful and they vary from simple to very complex We ll provide a few examples Enter the formula IFconditionexpressionexpression2 where condition is any condition that is either true or false expression is the value of the formula if the condition is true and expression2 is the value of the formula if the condition is false A simple example is IFA1lt510 NA Note that if either of the expressions is a label as opposed to a numerical value it should be enclosed in double quotes Try it Enter appropriate IF formulas in columns C and D Scroll to the right to see the correct answer For each product if the end inventory is less than or equal to 50 units enough units are ordered to bring stock back up to 200 otherwise no units of that product are ordered Product End inventory Order placed yes or no of units ordered 1 100 2 4O 3 2O 4 7O Sometimes IF functions are nested For example there might be three possibilities depending on whether the value in cell Al is negative zero or positive A nested IF formula could then be used as follows Enter the formula IFcondiiion expressionIFcondiiion2expression2expression3 If condition is true the relevant value is expression Otherwise we check condition2 If it is true the relevant value is expression2 Otherwise the relevant value is expression3 An example is IFA1lt010IFA102030 Suppose this formula is entered in cell B2 Then if Al contains a negative number B2 contains 10 Otherwise if Al contains 0 B2 contains 20 Otherwise meaning that Al must contain a positive value B2 contains 30 Try it Use a nested IF function to ll in the grades in colunm C Scroll to the right to see the correct answer Each student gets an A if score is 90 or above 8 for satisfactory if score if 60 or above but less than 90 or U for unsatisfactory if score is below 60 Student Score Grade 1 70 95 55 80 60 90 CDU ILWN Sometimes more complex conditions AND or OR conditions are useful in IF functions These are not difficult once you know the syntax Enter the formula IFANDeondition eondition2expressionexpression2 This results in expression if both condition and condition2 are true Otherwise it results in expression2 Note the syntax The keyword AND is followed by the conditions separated by a comma and enclosed within parentheses Of course more than two conditions could be included in the AND Try it Use an IF function with an AND condition to ll in the bordered range Scroll to the right to see the correct answer note the double quotes for labels Investor sells stock only if its price has gone up three consecutive days including the current day Day Price change Sell yes or no Up Down CDU ILWNA C U Enter the formula IFORconditi0nIcondition2expressi0nexpressi0n2 This results in expression if either condition or condition2 is true or if they re both true Otherwise it results in expression2 Again more than two conditions could be included in the OR Try it Use an IF function with an OR condition to ll in the bonuses in column F Scroll to the right to see the correct answer Any student who scores at least 95 on any of the exams gets a bonus which is 1 oftheirtotal score Student Exam1 Egtltam2 Egtltam3 Egtltam4 Bonus 1 87 83 83 8O 2 77 72 74 97 3 8O 95 79 75 4 82 87 96 88 5 78 94 81 79 6 75 83 8O 72 Using lookup functions Lookup tables are useful when you want to compare a particular value to a set of values and depending on where your value falls assign a given answer For example you might have a taX table that shows for any gross adjusted income what the corresponding taX is There are two versions of lookup tables vertical VLOOKUP and horizontal HLOOKUP Since they are virtually identical except that vertical goes down whereas horizontal goes across we 11 only discuss the VLOOKUP function The VLOOKUP function takes three arguments 1 the value to be compared 2 a table of lookup values with the values to be compared against always in the leftmost column and 3 the column number of the lookup table where you nd the answer Since the VLOOKUP function is often copied down a column it is usually necessary to make the second argument an absolute reference and this is accomplished most easily by giving the lookup table a range name such as LookupTable Range names are always treated as absolute references The only requirement of a lookup table is that the values in the rst column the comparison column must be sorted in ascending order Let s say you want to assign letter grades to students based on a straight scale below 60 an F at least 60 but below 70 a D at least 70 but below 80 a C at least 80 but below 90 a B and 90 or above an A The spreadsheet sample below shows how you would set this up The comparison column in the lookup table starts at 0 the lowest grade possible then records the cutoff scores 60 through 90 The lookup table in the range E2F6 is rangenamed LookupTable The typical formula in cell C2 which is copied down column C is VLOOKUPB2LookupTable2 This compares the value in B2 67 to the values in column E and chooses the largest value less than or equal to it This is 60 Then since the last argument in the VLOOKUP function is 2 the score reported in C2 comes from the second column of the lookup table neXt to 60 namely D Student Score Grade Lookup table 1 67 D 0 F 2 72 C 60 D 3 77 C 70 C 4 70 C 80 B 5 66 D 90 A 6 81 B 7 93 A 8 59 F 9 90 A Create a lookup table with at least two columns where the values in the rst column are in ascending order and for best results give the table range a range name Then enter the formula VLOOKUPvaluelookup tablecolumn as described above Try it Enter a lookup table in columns E and F and VLOOKUP functions in column C Assume there is a quantity discount policy in force for orders less than 300 units the unit price is 3 for orders of at least 300 units but less than 400 the unit price is 250 for orders of 400 units or more the unit price is 2 Scroll to the right to see the correct answer Order Units sold Total cost 1 373 475 459 441 238 349 344 203 ODNCDU ILWN Using data tables Data tables also called whatif tables allow you to see very quickly how one or more outputs change as one or two key inputs change There are two types of data tables oneway tables and twoway tables A oneway table has one input and any number of outputs A twoway table has two inputs but only one output We ll demonstrate both types To illustrate suppose Mr Jones buys a new car for 20000 makes a 5000 down payment and nances the remaining amount over the neXt 36 months at an 85 annual interest rate There are at least two outputs that might be of interest the monthly payment and the total interest paid These are affected by at least two inputs the amount of the down payment and the annual interest rate Let s rst look at a simple oneway data table where we see how a single output monthly payment varies as the annual interest rate varies This is shown in the following spreadsheet Price of car 20000 Data table Down payment 5000 Interest rate Monthly payment Amount financed 15000 47351 Annual interest rate 850 800 47005 Number of payments 36 825 47178 850 47351 Monthly payment 47351 875 47525 Total interest paid 204647 900 47700 Enter the formula for the output in cell E3 Since the monthly payment was calculated with the PMT function in cell B7 simply enter B7 in cell E3 Starting in cell D4 enter any sequence of interest rates Select the entire table that is the range D3E8 Finally use the DataTable menu item and enter B4 as the column input cell There is no row input cell so leave it blank What happens when you do this Excel takes each interest rate in column D substitutes it into the column input cell you designated cell B4 recalculates the formula in cell E3 the one we colored gray for emphasis with this new interest rate and records the answer in the data table We use a column input cell because the possible inputs interest rates are listed in a column We can also capture more than one output in a oneway data table An example appears below where the single input is still the interest rate but there are two outputs monthly payment and total interest paid This table is formed exactly as before except that the table range is now D3F8 Price of car 20 000 Data table Down payment 5000 Interest Monthly payment Total interest Amount financed 1 5 000 47351 2046 47 Annual interest rate 850 800 47005 192164 Number of payments 36 825 47178 198398 850 47351 2046 47 Monthly payment 47351 875 47525 210909 Total interest paid 204647 900 47700 217186 Try it Create a oneway data table that shows the monthly payments and the total interest paid for each term value in cell BS from 12 to 48 in increments of 12 Scroll to the right for the correct answer Price of car 20 000 Down payment 5 000 Amount financed 1 5 000 Annual interest rate 850 Number of payments 36 Monthly payment 47351 Total interest paid 2046 47 Twoway tables allow you to vary two inputs one along a row and one along a column and capture a single output in the body of the table The following spreadsheet illustrates this where we vary the annual interest rate and the amount of the down payment The single output is the monthly payment Price of car 20000 Data table of monthly payment Down payment 5 000 47351 4000 5 000 6 000 Amount financed 1 5 000 800 50138 47005 43871 Annual interest rate 850 825 50323 47178 44033 Number of payments 36 850 50508 47351 44195 875 50694 47525 44357 Monthly payment 47351 900 50880 47700 44520 Total interest paid 204647 Enter the formula B7 for the single output in the upper left corner cell D2 of the data table Again we colored this cell gray for emphasis Enter any sequence of down payments to the right of this and any sequence of interest rates below this Finally select the entire data table range D2G7 use the DataTable menu item enter B2 as the row input cell and enter B4 as the column input cell Note that B2 is the row input cell because various down payments are entered in a row E2G2 Similarly B4 is the column input cell because interest rates are entered in a column Excel substitutes each down payment into cell B2 each interest rate into cell B4 calculates the formula in cell D2 and records the answer monthly payment in the body of the table Try it Create a twoway data table that shows the total interest paid for each term from 12 to 48 in increments of 12 and each down payment from 3000 to 5000 in increments of 1000 Put down payments along the side terms along the top Scroll to the right for the correct answer Price of car 20000 Down payment 5000 Amount financed 15000 Annual interest rate 850 Number of payments 36 Monthly payment 47351 Total interest paid 2 04647 Recording simple macros into Personalxls Macros are sets of instructions programs that automate various tasks in Excel Microsoft has developed a programming language Visual Basic for Applications or VBA that can be used to develop macros in Excel and other Microsoft Office applications It takes some time to learn this language but you don t need to learn it to record simple macros and then use them This can be very handy for automating tasks that you do frequently In fact once you ve recorded a useful macro you can even assign it to a button that you can place in a toolbar Then every time you want to perform the task run the macro all you have to do is click on the button This section will illustrate the procedure Here s a neat example Suppose there are many times you d like to place a blue border around a range Wouldn t it be nice to simply select the range or ranges click on a button and have the blue borders appear The procedure which only ever has to be done once on a given PC is as follows 1 Select any range 2 Turn on the macro recorder with the ToolsRecord MacroRecord New Macro menu item 3 Give the macro a suggestive name such as BlueBorder in the Macro Name box and then click on the Options button to obtain a dialog box 4 Under the Store In section click on Personal Macro Workbook and then click on OK This stores your macro in a le called Personalxls which is stored in your XLStart directory so that it always opens when you open Excel It is usually stored as a hidden le so that you don t actually see it But it s there and all of the macros in it are available to you at any time Note You probably will not be allowed to do this in your school s lab But it should work ne on your own PC 5 The recorder is now running so anything you do is being recordediso be careful Go through the procedure of putting a blue border around the range you selected To do this use the FormatCells menu item click on the Borders tab and make the obvious selections to get a blue outline border 6 Once you ve got the blue border click on the black square stop button that s oating somewhere on the screen This turns off the recorder 7 To check whether the macro is working correctly select any range and run the macro To run it use the ToolsMacro menu item click on the macro name you just recorded and click on the Run button You should get a blue border around the selection Actually try running it with more than one range selected You should get a blue border around each selected range This makes the macro even more useful Try it Record a macro called BlueBorder and then test it out Note In step 4 above choose the This Workbook option instead of the Personal Macro Workbook option It will work better for this demo Try it again A common task is to copy a range of formulas onto itself but to replace the formulas with values Remember the Paste SpecialValues option Record a macro called Special that does this using the formulas in column C when you do the recording Then try out your macro with the formulas in column D Again choose the This Workbook option instead of the Personal Macro Workbook option for this demo Value 1 Value 2 Sum Product 7 10 17 70 4 3 7 12 6 3 9 18 2 3 5 6 3 9 12 27 4 5 9 20 8 2 10 16 6 1 7 6 Customizing toolbars Excel provides several toolbars Two of these the Standard and Formatting toolbars appear by default when you open Excel Each of the buttons on these toolbars has a macro assigned to it so that when you click on the button some useful task is carried out You might not know however that there are several other toolbars you can make visible at any time In addition there are a number of preprogrammed buttons that you can add to any of the toolbars and you can delete any of the standard buttons you never use You can even form your own toolbars and customized buttons that run your own macros and it s all quite easy This section will explain how Put the cursor over any toolbar and right click You ll see a list of toolbars Click on any to place checks next to those you want to show or remove checks next to those you want to hide Put the cursor over any toolbar and right click Choose the Customize option at the bottom and then click on the Commands tab in the resulting dialog box You ll see a list of categories and buttons Click on any category and then click on any button You ll see a short description of what this button does at the bottom of the dialog box If you d like to have this button on a toolbar simply drag it to the toolbar Similarly if there is any button on a toolbar that you never use simply drag it off the toolbar to delete it Let s say you ve recorded the macro BlueBorder as above and you now want to attach it to a button that you ll place on a toolbar Put the cursor over any toolbar right click choose the Customize option and click on the Commands tab Scroll down the list of categories until you get to the Macros category and click on it You ll see a generic quothappy facequot button that doesn t have any preprogrammed macro attached to it This button for your use Click on it and drag it up to a toolbar Now right click on your new button with the Customize dialog box still open to see a menu Click on Assign Macro and then select BlueBorder from the list of macros shown This assigns your macro to the button At this point you probably want a more suggestive picture icon on your new button You have two options The rst is to right click on the button with the Customize dialog box still open and select the Edit Button Image menu item This takes you to a paint program where you can paint the image you want such as a blue rectangular border Alternatively you can borrow an image and then modify it To do this right click on a button whose image you want to borrow and select the Copy Button Image menu item Then right click on your new button and select the Paste Button Image menu item Finally right click again on your new button select the Edit Button Image menu item and modify the image as you like Note If you d like to mimic the image of a button that shows in the Customize dialog box but isn t yet on a toolbar you ll need to drag this to a toolbar rst You can always drag it off after you ve copied its image Finally right click on the button and type Blue Outline Border next to the Name menu item This creates a quottool tipquot for your button From then on when you put your cursor over the button you ll see this tool tip Put the cursor over any toolbar right click choose Customize click on the Toolbars tab and click on the New button This allows you to create a new toolbar with a name you can choose From that point your new toolbar is just like any of the ones that came with Excel You can show it or hide it and you can add buttons to it In fact it starts out empty so you will have to add buttons to it to make it useful If you mess up or if you don t need your new toolbar any longer just go to Customize and the Toolbars tab select your toolbar and click on Delete Setting the default number ofsheets in a new workbook Excel s default number of worksheets in a workbook is 3 Who knows why Usually you need only 1 and the extra blank sheets just take up wasted memory on your hard drive when you save the le Use ToolsOptions click on the General tab and change the Sheets in New Workbook setting to a smaller number probably 1

### BOOM! Enjoy Your Free Notes!

We've added these Notes to your profile, click here to view them now.

### You're already Subscribed!

Looks like you've already subscribed to StudySoup, you won't need to purchase another subscription to get this material. To access this material simply click 'View Full Document'

## Why people love StudySoup

#### "I was shooting for a perfect 4.0 GPA this semester. Having StudySoup as a study aid was critical to helping me achieve my goal...and I nailed it!"

#### "I made $350 in just two days after posting my first study guide."

#### "Knowing I can count on the Elite Notetaker in my class allows me to focus on what the professor is saying instead of just scribbling notes the whole time and falling behind."

#### "Their 'Elite Notetakers' are making over $1,200/month in sales by creating high quality content that helps their classmates in a time of need."

### Refund Policy

#### STUDYSOUP CANCELLATION POLICY

All subscriptions to StudySoup are paid in full at the time of subscribing. To change your credit card information or to cancel your subscription, go to "Edit Settings". All credit card information will be available there. If you should decide to cancel your subscription, it will continue to be valid until the next payment period, as all payments for the current period were made in advance. For special circumstances, please email support@studysoup.com

#### STUDYSOUP REFUND POLICY

StudySoup has more than 1 million course-specific study resources to help students study smarter. If you’re having trouble finding what you’re looking for, our customer support team can help you find what you need! Feel free to contact them here: support@studysoup.com

Recurring Subscriptions: If you have canceled your recurring subscription on the day of renewal and have not downloaded any documents, you may request a refund by submitting an email to support@studysoup.com

Satisfaction Guarantee: If you’re not satisfied with your subscription, you can contact us for further help. Contact must be made within 3 business days of your subscription purchase and your refund request will be subject for review.

Please Note: Refunds can never be provided more than 30 days after the initial purchase date regardless of your activity on the site.