CoachEel3085
Lab Exercise 6 Excel Fundamentals, Part 2 Formulas and…
Lab Exercise 6 Excel Fundamentals, Part 2
Formulas and Functions:
If Excel were just for storing data, it wouldn’t be terribly useful. The power of Excel comes from relating results in one or more cells to another. The two basic techniques are formulas (where the user specifies that one cell contains the result of some computation) and functions (which are special formulas already built into the Excel software). To specify that a cell holds a formula instead of text or a number, its content should begin with an equals symbol (“=”). Then, the formula follows the equals symbol.
Action 1:
Open a new Excel file and we’ll make a few simple computations using formulas and functions:
In cells A1, A2, and A3, enter the text “Value 1”, “Value 2”, and “Value 3”.
In cells B1, B2, and B3, enter the values 1, 10, and 100.
In cell A5, enter the text “Sum Formula” and in B5 enter “=B1+B2+B3”. This will cause B5 to hold the sum of the values in cells B1, B2, and B3.
In cell A6, enter the text “Sum Function 1” and in B6 enter “=SUM(B1,B2,B3)”. Just like the formula above, B6 will hold the sum of values in cells B1, B2, and B3.
In cell A7, enter the text “Sum Function 2” and in B7 enter “=SUM(B1:B3)”. Here, A7 will hold the sum of all values in the range B1:B3.
Here, the generic formula for adding the three numbers works the same as the functions. However, be careful when inserting or deleting rows or columns, as formula ranges will often automatically adjust the range endpoints.
Action 2:
For example, click on the 2 in the very left column, then press Alt-I (for insert), then R (for row). This inserts a new, empty row between the 1 and 10. Enter the value 5 in the new cell B2 and see the results change. You can press Ctrl-~ (the ~ character is a tilde and you can usually find it just above the TAB key) to display the formulas in all the cells. How have they changed from what you originally entered?
Excel tends to be forgiving of certain irregularities, especially for simple functions. The cell references and function names are not case-sensitive (in B7, you could just as well write “=sum(b1:b3)”). Sometimes, you can even omit a closing parenthesis in a function (again, in B7, you could write “=sum(b1:b3” and immediately press Enter – Excel will close out the parentheses for you), although this is best avoided until you have achieved a degree of proficiency.
Copying, Cutting, & Pasting with Formulas:
Excel makes a basic assumption about moving data or formulas around a worksheet:
Copying means you want to transfer existing relationships to a new set of cells. For example, suppose you have the formula “=SUM(C2:C6)” in cell C8. If you copy and paste C8 to D8, Excel will maintain the formula structure (adding the series of cells from six cells above to two cells above) and transfer that structure to D8. So, D8 will hold “=SUM(D2:D6)”.
Cutting means you just want to relocate the data and relationships to a new place on the worksheet. So, Excel keeps them as is. For example, if C8 again has the formula “=SUM(C2:C6)” and you cut and paste it to D8, D8 will hold the formula unchanged as “=SUM(C2:C6)”.
Autofill can also be used with formulas and this is probably where you will most often apply it. For autofill with formulas, the cell references are the pattern – the formula is copied to new cells with updated cell references. Suppose you have a list of data arranged in rows, like the list below of inventory items, quantity in stock, and the cost per item.
Action 3:
On a new worksheet, copy the data from the table below into the range A1:D7 (you can copy from the Word document and paste into the Excel document). Then, in D2, enter the formula for the total cost of item 1 as the “In Stock” cell (B2) times the “Unit Cost” cell (C2).
Item
In Stock
Unit Cost
Total Cost
1
48
$1.50
2
32
$2.94
3
192
$0.84
4
252
$0.23
5
40
$5.16
6
64
$6.44
The total cost for each item should only consider the units in stock and unit cost for that item. This is the default mode for autofill – use autofill to copy the total cost formula for the first four items, then press Ctrl-~ to see how the formulas have changed. To prove that this is simply copying, use Ctrl-C to copy the total cost formula from item 5 to item 6 and then look at the formula for item 6’s total cost. If, however, you use Ctrl-X to cut the total cost formula from item 5 to item 6, you’ll see that the cell references in the formula don’t change. Try it if you like but then undo the action with Ctrl-Z – we’ll need this data later in the exercise.
Remember, copying a formula is typically done to apply a formula over a wider range of data while maintaining the same relationship for each row or column. So, the cell references should change when the formula is copied. Cutting a formula is typically done to move the results (or formula inputs) to a different part of the worksheet without affecting the original relationships. For example, if you cut the “In Stock” column and paste it several rows lower, the total cost results will be unchanged. Similarly, cutting the “Total Cost” column and pasting it in a new location also does not affect the total cost results. However, the cells referenced in the total cost formula will change, in order to keep the results from changing.
More Functions:
Many different functions are available in Excel. Go to the Formulas tab to see the main groupings, like financial, logical, and text. Knowing what functions are available and how to use them is another important Excel skill but it takes time and practice to develop. For now, we’ll look at a few simple function examples.
Action 4:
MAX: The MAX function gives the highest numeric value from a set of cells. In C9, enter the text “Max.” and in D9 enter the formula “=MAX(D2:D7)”. This will give the highest value from the set of total cost entries. Notice that when you have D9 selected, any cells referenced in its function are highlighted.
Action 5:
AVERAGE: The AVERAGE function gives a simple average from a set of cells. In C10, enter the text “Avg.” and in D10 enter the formula “=AVERAGE(D2:D7)”. This gives the average value from the total cost entries. If you don’t like the number of decimal places, you can adjust it by using the increase/decrease decimal tools on the Home tab’s toolbar (circled below).
Action 6:
CONCAT: The CONCAT (for concatenate) function combines text or numbers from multiple cells into one cell. Suppose you wanted to combine the cells from a row, with contents spaced by forward slashes. In E2, enter the formula “=CONCAT(A2,”/”,B2,”/”,C2,”/”,D2)”.
Relative and Absolute Addressing:
Now, we’ll introduce relative and absolute addressing. The difference between the two is in what happens when a cell reference is copied to a new cell. In relative addressing, which is how we have performed copying so far, both the row and column may change to maintain relationships.
Action 7:
Type the formula “=K2” into cell K3. Then, copy K3 to M3; you can verify that the formula has changed to “=M2”. In effect, the formula tells the current cell to hold the value from the cell above it. The cell reference in the formula is relative to its current cell.
Sometimes, it’s useful to keep cell references the same even when the formula is copied to a new cell. For example, suppose a sales associate earns a 5% commission on all jewelry he sells. A spreadsheet might hold all the items sold during the month by that associate. It would certainly be possible for a column to hold the commission earned for each item (that is, the sale price multiplied by the commission rate). But, it’s good practice to put the commission rate in one cell and have all the formulas reference it absolutely. Then, a change in the commission rate can be implemented simply by changing one cell instead of many.
To use absolute referencing, you simply place a dollar sign in front of the row (if you don’t want the row to change) or the column (if you don’t want the column to change) or both (if you don’t want either to change). A quick way to cycle through the four possibilities is by pressing the F4 key.
Action 8:
Using the previous example, let’s place our commission rate (5%) in cell F2. Then, copy the table below into the range cells F5:H11.
Item
Sale Price
Commission
DHT-014
$4,500
DHT-013
$2,000
FHT-003
$1,500
FHT-003
$1,250
EHX-001
$3,000
EHX-002
$2,500
The commission for the first item (DHT-014) can be found by entering the formula “=$F$2*G6” in cell H6. You can use autofill to copy this formula down to H11. Since the $F$2 reference did not change during copying, every cell has the correct commission rate. Since the G5 reference did change during copying, every calculated commission used the correct sale price. As a side note, the formula “=F$2*$G6” could also have been used in cell H6 and copied over H7:H11 without changing the commission results. Why?
VLOOKUP:
The VLOOKUP function allows finding a matching entry within a table. Suppose you’re selling a product and offering volume discounts on large purchases. The table below indicates the unit cost by quantity.
Quantity
Unit Price
1
$100
5
$95
10
$90
25
$85
50
$80
Action 9:
Copy the table into K6:L11 on your worksheet. In K13, enter a number between 1 and 100 (your choice). This will be the quantity ordered and we can use VLOOKUP to find the total order price based on the discount. In L13, we’ll put the quantity-dependent unit price: enter the formula “=VLOOKUP(K13,K7:L11,2)”. We’ll put the total order price in M13: enter the formula “=K13*L13”. Change the value in K13 to get other unit prices and verify that they change appropriately.
How does VLOOKUP work?
The first function parameter (K13) tells VLOOKUP what value to look up – this is known as the “lookup value”
The second function parameter (the range K7:L11) tells VLOOKUP the range of the lookup table, with the values in the left column compared against K13
The third function parameter (2), tells VLOOKUP which column of the table holds the value that the function returns – here, the second column with the unit price
For VLOOKUP, the lookup value doesn’t need to exactly match any value in the left column of the specified table. Instead, VLOOKUP returns the result from the row with the highest value that is not greater than the lookup value. Because the comparison process stops once VLOOKUP finds a value, the comparison values in the left column should be arranged in ascending (increasing) order.
