Calling all Data Geeks! Corey McAfee October 24, 2014
File Access Lower case ‘L’
http://goo.gl/VlQGzl
Topics Covered
Conditional Formatting Find/Replace Sorting Filtering Using Named Ranges Advanced Formulas (IF,VLOOKUP) Pivot Tables
Conditional Formatting Basics Automatically apply formatting to cells based on the value
Can apply to a single column/row or an entire sheet Can use built-in or custom rules/formats
Can use multiple rules
Conditional Formatting How-To
Conditional Formatting Application In the Comprehensive Data File: Highlight in red students with a score of less than 200 (“Highlight Cells Rules”) Highlight in green students with a score of 200 or above (“Highlight Cells Rules”) Clear the above formats, then highlight all students with above average scores (“Top/Bottom Rules”) Highlight the entire row for all students with a test duration of less than 25 minutes
Find/Replace Basics
Quickly find specific text Quickly replace with other text
Find/Replace How-To
Find/Replace Application
In the Comprehensive Data File: Find the first student in the CDF who scored 210 Replace all instances of your school’s name with a different name (only in the SchoolName column)
Sorting Basics
Sort Excel data on one column Sort Excel data on multiple columns Sort in ascending or descending order
Sorting How-To
Sorting Application
In the Comprehensive Data File: Sort scores in ascending order Sort by test subject in ascending order, then by scores in ascending order
Filtering Basics Filter your Excel data if you only want to display records that meet certain criteria To remove the filter for a single column, click the filter arrow and select “Clear filter from…” To remove the filter from all columns, on the Data tab, click Clear To remove the filter AND the arrows, click Filter
Filtering How-To
Filtering Application
In the Comprehensive Data File: Filter to show only a single test subject Remove filtering arrows
Named Ranges Basics A range in Excel is a collection of two or more cells Drag/select the cell or range of cells to be named Click in the Name box, to the left of the formula bar, and type a name Edit the name/range using Name Manager on the Formulas tab
Named Ranges How-To
IF Function Basics
The IF function checks whether a condition is met Returns one value if TRUE and another value if FALSE Syntax:
=IF(logical_test, value_if_true, [value_if_false])
IF Function How-To
Nested IF Function How-To
AND/OR Functions Basics
The AND function returns TRUE if all conditions are true and returns FALSE if any of the conditions are false The OR function returns TRUE if any of the conditions are true and returns FALSE if all conditions are false
AND/OR Functions How-To
IF/AND/OR Functions Application In the Comprehensive Data File: Insert a new column for the function, then Write a function to identify students with a test duration less than 15 minutes Write a function to identify students with a test duration less than 20 minutes AND a score less than 190 Write a function to identify students with a test duration less than 20 minutes OR a score less than 190
VLOOKUP Function Basics
The VLOOKUP (Vertical Lookup) function looks for a value in the LEFTMOST column of a table or range, and then returns a value in the same row from a column you specify Syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
VLOOKUP Function How-To =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
lookup_value: Required. The value you would like to search in the first column of the lookup table or range. The lookup_value argument can be a value or a reference. If the value you supply for the lookup_value argument isn’t found in the first column of the table_array argument, VLOOKUP returns the #N/A error value.
table_array: Required. The range of cells that contains the data you want to search. You can use a reference to a range (for example, A2:D8), or a named range. The values in the first column of table_array are the values searched by lookup_value. Uppercase and lowercase text are equivalent.
VLOOKUP Function How-To =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
col_index_num: Required. The column number in the table_array argument from which the matching value must be returned. A col_index_num argument of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on. If the col_index_num argument is: Less than 1,VLOOKUP returns the #VALUE! error value. Greater than the number of columns in table_array, VLOOKUP returns the #REF! error value.
VLOOKUP Function How-To =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
range_lookup: Optional, but critical. A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match: If range_lookup is either TRUE (or 1) or is omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned. If the range_lookup argument is FALSE (or 0),VLOOKUP will find only an exact match. If there are two or more values in the first column of table_array that match the lookup_value, the first value found is used, meaning that SORT ORDER CAN BE CRITICAL. If an exact match is not found, the error value #N/A is returned.
As data geeks, we will almost ALWAYS use FALSE.
VLOOKUP Application In the Comprehensive Data File: Starting with the StudentID column, select to the right to TestRIT and down to the bottom of the CDF Name the range Copy a few rows from the StudentID column and paste to Column A(1) in a new worksheet (name your columns!) Write a VLOOKUP in Column B(2) to pull TestRIT into your sample worksheet If you have time, try to pull student names from the “Student List By School” into your “Assessment Results”
Pivot Tables Basics
Pivot tables are one of Excel's most powerful time-saving features A pivot table allows you to extract the significance from a large, complex data set
Pivot Tables How-To
Pivot Tables How-To
Pivot Tables How-To
Pivot Table Application Determine how many Math tests were taken Determine how many individual students took the Math test Determine the percentage of students whose test duration was between 30 and 40 minutes Generate a facsimile of the CDF only containing students who scored a 200 in Reading (don’t use filtering in the CDF) Generate a table showing the average RIT score in all subjects If you have time, generate a table showing average test RIT for 10 minute test-duration groups, then generate a Pivot Chart representing this data
Questions?
Corey McAfee
Assessment and Research Analyst, The New America School
[email protected]
