Amber Hutchison 145 614 572 8252 This review sheet covers the most common questions I get about excel functions as well as some of the mistakes I ve seen people make Please feel free to text me if anything on here doesn t make sense or if you re studying and working on practice problems and are confused I m happy to answer questions and will respond as quickly as I m able SUM PRODUCT MAX MIN SUMIF SUMIFS IF AND OR NOT VLOOKUP RATE NPER Excel Functions SUMPRODUCT AVERAGE LARGE SMALL AVERAGEIF AVERAGEIFS IFERROR HLOOKUP PMT PV COUNT COUNTA RANK EQ ROUND COUNTIF COUNTIFS FV General Excel Strategies Make sure you answer the entire question Pay attention to which direction if any you re copying your formula cell lock accordingly Pay attention to which worksheet you re working in and referencing page reference accordingly If you re given a constants units conversion section cell reference to it as much as possible DO NOT HARDCODE Look for key words Highlighting can make it easier to keep track of where you are and what s going on in a problem If you re not sure where to start see if you know which formula to use for any of the problems and start there Don t complicate the question it s possible you will be given simple calculation problems without functions You will be given the syntax on the exam but it is faster if you are familiar with it already Cell Locking Copying down the row numbers are the only thing that may change and the only thing you may need to cell lock Copying across the column numbers are the only thing that may change and the only thing you may need to cell lock Copying down and across both row numbers and column numbers may change you may need to cell lock both Key Words Pay attention these do not necessarily always indicate the given functions and this is not a complete list Key Word s Total with a single criteria with multiple criteria Average with a single criteria with multiple criteria Number with a single criteria with multiple criteria Maximum the most the highest Function s SUM SUMIF SUMIFS AVERAGE AVERAGEIF AVERAGEIFS COUNT COUNTA COUNTIF COUNTIFS MAX CSE 2111 Excel Review Amber Hutchison 145 Minimum the least the lowest The nth largest The nth smallest Rank priority Round If then T F true false whether or not if All every both each and Any at least one or Not The same equal Less than fewer not enough Greater than more than enough None not any Only each but not Inclusive between 614 572 8252 MIN LARGE SMALL RANK EQ ROUND IF Boolean Functions AND OR NOT NOT OR false things AND OR true things NOT OR false things AND Reference function Loan CD mortgage HLOOKUP VLOOKUP Financial Functions Rate yearly rate Total number of periods periods per year years Payments yearly payments To have how much do I need today How much is it worth today How much will it be worth RATE RATE periods per year NPER NPER years NPER PMT PMT periods per year PV FV Excel Function Dominance Some excel functions are more dominant than others meaning they are generally the first function you write when using multiple functions Some functions that are commonly dominant are the ROUND and IFERROR functions Both of these functions are intended to contain other functions as their arguments ROUND number num digits IFERROR value value if error Both the number argument in the ROUND function and the value argument in the IFERROR function could easily be very complicated functions on their own If you feel like you re going to be using either of these functions they will most likely be the most dominant Dominance with almost every other function depends on the context of the problem Pay particular attention to Boolean functions because their meaning changes drastically with different ordering Ex Both Bill and Fred or Both Lisa and Kelly OR AND B F AND L K compared with Bill or Fred and Lisa or Kelly AND OR B F OR L K Specific Excel Function Notes Value Based Functions CSE 2111 Excel Review Amber Hutchison 145 All of these functions accept individual values a series of values or a range of values as arguments Within each function these values are all treated the same regardless of order or size 614 572 8252 SUM number1 number2 number3 adds all of the numbers together PRODUCT number1 number2 number3 multiplies all of the numbers together AVERAGE number1 number2 number3 averages all of the numbers together COUNT value1 value2 value3 counts the number of numerical values in the range COUNTA value1 value2 value3 counts the number of non empty cells in the range MAX MIN vs LARGE SMALL The MAX and MIN functions will only ever return the largest or the smallest values in the range The LARGE and SMALL functions can return the largest or smallest but are more commonly used for the 2 nd largest or smallest the 3 rd largest or smallest etc Ex I want to know the 6th smallest value on the range A1 B20 SMALL A1 B20 6 ROUND The round function syntax is ROUND number num digits The number part of this function is whatever value you re trying to round it could be a formula a cell reference a hardcode any kind of numeric value The num digits part of this function refers to how far past the decimal point you want to round num digits number 1111 1111 3 2 1 0 1 2 3 1000 1100 1110 1111 1111 1 1111 11 1111 111 IF vs IFS When using the SUMIF S AVERAGEIF S COUNTIF S functions make sure that you are paying attention to how many criteria you are concerned with If there is only one criteria you want to use the SUMIF AVERAGEIF or COUNTIF functions If there are multiple you will want to use SUMIFS AVERAGEIFS or COUNTIFS Total SUM SUMIF SUMIFS Average AVERAGE AVERAGEIF AVERAGEIFS Number COUNT COUNTA COUNTIF COUNTIFS All items with a value greater than 50 single criteria Excel Review No Criteria One Criteria Multiple Criteria Examples of criteria CSE 2111 Amber Hutchison 145 All items that have type A single criteria All items with a value greater than 30 but less than 60 multiple criteria All items that have type A and a value greater than 10 multiple criteria 614 572 8252 IFS functions work similar to AND functions in that all of the criteria must be met If you only need one criteria or the other to be met such as all items that are type A or type B you will need to add two IF functions together SUMIF SUMIF Nested IF Functions When using nested IFs you will generally be using one fewer IF function than you have possible outcomes The first IFs will take care of a single outcome each but the last IF will take care of two outcomes It