Excel
 Assignment 7 - Performing Basic Calculations

With Microsoft Excel, you can easily perform common and complex calculations. In addition to adding, subtracting, multiplying, and dividing, you can calculate the total and compute the average of a set of values. With basic calculations, you can figure profit values from revenue and expenses and you can compute an employee’s wages from hours worked and pay rates. These are just a few examples of the calculations Excel can perform.
Download the following documents and save them to your pc  Lodging Usage, Activity Rentals and Food07
Once they are saved go to the folder containing the documents  and open Lodging Usage with Excel
Enter formulas in a worksheet
In this exercise, you open a worksheet and create basic formulas using different methods to enter the formulas.
Click cell C9, and type =C5+C6+C7. As you type each cell address (or reference), the cell being referred to is selected and the selection border appears in a specific color. This color matches the color of the text used for the cell reference.
Press Enter. The total of the cells (908) appears in cell C9.
Click cell C13, type =, and click cell C5. C5 is added to the formula. The cell you click is color-coded and shows a flashing marquee border
Type /, click cell A5, and click the Enter button on the Formula bar. Excel completes the formula and displays the ratio of cells C5 and A5 (30.25).
Scroll down, if necessary, and click cell C21. Click in the Formula bar, type =C13/31, and click the Enter button on the Formula bar. Excel calculates the occupancy rate of chalet rooms for the month of January (0.97581), and the results appear in cell C21.
On the Format menu, click Cells.
Switch to the Number tab, if necessary, and click Percentage in the Category list.
Click OK to accept the default number of decimal places (2). The value in C21 appears as a percentage.
You may find that a similar formula is needed in several adjacent cells. For example, if you have a list of items and each item contributes to the total income for one year, you might want to add the items to total the income for that year. To total the income for consecutive years, you can create formulas to sum the income for each year.
Instead of entering the formula repeatedly for each year, you can simply copy it and paste it in the destination cells. You also can use the Fill feature to copy formulas to adjacent cells. 
When selecting a cell, you may have noticed the small black square in the bottom right corner, this is the Fill handle. When you click a cell containing a formula and drag the Fill handle, the formula is copied to the cells.
When the formula contains cell references, Excel changes them to match those of the column or row to which the formula has been copied unless the formula was enter telling Excel not to change the cell reference.
Copy formulas
In this exercise, you copy formulas in your worksheet.
Click cell C13, and on the Edit menu, click Copy. A flashing marquee border appears around the cell.
Click cell C14, and on the Edit menu, click Paste. The formula is pasted in cell C14, and the Paste Options button appears next to the cell.
Repeat step 2 for cells C15 and C16.
Click cell C9, and point to the Fill handle. The mouse pointer turns into a crosshair pointer when properly positioned.
Drag the Fill handle to cell D9. The formula in cell C9 is copied to cell D9, and the Auto Fill Options button appears next to the cell.
Click cell D9, and observe its formula in the Formula bar. The formula is copied from C9, and the column letter is adjusted to match the column of the new cell.
Point to the Fill handle in cell D9, and drag the handle to cell N9. The formula is copied to cells E9:N9. The total number of nights occupied for each month is displayed in cells E9:N9.
Save the file, and leave it open for the next exercise
As you have learned, you can copy (and move) cells that contain formulas with cell references, and the references automatically adjust to reflect their new location. These are referred to as relative references.
In some cases, however, you need cell references that don’t change when you copy them from one location to another. For example, a formula might refer to a rate of interest that is always stored in a particular cell. This is referred to as an absolute reference. A cell reference also may have an absolute reference to a row but not to a column, and vice versa. To make a cell reference absolute, type a dollar sign ($) before either or both of the column or row references.
Use cell references in formulas
In this exercise, you use absolute references and relative references to build and copy a formula.
Double-click cell C13.
Type a dollar sign ($) before the A in the reference to cell A5, and press Enter. The formula reads =C5/$A5.
Copy the formula in cell C13 to cell D13, and click cell D13, if necessary, to select it. The formula in cell D13 reads =D5/$A5. Because the column reference to cell A5 is absolute, that reference does not change when the formula is copied. The reference to the first cell in the formula is entirely relative.
Copy the formula in cell C13 to cell C14, and click cell C14. The formula in cell C14 reads =C6/$A6. Because the reference to column A is absolute, that reference does not change when the formula is copied. However, the row reference is still relative, so that value is updated when the formula is copied. The reference to the first cell is entirely relative.
Using the Fill handle, copy the formula in cell C14 to cells C15 and C16. Click cell D13 and use its Fill handle to copy the formula to E13:N13. Use the Fill handles on C14, C15, and C16 to copy their formulas to D14:N14, D15:N15, and D16:N16, respectively.
Save and close Lodging Usage
Open Food07.
Edit formulas
In this exercise, you revise formulas.
Click cell P3, and click to the right of the formula in the Formula bar. The insertion point flashes at the end of the formula.
Press the Backspace key three times, click cell O3, and then press Enter. The Total Yearly Consumption figure, which also is shown in cell O3, is deleted from the formula, and the cell reference for that data replaces it. Using a cell reference in the formula instead of a constant allows you to copy the formula.
Copy the formula to cells P4:P35 by using the Fill handle in cell P3. Excel calculates and displays the yearly costs per item.
Save and Close Food07
Functions
One of the most common calculations performed in a worksheet is adding a range of cells. You can add a range of cells by creating a formula that includes each cell label separated by the addition (+) operator. An easier way to achieve the same result is to use the SUM function.
A function is a predefined formula that performs a calculation. For example, the SUM function adds values or a range of cells. A typical SUM function totaling cells C13 through C16 looks like this: =SUM(C13:C16).
A function consists of two components: the function name and, in most cases, an argument list. The argument list, which is enclosed in parentheses, contains the data (or operands) that the function requires to produce the result. Depending on the function, an argument can be a constant value, a single-cell reference, a range of cells, a range name, or even another function. When a function contains multiple arguments, the arguments are separated by commas. In this lesson, you explore some of the more commonly used functions, including the SUM, AVERAGE, and various date functions.
Open Activity Rentals.
Inserting functions
In this exercise, you use the Insert Function feature to enter functions in a worksheet.
Click cell O3, and click the Insert Function button on the Standard toolbar. The Insert Function dialog box opens
In the Or Select A Category box, click the down arrow, and select Most Recently Used, if necessary.

In the Select A Function list, click SUM, and then click OK. The Function Arguments dialog box appears, showing the SUM function totaling cells C3:N3.
Click OK in the Function Arguments dialog box. The Function Arguments dialog box closes, and the result of the calculation (57) appears in cell O3.
Click cell A20, and then click the Insert Function button on the Standard toolbar. The Insert Function dialog box appears.
In the Or Select A Category box, click the down arrow, and select Statistical.
In the Select A Function list, click COUNT, and then click OK. The Function Arguments dialog box appears.
In the Value1 text box, type A3:A17, and click OK. Excel counts 15 numeric entries in cells 3 through 17 of column A.
Save Activity Rentals, and then close the workbook.
Open Lodging Usage.
Using  AutoSum
In this exercise, you use AutoSum to total a range of cells.
Click cell C17, and click the AutoSum button on the Standard toolbar. A SUM formula appears in cell C17 and the Formula bar, and the range C13:C16 is surrounded by a flashing marquee border.
Press Enter. The formula is entered in cell C17, and the result (54.15) is displayed.
Click cell O5, and then click the AutoSum button. It looks a bit like this Σ A SUM formula is displayed in cell O5 and in the Formula bar, and the range C5:N5 is surrounded by a flashing marquee border.
Press Enter. The formula is entered in cell O5, and the result (2908) is displayed.
This is one of the most common formulas you will need.
Using Dates
Excel’s Date and Time functions allow you to use dates and times in formulas. 
NOW and TODAY are two of the most frequently used date functions. NOW returns the date and time that the function was entered in a worksheet. TODAY returns only the date. Each time you open a workbook that uses one of these functions, the date or time is updated automatically.
Entering Date functions
In this exercise, you use Date functions to calculate the amount of time between two dates.
Click cell A20, and click the Insert Function button.
Select Date & Time from the Or Select A Category list.
Select DATE from the Select A Function list, and click OK. The Function Arguments dialog box opens.
In the Year box, type 2004.
In the Month box, type 2.
In the Day box, type 29.
The serial number for the date 02/29/2004 appears as the formula result at the bottom of the Function Arguments dialog box.
With the Function Arguments dialog box still open, click to the right of the function in the Formula bar, press the spacebar, and type a minus sign (-).
On the Functions list to the left of the Formula bar, click Date. The Function Arguments dialog box opens.
In the Year, Month, and Day boxes, type 2003, 11, and 1, respectively, and click OK. The total number of days (120) between 11/1/2003 and 2/29/2004 appears in the cell. The formula reads =DATE(2004,2,29) - DATE(2003,11,1).
In the Formula bar, click the first Date function. A ScreenTip appears with a description of each item in that portion of the formula.
Click the word year in the ScreenTip to select 2004, and type 2005.
In the Formula bar, click to the right of the formula.
Press the Backspace key to remove the second date function of DATE(2003,11,1).
Type TODAY(), and press Enter. The total number of days between 2/29/2005 and the current date appears in the cell.
Using Basic Statistical Functions
Statistical functions are typically used to compile and classify data so as to present significant information. For example, a teacher wants to determine the highest score (MAX) and the lowest score (MIN) on an exam, a sales manager wants to set pay increases based on sales reps’ average sales over a 12-month period (AVERAGE), and a market researcher wants to figure out the middle income for a group of survey participants (MEDIAN).
Open Activity Rentals.
Insert statistical functions
In this exercise, you create formulas using the AVERAGE, MIN, and MAX functions.
Click cell A21, and click the Insert Function button. The Insert Function dialog box appears.
Select Statistical in the Or Select A Category list, click AVERAGE in the Select A Function list, and click OK. The Function Arguments dialog box appears.
In the Number 1 box, click the Collapse Dialog button. The Function Arguments dialog box collapses, allowing you to select the range of cells to be averaged.
Select cells C15:N15, and click the Expand Dialog button in the Function Arguments dialog box. The Function Arguments dialog box appears with the completed AVERAGE function.
In the Function Arguments dialog box, click OK. The average number of pairs of binoculars rented per month (15.5) appears in cell A21.
Click cell A22, and click the Insert Function button. The Insert Function dialog box appears.
Click the Functions down arrow to the left of the Formula bar, and click MAX.  The Function Arguments dialog box appears.
In the Number 1 box, click the Collapse Dialog button. The Function Arguments dialog box collapses, allowing you to select the range of cells to be used.
Select cells C15:N15, and click the Expand Dialog button in the Function Arguments dialog box. The Function Arguments dialog box appears with the completed MAX function.
In the Function Arguments dialog box, click OK. The highest number of pairs of binoculars rented in a month (25) appears in cell A22.
Click cell A23, type =MIN(C15:N15), and press Enter. The lowest number of pairs of binoculars rented in a month (9) appears in cell A23.
Save and close Activity Rentals.
So far you’ve learned how to enter formulas and functions that calculate data on a single worksheet. You also can enter formulas to calculate data from multiple worksheets within a workbook. A formula that contains a reference (referred to as a 3-D reference) to data or cells on one or more other worksheets is called a three-dimensional formula. When data is adjusted on a worksheet, any formula that references that data also is adjusted.
Three-dimensional formulas are widely used to create a summary sheet that totals figures from different sheets in a workbook file. For example, the chef at Adventure Works wants to see how much the restaurant has paid for food in a year. To calculate the total amount spent, he uses a three-dimensional formula on the Summary worksheet. The formula refers to the cells in each worksheet that contain the amount spent in a particular food category and adds these amounts together.
Open Food07.
In this exercise, you create a formula in one worksheet that adds together data in other worksheets.
Click the Summary sheet tab, click cell A3, type Total Cost of Food, and press Enter. Excel adds the text to cell A3 and moves the insertion point to cell A4.
In cell A4, type =SUM(
Click the Meats sheet tab, click cell P36, and type a plus sign (+). In the Formula bar on the Meats worksheet, the first argument and addition operator are added to the formula
Click the Dry Goods sheet tab, click cell P26, and type a plus sign (+). The second argument is added to the formula.
Click the Produce sheet tab, click cell P28, and press Enter.
The last argument is added to the formula, and the result of the
formula appears in cell A4 on the Summary worksheet.
Save and close Food07

Email Lodging Usage,  Activity Rentals, and Food07
 

Back to the main page