Wall Street Prep Excel Crash Course Formulas Explained Exam 45 Questions with Verified Answers
Average Function - CORRECT ANSWER Definition: An Excel
... [Show More] function that adds a group of values, and then divides the result by the number of values in the group.
IF Statement Function - CORRECT ANSWER Definition: A function that returns one value when a condition you specify evaluates to true and a different value if it evaluates to false.
Explanation: First you begin with the logical test for the function, and then provide the value the function should output if the logical test is evaluated to true, and last value the function should output if the logical test is evaluated to false.
Nested IF Statement Function - CORRECT ANSWER Definition: IF function with various IF functions within each other.
IFS Statement/Function - CORRECT ANSWER Definition: A function that resolves the chunkiness of IF statements/functions and Nested IF statements/functions. Can allow for more than one IF statement all in one.
IFERROR Function - CORRECT ANSWER Definition: A function that returns a value you specify if the formula evaluates to an error. If the formula does not result in an error, this function will return the result of the formula.
Explanation: Begin by putting the value you want to see if an error is evaluated by this value, example: something divided by 0. Then input what the function should output if this is truly an error.
& Function - CORRECT ANSWER A function that concatenates cells with a text string.
EOMONTH Function - CORRECT ANSWER Definition: A function that allows you to create monthly date headers. By outputting the last day of the specified month.
Explanation: Start by inputting the start_date into the first argument. Then input how many months ahead or behind you would like to go from that start_date.
EDATE Function - CORRECT ANSWER Definition: Similar to EOMONTH but this return the exact date from the start-date, after the specified number of months.
Explanation: Start by inputting the start_date into the first argument. Then input how many months ahead or behind you would like to go from that start_date.
ISNUMBER Function - CORRECT ANSWER Definition: A function that is useful criteria in IF Statements/Functions, and it is basically test to see if a cell has a number in it.
Explanation: Simply input value to check to see if value inputted comes out to true or false.
ISTEXT Function - CORRECT ANSWER Definition: A function that evaluates if there is text inside a cell and will output TRUE or FALSE depending on the result. Also a useful criteria to add to IF statements/functions.
Explanation: Simply input value to check to see if value inputted comes out to true or false.
YEARFRAC Function - CORRECT ANSWER Definition: A function that return the proportion of the year between two given dates, the start_date and the end_date. An optional basis parameter can be used do represent the number of days per month/year used to calculate the proportion of the year.
Optional Basis Parameters:
1. 0 - or Omitted = U.S. 30/360
2. 1 - Actual/Actual
3. 2 - Actual/360
4. 3 - Actual/365
5. 4 - European 30/360
Explanation: Simply input the start_date and the end_date with the desired basis argument and the function will output the proportion of the year between the two given dates.
DATE Function - CORRECT ANSWER Definition: A function that combines distinct year, month, and day elements into a function that will output the date.
Explanation: Input the year, month, and day to get the date.
DAY Function - CORRECT ANSWER Definition: returns the day portion of a date (a number between 1 and 31)
MONTH Funtion - CORRECT ANSWER Definition: Outputs the month based on inputted serial number
YEAR Function - CORRECT ANSWER Definition: Outputs the year based on inputted serial number.
AND Function - CORRECT ANSWER Definition: A function that facilitates the type of analysis where you need to evaluate multiple arguments at once. This function evaluates to true if all arguments (logical_tests) and evaluates to false if one or more arguments are false. Typically embedded in IF statements/functions.
Explanation: input the arguments you would like evaluated to get a TRUE output from the function or a FALSE output from the function.
OR Function - CORRECT ANSWER Definition: Similar to the AND Function which evaluates to true if at least one argument (logical_test) is TRUE.
Explanation: input the arguments you would like evaluated to get a TRUE output from the function or a FALSE output from the function.
HLOOKUP Function - CORRECT ANSWER Definition: A lookup & reference function that searches for a value in top ROW of a table or an array of values, and then returns a value in the same column from a row you specify.
Explanation: Begin by providing a lookup_value in the first argument of the function, then provide the table_array for function, and lastly use the XMATCH function to make the row_index_num dynamic, and lastly lookup/reference function a range lookup argument may be required to be inputted as a FALSE for lookup values that are text or numbers in a non-ascending order.
VLOOKUP Function - CORRECT ANSWER Definition: This functions searches for a value in the leftmost column of a table, and then returns a value om the same row from a column you specify in the table/array.
Explanation: Begin by providing the desired lookup_value, then provide the table array followed by the XMATCH function to make the column index number dynamic, and lastly lookup/reference function a range lookup argument may be required to be inputted as a FALSE for lookup values that are text or numbers in a non-ascending order.
INDEX Function - CORRECT ANSWER Definition: A lookup/reference function that uses the entire array and two other arguments that contain the row number of the desired cell (starting at the top of the array), and then a column number as the last argument (starting at the leftmost column of the array/table).
Explanation: Begin by inputting the array/table to reference from, and then use XMATCH for both the row_num and Col_num arguments to make the function dynamic.
OFFSET Function - CORRECT ANSWER Definition: Another lookup/reference function that uses a pivot point as the first argument and then has two more arguments for the number of rows after the pivot point where the desired cell is, and then an argument for the number of columns after the pivot point in which the desired cell is.
Explanation: Begin by selecting the most optimal pivot point of the array/table and then use the XMATCH function for both the number of rows and columns arguments.
INDIRECT Funvtion - CORRECT ANSWER Definition: This function returns the reference specified by a text string. However, the value of this function is when it is combined with the AND function. You can concatenate to make the SUM function very dynamic.
COLUMNS Function - CORRECT ANSWER Definition: This function tells you the number of columns in an array.
ROWS Function - CORRECT ANSWER Definition: This function tells you the number of rows in an array.
XLOOKUP Function - CORRECT ANSWER Definition: The ultimate lookup function in Excel that is already dynamic and does not break with added columns/rows.
Explanation: Begin by providing the lookup_value for the function in the first argument, and then provide the lookup_array in which the lookup_value is located, and the finally input the return_array where what you are trying to lookup/reference is located.
SUMPRODUCT Function - CORRECT ANSWER Definition: This function multiplies corresponding components in two or more arrays, and returns the sum of those products.
Explanation: Begin by select the first array in the first argument and then the second array in the second argument in order for the function to do the operation.
Extra: Criteria can also be embedded into this function for more specific cases, these are also known as BOOLEANS in which a variety of FALSE and TRUE results will come from the BOOLEAN and cause the operation to be more scenario based.
SUMIFS Function - CORRECT ANSWER Definition: SUMS desired data based on more than one logical test.
Explanation: Begin by providing this function the desired range to find a certain criteria, and then finally provide the sum-range for that criteria.
SUMIF Function - CORRECT ANSWER Definition: SUMS desired data based on a singular logical test.
Explanation: Begin by providing this function the desired range to find a certain criteria, and then finally provide the sum-range for that criteria.
AVERAGEIF(S) Function - CORRECT ANSWER Definition: Identical to SUMIF and SUMIFS functions but just the average operation is used instead.
ABS (Absolute Value) Function - CORRECT ANSWER Definition: This function provides the absolute value of a referenced cell.
CEILING Function - CORRECT ANSWER Definition: This function rounds UP a referenced cell up to the next amount (by the desired significance).
FLOOR Function - CORRECT ANSWER Definition: This function rounds DOWN a referenced cell by the inputted significance.
COMBIN Function - CORRECT ANSWER Definition: A function that provides the number of combinations that exist between both arguments in the function. Perfect formula for the SYD Depreciation method.
Explanation: Begin by providing a number in the first argument and then a second number to make the combination with.
MIN Function - CORRECT ANSWER Definition: A function that returns the smallest number in a specified set of values.
Explanation: Select different numbers to go into the MIN Function and the function will output which ever is the smallest. Commonly used to calculate borrowing capacity based on a 5x of the lesser of last year's EBITDA or the average EBITDA over the last 3 years.
MAX function - CORRECT ANSWER Definition: This function returns the largest number in a specified set of values.
Explanation: Begin by inputting the desired numbers into the arguments and then the function will output which number is the highest. This function is commonly used to prevent a revolving credit facility from dipping below 0 when there is a cash shortfall.
COUNT Function - CORRECT ANSWER Definition: This function returns the number of cells that contain numbers within the list of the arguments. Cells with text are disregarded with this function.
Explanation: Begin by inputting an array of cells that you want this function to output the number of cells that contain numbers.
COUNTA Function - CORRECT ANSWER Definition: This function does the same as the COUNT function but also counts cells with text in them.
Explanation: Same with the count function but text may also be included.
COUNTIF Function - CORRECT ANSWER Definition: This function counts the numbers of occupied cells that satisfy a specific criteria placed into the 2nd argument of the cell.
Explanation: Begin by selecting the array, and the input the criteria. The criteria in this function must be entered in quotes.
PV (Present Value) Function - CORRECT ANSWER Definition: This function provides the present value of a constant stream of cash flows based on the following arguments...
1) Rate: The Annual Interest Rate (Discount Rate)
2) NPER: Total number of payment periods
3) PMT: Payment made during each period
4) FV: Future Value [NOT Required]
5) Type: Indicates when payments are due [0/Omitted means end of period, and 1 means the beginning of the period.
Explanation: Simply fill out all of the arguments to reach the present value.
FV (Future Value) Function - CORRECT ANSWER Definition: This function outputs the future value of of an investment based on constant future cash flows.
SAME ARGUMENTS AS THE PV FUNCTION, except PV has an argument and not FV [not required}
Explanation: Same explanation as the PV function
NPV Function - CORRECT ANSWER Definition: This function provides the net present value of various different cash flows with a certain rate.
XNPV Function - CORRECT ANSWER Definition: This function is the same as the NPV function, but it has a an argument for dates incase the timing of payments/cash flows are uneven.
IRR Function - CORRECT ANSWER Definition: A function that outputs the IRR pf an investment based on the all of cash flows and the initial investment.
XIRR Function - CORRECT ANSWER Definition: Same as the IRR function but it has a value to handle uneven dates in the second argument.
Goal Seek Function - CORRECT ANSWER Definition: This function(often referred to as What-if-Analysis) is a method of solving for a desired output by changing an assumption that drives it. The function essentially uses a trial and error approach to back-solving the problem by plugging in guesses until it arrives at the answer. For example, if the formula for revenue is equal to the number of units sold multiplied by the selling price, Goal Seek can determine how many units have to be sold to reach $1 million of revenue, if the selling price is known. The function is extremely useful for performing sensitivity analysis in financial modeling.
Explanation: Input...
1) Rate: Interest Rate
2) NPER: Total number of payment periods
3) PV: Present Value
4 & 5) FV & Type [Not used most of the time] [Show Less]