Test 2 Notes

Spreadsheet (Excel)

Test 2 will focus on:

• Basic functions in Excel
• If statement with calculations
• Vlookup
• Advanced Filtering
• Octal conversion to binary and to decimal (Two questions)

Syntax

If statement
if(logical_test,value_if_true,value_if_false)
            (1)            (2)                (3)

1.  The logical test is what tests the condition.  For example average > 70
2.  The value_if_true will be displayed if the condition is met
3.  The value_if_false will be displayed if the condition is not met

Vlookup
vlookup(lookup_value,table_array,col_index_num,range_lookup)
                     (1)               (2)                  (3)                  (4)

1. The lookup value is the cell address that has the value that we will look up in the table array to compare and find the answer,
2. The table array is where on the spreadsheet has the columns with the answer and comparison data.
3. The column index number is the number of the column that has the answer we are searching for.
4. The range lookup is either true or false.  False is what we are looking up for is exact and True if it is an approximate.

Notes:

Filtering is extracting data based on a condition.
The condition is known as a criterion (criteria is the plural of criterion)
A criterion with one condition takes up two cells
A criteria with two condition takes up four cells

Example:
                   Name
                   ="John"
This extracts all people with the name John

                    Age
                     >17
This extracts all people who are older than 17 years

                     Items
                      B*
This extracts all Items with the name starting with the letter B

PRACTICE ACTIVITY

1. Using the spreadsheet below, use vlookup to identify each page name.
2. Perform advanced filtering to extract all Pages that had more than 20,000 views and Page Name has the letter N in it.
3. Calculate if the Page views is more than 200,000 then list it as Active; otherwise list as inactive.

You can click on the spreadsheet to view as a larger image

IF Statements with Calculations:

1.  Cell A29 has the sum of three numbers.  If the sum is more than 25, then calculate and output the square of the sum; otherwise calculate and output the cube of the sum.

2.  Cell B3 has a value.  If the value is less than 100, then output that the value is small; otherwise output that the value is larger.

3.  Cell G78 has a value.  If the value exceeds 50, add 10% to the value; otherwise, add 3% to the value.  (Remember that the % sign is not to be used in the calculation; it should be represented in decimal format)

4.  If the sum of B3 to G3 is more than 100, then calculate the product of cell B3; otherwise, calculate the average of the range.

GOOD LUCK ON YOUR TEST