# data[].attributes.headline
Summing a Data Range Based on Conditions

# data[].attributes.subject
Summing a Data Range Based on Conditions

# data[].attributes.body
We can use functions to selectively COUNT, SUM, and AVERAGE

Task 1 - Use Formulas to Determine Number of Tours Sold
The COUNTIF function can be used to count the number of times a particular value appears in a range.

1. Click NY sheet tab, then Click cell G7

# data[].attributes.body
Formulas can be used to analyze data to make it more meaningful in your spreadsheets.

Workbook: EX E-1.xlsx

# data[].attributes.body
The format of a cell determines how the values and labels look.

Task 1 - Change the Format of values to Accounting

1. Select range D4:D32

2. Click the Format menu

3. In the Number sub-menu, Choose Accounting

Task 2 - Change the Format of values to Financial

1. Select range F4:H32

2. Click the Format menu

3. In the Number sub-menu, Choose Financial

Task 3 - Change the Format of values to Percentages

1. Select range J4:J32

2. Click the Format menu

3. In the Number sub-menu, Choose Percentage

4. Use the Decrease Decimal and Increase Decimal buttons to change the number of decimals that are shown.

Task 4 - Use Format menu to Format Other Values
Use the formatting tools to change the date and number formats in the other columns of the sheet.

# data[].attributes.body
You can use formatting features to make a worksheet more attractive and easier to read, and to emphasize key data. You can also apply conditional formatting so that cells meeting certain conditions are formatted differently from other cells.

Worksheet: EX C-1.xlsx

# data[].attributes.headline
Understanding Relative and Absolute Cell References

# data[].attributes.subject
Understanding Relative and Absolute Cell References

# data[].attributes.body
Relative cell references are the way spreadsheet programs typically indicate a cell when entering formulas and using formulas. When we copy a cell formula to another cell our spreadsheet program updates the relative reference.

Absolute cell references allow us to make sure a specific column, row, or cell is referenced. We do this by putting a $ in-front of the cell reference for either the row or column or row and column.

# data[].attributes.body
Functions are predefined worksheet formulas that enable you to perform complex calculations easily. They can be entered into a cell either using the Insert Function feature or my typing it in the cell.

Data
The data we need to complete this step is the expenses for each country

Pseudo Formula: Average = ( a₁ + a₂ + ... + an ) / N

Task 1 - Use the Average Function

1. Click cell B15

2. Click the Insert Menu then Function

3. Click AVERAGE in the Select a function list, then Click OK

4. Select the range B4:B11

5. Press ENTER

Pseudo Formula:

Task 2 - Type a function into a cell

1. Click cell B16

2. Type =

3. Type MAX

4. Double-click MAX

5. Select range B4:B11

6. Press ENTER

Task 3 - Use functions to calculate the Minimum row
Using either the Insert Function Menu or the typing the MIN function, get the values for the Minimum

# data[].attributes.body
A complex formula is one that uses more than one arithmetic operator (+,-,*,/). In this step we want to calculate a 20% rise in expenses for our travel company.

Data
The data we need to complete his step is the Total

Pseudo Formula: Total + Total * 20%

Task 1 - Enter formula into cell B14

1. Click cell B14

2. Type =

3. Click cell B12

4. Type +

5. Click cell B12

6. Type *.2

7. Click the Enter formula (check mark) on the Formula Bar

Task 2 - Use formulas to Calculate 20% for Quarters 2, 3 and 4
To complete this task you can either enter the formula as we did in Task 1 or you can use rag and fill using the Fill Handle

# data[].attributes.body
Using your knowledge of Excel basics, you can develop your worksheets to include more complex formulas and functions.

Worksheet: EX B-1.xlsx

# data[].attributes.body
Please see the included picture to familiarize yourself with the Spreadsheet Pieces we will be using in today's lessons