Download Excel Crash Course Exam Answer Sheet: Essential Shortcuts, Functions, and Tips and more Exams MS Microsoft Excel skills in PDF only on Docsity!
100% A+ ANSWER SHEET - WALL STREET PREP
EXCEL CRASH COURSE EXAM ANSWER SHEET
"Get inside a drop-down list - CORRECT ANSWER Alt+Up/DownArrow (Option+Up/DownArrow)"
"Get Rid of Borders - CORRECT ANSWER Ctrl+Shift+-"
"Scenario Analysis Using XLOOKUP - CORRECT ANSWER You can use XLOOKUP's ability to generate
multiple values to create a scenario analysis for something like an income statement"
"SUMPRODUCT - CORRECT ANSWER =SUMPRODUCT(array1,array2,array3,...) multiples corresponding
components in two or more arrays and returns the sum of those products A lesser known features is the ability to embed criteria directly into the arrays"
"SEARCH - CORRECT ANSWER =SEARCH(find text, within text, start number)
Finds the text within the string text starting at the start number position and outputs the position of the text in the string"
"FIND - CORRECT ANSWER =FIND(find text, within text, [start number])
The same as SEARCH but is case sensitive!"
"SUBSTITUTE - CORRECT ANSWER =SUBSTITUTE(text, old text, new text, [instance number]) replaces
old text within a string of text ("text") with new text If there are several instances of the old text, you can identify which instance with instance number"
"REPLACE - CORRECT ANSWER =REPLACE(old text, start number, number of characters, new text)
replaces a portion of a string with another string/number, where the portion of the string being replaced is identified by the starting number position and the number of characters Note: To replace with text, use quotation marks!"
"Flash Fill - CORRECT ANSWER Excel 2013 has introduced a real improvement to working with large
data sets in the form of Flash Fill Tries to guess at to what kind of data you're trying to get at Shortcut: Ctrl+E or Alt+A+F+F (Data > Flash Fill)"
"Remove Duplicates - CORRECT ANSWER Data > Remove Duplicates
Highlight the relevant data range and then click remove duplicates Select all columns to ensure true duplicates"
"Combining Sort & Subtotal - CORRECT ANSWER Combining Sort with Subtotal - Alt+A+B (Data >
Subtotal) - can add further clarity to data sets Sort by category and then sum"
"COUNTIF as an array - CORRECT ANSWER If you want to see if any values in an array equal any values
in the criteria, you can use COUNTIF. However, just using COUNTIF gives you 0 and simply stores the proper array. To properly count, you can use SUMPRODUCT and COUNTIF: =SUMPRODUCT(COUNTIF(range array, criteria array)"
"Recording Macros - CORRECT ANSWER Excel allows you to record a sequence of instructions, and
assign a keyboard shortcut to invoke them as desired These instructions are called macros Go to File > Record Macro"
"Name Box - CORRECT ANSWER Tells you what cell you are in (top left below the ribbon)"
"Formula Bar - CORRECT ANSWER When you insert a formula into a cell and hit return, the cell will
show you the output The formula bar, however, will show you the formula Next to "fx" right below the ribbon"
"Moving Between Worksheets - CORRECT ANSWER Ctrl + PageDown/PageUp
(Option + RightArrow/LeftArrow)"
"Adding Worksheets - CORRECT ANSWER (Fn Shift F11)"
"Columns - CORRECT ANSWER Alphabetically labeled (A, B, C, etc.)"
"Rows - CORRECT ANSWER Numerically labeled (1, 2, 3, etc.)"
"How to Access Ribbon on Mac - CORRECT ANSWER Ctrl Fn F
Use arrow keys to peruse the Mac ribbon"
"Using Function Keys - CORRECT ANSWER Hit Fn and then the function key to use the function keys
themselves on a Mac"
"Open a New Workout - CORRECT ANSWER Ctrl N
Cmnd N"
Calculation/Functions: Change Calculation Options to "Automatic except for data tables" Check "Enable iterative calculation" Edit/Advanced: Uncheck "After pressing Enter, move selection" *Optional - Check "Automatically insert a decimal point""
"Protection - CORRECT ANSWER Go to File > Info > Password Protecting Files (File > Passwords)
Alt F T I You can password protect files "Encrypt with Password" You can also protect individual worksheets so that people can't see or edit formulas "Protect Current Sheet""
"Shortcuts to Allow the User Input to Add More Sheets to a Workbook - CORRECT ANSWER Alt i w
Alt h i s Shift F Alt Shift F1"
"Find or Find and Replace Shortcut - CORRECT ANSWER Ctrl F
(Cmnd F)"
"What is the recommended workbook calculation setting for Excel? - CORRECT ANSWER Automatic
Except for Data Tables"
"Autofit Row Height Command - CORRECT ANSWER Alt H O A"
"Autofit Column Height Command - CORRECT ANSWER Alt O C A"
"Assign Column Width Command - CORRECT ANSWER Alt H O W
(Home > Format > Column Width)"
"Assign Row Height Command - CORRECT ANSWER Alt H O H
(Home > Format > Row Height)"
"Command to Change Zoom Size - CORRECT ANSWER Alt+V+Z
(Ctrl+MouseScroll)"
"Autofit the Column Width Command - CORRECT ANSWER Alt H O I
(Home > Format > Autofit Column Width)"
"Ctrl Commands - CORRECT ANSWER Most commands involving Ctrl are shortcuts that are automated
by default to make Excel more efficient and user friendly"
"Save As Shortcut - CORRECT ANSWER Alt F A"
"Alt Commands - CORRECT ANSWER Most commands involving Alt are shortcuts to the commands
and functions inside the default eight Main Tabs"
"Select Column & Range of Columns - CORRECT ANSWER Ctrl Space
Range: Ctrl Space Shift+RightArrow"
"Select Row & Range of Rows - CORRECT ANSWER Shift Space
Range: Shift Space Shift+DownArrow"
"Undo - CORRECT ANSWER Ctrl Z"
"Operations in Excel - CORRECT ANSWER "
"Copying Across Formulas - CORRECT ANSWER Ctrl + C to copy, Ctrl + V to paste"
"Open Format Cells Dialog - CORRECT ANSWER Ctrl+
(Cmnd+1)"
"Navigating Format Cells Dialog - CORRECT ANSWER Using the Arrow keys to get around the tab
To get in a tab, either use Tab key or use Alt and the relevant letter Use Space to toggle check boxes"
"Highlight a Contiguous Range - CORRECT ANSWER Ctrl + Shift + Arrows
(Cmnd + Shift + Arrows)"
"Combining Data in Two Sheets into Another Sheet - CORRECT ANSWER (1) Copy and paste the
relevant headers into the third sheet (2) Type "=" in the relevant column in the third sheet and then use Ctrl PageUp/Down (Option Right/LeftArrow) to get to the first sheet and find the right data (3) Press "+" and then find the relevant data in the second sheet and press "Enter" (4) Ctrl+C to copy that formula and then apply it to the rest of the table (5) Delete and retype any important formulas (ex. Net Profit) (6) Add in formatting by Ctrl+C the table in sheet 1 or 2, moving to the upper corner of the table in sheet 3 and using Paste Special and then format: Alt+E+S+T or Ctrl+Alt+V+T (Ctrl+Cmnd+V+T)"
"Paste Special - CORRECT ANSWER Alt+E+S
"Highlight Formula Elements & Maintain Contiguous Elements - CORRECT ANSWER Ctrl+Shift+Arrows
(Cmnd+Shift+Arrows)"
"Highlight the Whole Formula of an Active Cell - CORRECT ANSWER Shift+DownArrow
(Shift+Up/DownArrow)"
"How to Revert Back to Original Formula after Editing an Active Cell - CORRECT ANSWER Esc"
"How to Exit Cell Edit Mode in an Active Cell - CORRECT ANSWER F
(Fn+F2)"
"Clearing a Cell - CORRECT ANSWER Alt+H+E (Home>Clear)
Clear All: Alt+H+E+A Clear Format: Alt+H+E+F Clear Comments: Alt+H+E+M"
"Add Comment - CORRECT ANSWER Shift+F2 (Shift+Fn+F2)
Esc twice to exit"
"Right Fill from Cell Left - CORRECT ANSWER Highlight the cell you want to copy
Shift+RightArrow to the cells that you want to be filled Hit Ctrl+R"
"Down Fill from Cell Up - CORRECT ANSWER Highlight the cell you want to copy
Shift+DownArrow to the cells that you want to be filled Hit Ctrl+D"
"Inserting Rows - CORRECT ANSWER (1) Go to any cell in the row below the desired row
(2) Press Alt+I+R OR (1) Highlight the row below the desired row with Shift+Space (2) Press Ctrl Shift + to insert the new row"
"Inserting Columns - CORRECT ANSWER (1) Go to any cell in the desired column
(2) Press Alt+I+C OR (1) Highlight the desired column by pressing Ctrl+Spacebar (2) Insert a column by pressing Ctrl Shift +"
"Deleting Rows and Columns - CORRECT ANSWER Row: Alt+H+D+R
Column: Alt+H+D+C OR
Highlight the row/column and press Ctrl -"
"Paste Only Formulas - CORRECT ANSWER Alt+E+S+F"
"Paste Only Formatting - CORRECT ANSWER Alt+E+S+T"
"How to change a list of numbers quoted in 1,000s to 1s - CORRECT ANSWER In another cell, enter the
number 1000 Copy this cell, then highlight your list of numbers Press Alt+E+S+M (Paste Special Multiply)"
"Paste Special Operations - CORRECT ANSWER Allows you to apply operations to large amounts of
data You can convert large amounts of data to positive to negative or vice versa *Note: You should ONLY paste on numbers that are hard inputs"
"Converting Positive Numbers to Negative - CORRECT ANSWER Ctrl+C the data values
Special paste the new numbers using the Subtract operation - Alt+E+S+S OR In another cell, enter the number - Ctrl+C -1 and special paste on the relevant numbers using the multiply operation (Alt+E+S+M)"
"Ctrl Shortcuts - CORRECT ANSWER Cut: Ctrl+X"
"Cut Cells - CORRECT ANSWER Ctrl+X
Note: Dependent calculations DO NOT change when you cut cells!"
"Number Format: 2 decimals, 000 separator - CORRECT ANSWER Ctrl+Shift+!"
"Currency Format: 2 decimal places - CORRECT ANSWER Ctrl+Shift+$"
"Percentage Format with No Decimal Places - CORRECT ANSWER Ctrl+Shift+%"
"Date Format with the Day, Month, and Year - CORRECT ANSWER Ctrl+Shift+#"
"Boldface - CORRECT ANSWER Ctrl+B"
"Italicize - CORRECT ANSWER Ctrl+I"
"Underline - CORRECT ANSWER Ctrl+U
"Distinguishing Constants - CORRECT ANSWER Constants are usually distinguished from formulas, such
as by blue text vs. black text"
"Identifying Where Blanks Are - CORRECT ANSWER (1) Highlight the relevant region
(2) Use Go To Special and select blanks (3) Hit Enter"
"Identifying Where Comments Are - CORRECT ANSWER (1) Highlight the relevant region
(2) Use Go To Special and select comments (3) Hit Enter"
"Bottom Bar Customization - CORRECT ANSWER Right click on the bottom bar to change what you see
from your selection (ex. Average, Count, Max, Sum, etc.)"
"Conditional Formatting - CORRECT ANSWER Allows you to create your own conditional formats or use
a preset from Excel Alt+O+D or Home > Conditional Formatting > New Rule / Alt+H+L > New Rule for customs Alt+H+L or Home > Conditional Formatting > Highlight Cells Rules for presets"
"Identify which numbers in a column are above some number - CORRECT ANSWER (1) Highlight the
relevant range (2) Hit Alt+O+D (3) Select "Use a formula to determine which cells to format" (4) Write "=" and select the first number in the column (5) Anchor the column (6) Write ">" and some number or cell that is your reference (7) Press Enter Ex. "= $C3>500""
"And Functions - CORRECT ANSWER =and([first statement],[second statement)"
"Dynamic Headers & Text - CORRECT ANSWER Name: ="Income Statement for "&[Cell]
Date: ="Share price as of "&TEXT([Cell], "mm/dd/yy")"
"Custom Formatting - CORRECT ANSWER (1) Type the number
(2) Go to format tab (3) Go to "Custom" (4) Type in your custom format as [positive numbers];[negative numbers];[zero];[text] Multiple: 0.0x_);(0.0x);@_) 1 = True/0 = False: "True";"Invalid";"False" n "Years": 0 "years""
"Insert Line Breaks - CORRECT ANSWER Alt+Enter (Option+Enter)"
"Custom Format: Aligning Decimal Points - CORRECT ANSWER Add ")" after and "(" before the
positive numbers and zero formatting Ex. (0.0);(0.0)"
"Custom Format: Adding a Comma Separator - CORRECT ANSWER Add "#,##" before the zeros
Ex. (#,##0.0);(#,##0.0)"
"Custom Format: Aligning Non-Multiple Numbers with a Multiple - CORRECT ANSWER Add "x_" after
the end _ for positive numbers and after the closing parenthesis for negative numbers Ex. (#,##0.0_x);(#,##0.0)_x" "Custom Format: Negative number in parenthesis, aligned with positive number format, "Balance" when
result is 0 - CORRECT ANSWER #,##0.00_);(#,##0.00);"Balance""
"Custom Format: Negative number in parenthesis, aligned with positive number format - CORRECT
ANSWER #,##0.00_);(#,##0.00)"
"Custom Format: Multiple "x" format. Negative numbers in parenthesis, aligned with positive number
format - CORRECT ANSWER (#,###0.0x);(##,##0.0x)"
"Custom Format: Negative number in parenthesis, aligned with positive number format and multiple "x"
format - CORRECT ANSWER (#,###0.0_x);(##,##0.0)_x"
"Custom Format: L + [] Basis Points - CORRECT ANSWER L + 0 "bps""
"Custom Format: Changing the Color of Negative Numbers to Red - CORRECT ANSWER Add [red] at
the beginning of the negative numbers format Ex. (#,##0.0);Red"
"Most Common Excel Errors - CORRECT ANSWER #DIV/0! Divided by zero
#REF! Referencing a previously deleted cell #NUM! Number not valid #NAME? Text not valid (ex. incorrect function name) #VALUE! Incorrect arguments (ex. Using text as a number) ####### Column not wide enough"
"Find and Replace - CORRECT ANSWER Ctrl+F
Write what you want to find Click Replace Write what you want to replace
“Keyboard Versus the Mouse - CORRECT ANSWER Almost everything that can be done in Excel using a
mouse can also be done using the keyboard shortcuts Best way to learn is to disconnect the mouse and work through Excel using only the keyboard"
"Worksheets - CORRECT ANSWER An Excel file is called a workbook;
You start with 1 worksheet but you can add/delete more The active worksheet is highlighted in Excel"
"Main tabs - CORRECT ANSWER Although we focus on shortcuts, virtually all commands, functions,
and Excel features can be accessed through the task-oriented tabs which organize them into nine logical categories: (1) Home (2) Insert (3) Draw (4) Page Layout (5) Formulas (6) Data (7) Review (8) View (9) Developer"
"Mac Settings to Disable - CORRECT ANSWER (1) Function Keys: Settings - Keyboard - Use F1, F2, etc.
keys as standard function keys (2) Mission Control: System Preferences - Keyboard - Shortcuts - Mission Control - Disable "Move a space left" and "Move a space right" to use Ctrl RightArrow or Ctrl LeftArrow"
"The File Tab - CORRECT ANSWER Many Excel features (Open file, Save file, Print file, etc.) are located
in this tab Excel also has alternative keyboard shortcuts (using Ctrl) for many of these features Open a File: Ctrl O (Cmnd O) Save a File: Ctrl S (Cmnd S) Print a File: Ctrl P (Cmnd P)"
"Basic Excel Drills - CORRECT ANSWER Most keyboard shortcuts involves Alt or Ctrl keys
Alt: Press each key and let go (do you NOT need to hold to the Alt key) Ctrl: Ctrl key must be held down as you press the other key in the shortcut sequence"
"Excel Formulas - CORRECT ANSWER Start with the = sign
The = sign tells Excel that the info that will follow the = sign should be treated as a formula and not as plain text Once you type in the = sign, use the arrow keys to navigate around the Excel workbook to find the cells you need for your formula"
"Referencing Cells from Other Workbooks - CORRECT ANSWER (1) Hit "="
(2) Hit Ctrl+Tab (Cmnd ~) to go to the other workbook (3) Find the relevant data and hit Enter"
"Freezing and Splitting Panes - CORRECT ANSWER Excel worksheets often become too large to allow
users to view all of their contents on one screen 'Freezing Panes' and 'Splitting Panes' options provide users with the flexibility to select specific rows and columns that always remain visible when scrolling in the worksheet"
"Logical Functions: IF - CORRECT ANSWER Returns one value if a condition you specify evaluates to
TRUE and another value if it evaluates to FALSE Use IF to conduct conditional tests on values and formulas All IF statements follow the same structure =IF(x, y, z), where... (see picture) Text output is designated by quotation marks around the outputs"
"Greater Than or Equal to Functions - CORRECT ANSWER <="
"Nested IF statements - CORRECT ANSWER Generally follows the structure:
=IF([First Criteria for Yes], =IF([Second Criteria for Yes],"Yes","No"),"No") OR =IFS(Criteria 1, Value if Criteria 1 is True, Criteria 2, Value if Criteria is True, ...) Note: There's no longer a value if false with the IFS statement; However, you can make a criteria TRUE, which becomes the if false argument"
"Error-Trapping Function IFERROR - CORRECT ANSWER =IFERROR(value, value_if_error) returns a
value you specify if a formula evaluates to an error If the formula does not result in an error, IFERROR returns the result of the formula"
"Creating Dynamic Headers by Combining Cell References with Text ("&") - CORRECT ANSWER Excel
allows users to combine (or "concatenate") cells with a text string in them with other text strings, creating one text string by using the "&" function Ex. ="Income Statement for "&A1, where A1 is the company name"
"Date Functions (EOMONTH) - CORRECT ANSWER =EOMONTH(start_date,months) allows you to
create monthly date headers by outputting the last day of a specified month start_date represents a starting date reference months represents x number of months before or after the start_date Note: To output a date x months before a start_date, x should be negative"
"EDATE Date Functions - CORRECT ANSWER =EDATE(start_date, months) is a similar function to
EOMONTH
However, EDATE returns the exact date, x months from the start date"
"Range Lookup - CORRECT ANSWER "
"INDEX - CORRECT ANSWER =INDEX(Array, Row Number, Column Number)
Selects a value from an array of values with the appropriate row number and column number of the array"
"CHOOSE - CORRECT ANSWER =CHOOSE(Index Number, Value 1, Value 2, ...)
Selects a number of values (the index number) out of a list of delineated values"
"OFFSET - CORRECT ANSWER =OFFSET(Reference, Rows, Columns, [Height], [Width])
Define a reference point (the top left corner); this function spits out a result that is x rows below and y columns to the right of the reference point"
"Common Errors with HLOOKUP, VLOOKUP, CHOOSE, OFFSET, & INDEX - CORRECT ANSWER If you add
a row into a table, the HLOOKUP, INDEX, and OFFSET are instantly screwed up, as they depend on the rows If you add a column into a table, the INDEX, VLOOKUP, and OFFSET are messed up"
"MATCH Function - CORRECT ANSWER The MATCH function returns the relative position (number) of
an item in an array that matches specified lookup value Syntax: =MATCH(lookup_value,lookup_array,match_type) It does NOT return the value within the cell itself (as opposed to the HLOOKUP and VLOOKUP functions) Match type is an exact match (0), greater than (-1), less than (1) -- we only really use 0"
"Combining MATCH Function with Lookup & Reference Functions - CORRECT ANSWER Combining
MATCH with functions like HLOOKUP, VLOOKUP, OFFSET, INDEX, and CHOOSE makes formulas more durable and dynamic See Lookup & Reference sheet on Practice Sheet for examples"
"INDIRECT - CORRECT ANSWER =INDIRECT(reference text) returns the reference specified by a text
string =INDIRECT("B4") will output the value of what is in cell B The most common way to get value out of this function is to combine with concatenate (&) Ex. When creating a model for a flexible user defined start and end date for calculating a cumulative EBITDA result, you can use INDIRECT and &"
"Data Validation & Creating Drop-Down Menus - CORRECT ANSWER A utility in Excel whose most
frequently used feature is its ability to create simple and quick drop-down menus (1) To create a dropdown menu, with the cell where you want your drop-down menu active, open the data validation form Alt+D+L or Alt+A+V+V (Data>Data Validation) (2) Within the Settings tab, select list from the dropdown menu
(3) Within the 'Source:' field, identify a contiguous cell range containing the data you want to include in your dropdown, and hit OK and you should see your dropdown menu appear (note: it only appears when you are on the active cell)"
"Combining INDIRECT with MATCH - CORRECT ANSWER To combine INDIRECT with MATCH, you can
use INDIRECT's second argument: TRUE is in the form A1, FALSE is in the form R1C1, or row 1 column 1 Ex. =INDIRECT("R1C1",FALSE) Now, you can use INDIRECT, MATCH, and & to create a dynamic function Note: With INDIRECT, you have to start the MATCH array from the very edge of the worksheet (first column and first row) for the correct row and column number; or, you can add the number of rows above or columns to the left of the beginning of your array to the MATCH function"
"The Address Function - CORRECT ANSWER =ADDRESS(row_number,column_number)
With INDIRECT and MATCH, you can dynamically find a value in a table using the ADDRESS function (see picture) Also makes working between worksheets easier -- add the relevant sheet name in quotations with =ADDRESS(row_number,column_number,,,sheet_name) Ex. =ADDRESS(1,2,,,"Sheet1") for R1C1 of Sheet1"
"COLUMN and ROW Functions - CORRECT ANSWER =COLUMN() gives you the current column and
=ROW() gives you the current row =COLUMN(reference) and =ROW(reference) gives you the column and row of a reference point =COLUMNS(array) and =ROWS(array) gives you the number of columns and rows in an array"
"Using COLUMN and ROW Functions as Counters in Complex Formulas - CORRECT ANSWER Use
COLUMNS(array) and ROWS(array) to act as counters when using INDIRECT w/ MATCH to create a dynamic counter in the function"
"Evaluate - CORRECT ANSWER Ctrl +"
"Data Tables - CORRECT ANSWER Allow us to examine a piece of output data - such as a company's
EPS - and how it is impact by changes in input variables such as revenues and gross margin assumptions Output the results in a presentation-friendly matrix Often used for sensitivity analysis (i.e. EPS's sensitivity to changes in gross profit margin) and is used widely by analysts to illustrate a range of possible output values"
"Building a Vertical Data Table - CORRECT ANSWER Layout assumptions on the LEFT
(1) Identify the output variable The variable you are trying to sensitize is the output variable Must be referenced from your analysis into the top right corner of the data table (2) Hard-code the input variable sensitivities The variables whose impact on the output variables you want to analyze are the input variables Input variable assumptions should not be referenced from the analysis, but rather be hard-coded and arranged in the column to the left of the output variable
(See XLOOKUP workbook)"
"Using XLOOKUP to Generate Multiple Values - CORRECT ANSWER If you set up your search returns as
the same number of columns as the data table and then set up the return array in the XLOOKUP function as the full data table, it will automatically occupy the full search returns"
"Where XLOOKUP Loses to INDEX MATCH - CORRECT ANSWER When trying to create a master formula
that can be copied across an entire range, XLOOKUP loses to INDEX MATCH or INDEX XMATCH XMATCH (See last worksheet of XLOOKUP workbook for example)"
"XMATCH - CORRECT ANSWER =XMATCH(lookup_value,lookup_array) only gives you an exact match,
so you only have to define two arguments instead of three! Use this instead of MATCH"
"Booleans in Excel - CORRECT ANSWER When Excel spits out a TRUE or FALSE (see picture), you can
convert them respectively into 1 or 0 by applying any operator on them Interestingly, multiplying a TRUE (or FALSE) by another TRUE (or FALSE) also has the effect of converting it into a 1 or 0, respectively"
"SUMPRODUCT with Embedded Criteria - CORRECT ANSWER A lesser known feature of SUMPRODUCT
is the ability to embed criteria directly into the arrays For example, we can directly calculate proceeds on options that have an exercise price less than the share price so you no longer need to calculate option proceeds for each tranche: We have 2 criteria -- the options # and the exercise price per tranche, which is multiplied against a TRUE or FALSE criteria for each exercise tranche Tranche 1 evaluates to TRUE, so Excel multiplies the TRUE by the Tranche 1 exercise price, and then by the # of options Tranche 2 & 3 evaluate to FALSE, and become 0 when multiplied by the exercise prices Note: See MATH sheet of Excel Practice workbook for more examples"
"When Are Options Exercised? - CORRECT ANSWER When their exercise price is less than the strike
price (they're "in the money")"
"SUMIF - CORRECT ANSWER =SUMIF(range, criteria, sum range) adds the cells specified by a given
criteria The range is the range that you want to evaluate with the criteria, whereas the sum range is what is actually summed Criteria can either be hardcoded which requires quotation marks as you see in the picture, or a direct cell reference (which would not need quotes around it)"
"SUMIFS - CORRECT ANSWER =SUMIFS(range1, criteria1, sum range1, range2, criteria2, sum range2,
etc.)
Same as SUMIF but can handle multiple criteria and sum ranges"
"AVERAGEIF and AVERAGEIFS - CORRECT ANSWER Identical to SUMIF and SUMIFS but instead of
summing, this function averages the data in the range"
"Absolute Value - CORRECT ANSWER =ABS(cell or number) gives you the absolute value"
"Ceiling - CORRECT ANSWER =CEILING(number, significance)
Rounds up to the nearest x amount with a certain level of significance Ex. If you want it rounded up to the nearest 10th, the significance = 0.1"
"Floor - CORRECT ANSWER =FLOOR(number, significance)
Rounds down to the nearest x amount with a certain level of significance Ex. If you want it rounded down to the nearest 10th, the significance = 0.1"
"Combinations Function - CORRECT ANSWER =COMBIN(number, number chosen)
Ex. =COMBIN(4, 2) gives you the number of two person combinations out of a number of 4 people Note: Given the useful life of an asset, you can find the return sum of years' digits with =COMBIN(useful life + 1,2)"
"Round Functions - CORRECT ANSWER =ROUND(number,number of digits) rounds the number to the
specified number of decimal places =ROUNDUP rounds up =ROUNDDOWN rounds down"
"MIN Function - CORRECT ANSWER =MIN(number 1, number 2, ...) returns the smallest number in a
specified set of values"
"MAX Function - CORRECT ANSWER =MAX(number 1, number 2, ...) returns the largest number in a
specified set of values A classic use in financial modeling is to use a max function to prevent a revolving credit line balance from dipping below 0 when there is a cash shortfall"
"COUNT, COUNTA, and COUNTIF Functions - CORRECT ANSWER COUNT =COUNT(value1, value2, ...)
counts the number of cells that contain numbers within the list of arguments; cells with text are disregarded COUNTA Same as COUNT except cells with numbers and text are counted COUNTIF =COUNTIF(range, criteria) counts the number of items in the range that satisfy a specific criteria - similar to the SUMIF function"
"COUNTIF Syntax - CORRECT ANSWER If you want the criteria to be "equals a cell", just input the cell
number If you want it to be less than or greater than, use quotation marks and &:
