Wall Street Prep; Excel Crash Course Formulas Explained Questions with 100% Correct Solutions

Average Function - 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 - 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 - Definition: IF function with various IF functions within each other. IFS Statement/Function - 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 - 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 - A function that concatenates cells with a text string. EOMONTH Function - 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 - 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 - 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 - 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 - 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 - 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 - Definition: returns the day portion of a date (a number between 1 and 31) MONTH Funtion - Definition: Outputs the month based on inputted serial number YEAR Function - Definition: Outputs the year based on inputted serial number. AND Function - 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 - 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 - 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 - 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 - 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 - 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.