Introduction

Excel is a powerful data analysis tool that you can use to troubleshoot and modify PowerSchool data. By the end of this course, you will be able to use a variety of functions and tools, including:

• Logical functions, like IF, AND, and OR, to evaluate whether a record meets criteria you specify
• Lookup and reference functions, like VLOOKUP (vertical look up) and MATCH, to connect a value in one worksheet to a value in another
• Mathematical functions, such as COUNTIF and SUMIF, to tally and sum values that meet certain criteria, and perform arithmetical operations
• Data validation and field protection to control and protect your data

Using Logical Functions

In Excel, logical functions test for certain conditions, and return either TRUE or FALSE or a value that you define, depending on the function. One of the most useful and versatile logical functions is the IF function, especially when combined with other functions.

• IF The IF function performs a logical test (usually on the value in a particular cell) and returns one value if the result of the logical test is true, and another value if the result is false. In other words, if the logical test is true, the function does one thing; otherwise it does another thing.
• AND The AND function tests for two or more conditions and returns either TRUE if all conditions are met, or FALSE if at least one condition is not met. You can enter up to 255 arguments.
• OR The OR function is similar to the AND function in that you are able to test multiple (up to 255) conditions. However, the OR function only requires one of the conditions to be met for the function to result in TRUE.

Using Lookup And Reference Functions

Searching for and retrieving values from a range of cells are useful processes when analyzing data in Excel. Use MATCH when you want to know where a value exists in a range. Use VLOOKUP when you want to search for a value in a range and return a corresponding value from a different column.

• MATCH The MATCH function searches for a specified value in a range of cells and, if a match is found, returns the value’s position in the range (the row number within the range if you searched a column of cells, or the column number within the range if you searched a row of cells). Since similar types of data are usually organized into columns in Excel, searching within a column is more common. If the MATCH function finds no matching values, it returns #N/A.
• VLOOKUP The VLOOKUP function is similar to the MATCH function, but rather than returning the position of the matched value, it returns a value located in a different column of the same row as the matched value.
• COLUMN The COLUMN function has just one argument, a cell reference, and returns the column number within the worksheet of the cell. This function isn’t typically very useful on its own, but one way to make it more productive is to enter it as an argument within another function. When you use a COLUMN function instead of a fixed value in another function, the argument becomes dynamic, and the result will change as you autofill the formula into other cells.

Using Conditional Math Functions

Use conditional math functions to perform mathematical operations on values in a range based on certain criteria.

• COUNTIF Use COUNTIF to tally the number of times a value that meets your criteria occurs in a range.
• SUMIF Use SUMIF to add up values in a range when the values meet the criteria you define.
  
  Note that the criteria can refer to a range of cells other than those to which you want to apply the mathematical operation. For example, you can test one column for the presence of one value, and sum the values in a corresponding column when the function finds the first value.

Using Data Validation And Field Protection

Use data validation to limit the information that users can enter into a cell or range of cells.
Use field protection to restrict access to cells in order to keep users from deleting or making changes to data or formulas.
 

Key Points

• The IF function is a powerful and versatile tool, especially when paired with other functions, such as AND and OR
• The VLOOKUP function is great for finding values when the data is formatted in the right way; the INDEX/MATCH method is more involved, but doesn’t have the same restrictions
• Nesting a COLUMN function in a VLOOKUP or INDEX/MATCH makes returning entire records much quicker
• When using SUMIF or COUNTIF, remember that the criteria argument can refer to a range of cells other than those to which you want to apply the mathematical operation
• Use data validation and field protection to control and protect the data in your cells and worksheets