HOSMERIT
WALL STREET PREP; EXCEL CRASH COURSE
FORMULAS EXPLAINED QUESTIONS WITH CORRECT
DETAILED ANSWERS 2025.
Average Function - ANSWER-Definition: An Excel function that adds a group of values,
and then divides the result by the number of values in the group.
IF Statement Function - 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 - ANSWER-Definition: IF function with various IF
functions within each other.
IFS Statement/Function - 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 - 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 - ANSWER-A function that concatenates cells with a text string.
EOMONTH Function - 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 - 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 - 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 - 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.
INDIRECT Funvtion - 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 - ANSWER-Definition: This function tells you the number of
columns in an array.
ROWS Function - ANSWER-Definition: This function tells you the number of rows in an
array.
YEARFRAC Function - 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.
VLOOKUP Function - 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 - 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).
WALL STREET PREP; EXCEL CRASH COURSE
FORMULAS EXPLAINED QUESTIONS WITH CORRECT
DETAILED ANSWERS 2025.
Average Function - ANSWER-Definition: An Excel function that adds a group of values,
and then divides the result by the number of values in the group.
IF Statement Function - 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 - ANSWER-Definition: IF function with various IF
functions within each other.
IFS Statement/Function - 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 - 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 - ANSWER-A function that concatenates cells with a text string.
EOMONTH Function - 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 - 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 - 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 - 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.
INDIRECT Funvtion - 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 - ANSWER-Definition: This function tells you the number of
columns in an array.
ROWS Function - ANSWER-Definition: This function tells you the number of rows in an
array.
YEARFRAC Function - 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.
VLOOKUP Function - 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 - 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).
