Excel Formulas and Functions

XLOOKUP (extended lookup)

function that can perform both vertical and horizontal lookups. It allows for more complex searches and can return results from any column or row, regardless of its position. Syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) Example: =XLOOKUP(101, A2:A10, B2:B10, "Not Found", 0) searches for product ID 101 in column A and returns the corresponding product name from column B. If the ID is not found, it returns "Not Found." The last three parameters in the formula are not mandatory.

You are using Excel's VLOOKUP function to search for employee information. The formula you use is: =VLOOKUP("E00021", A2:M51, 3, FALSE). What does this formula do?

searches FOR "e00021" IN THE FIRST COLUMN OF THE a2 RANGE AND RETURNS THE VALUE FROM THE THIRD COLUMN

VLOOKUP (vertical lookup)

searches for a value in the first column of a range or table and returns a value in the same row from another specified column. Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) Example: In a table where column A contains product IDs and column B contains product names, =VLOOKUP(101, A2:B10, 2, FALSE) searches for product ID 101 in column A and returns the corresponding product name from column B. The last parameter is not mandatory and Excel sets the default to False.

HLOOKUP (horizontal lookup)

searches for a value in the first row of a range or table and returns a value in the same column from another specified row. Syntax: =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) Example: In a table where row 1 contains months and row 2 contains sales data, =HLOOKUP("January", A1:H2, 2, FALSE) searches for January in row 1 and returns the corresponding sales data from row 2. The last parameter is not mandatory and Excel sets the default to False.

True or false, using the OR function in Excel allows you to check if either a performance rating is below 85% or actual sales are lower than the sales target to determine if an employee needs improvement.

true: The OR function returns TRUE if either of the two conditions is met, which is useful for identifying employees needing improvement.

Fabian is working on a project that requires calculating the number of days between two key project milestones. He needs to find out how many days are between January 1, 2024, and December 31, 2024. He decides to use the DATEDIF function for this purpose. Which of the following formulas should Tom use to calculate the number of days between these two dates?

=DATEDIF("2024-01-01", "2024-12-31", "D")

Question 2 Fernanda is preparing a report that needs to dynamically update based on the current date. She wants to use Excel functions to include both the current date and time in her report and calculate a future date that is 3 months from now. Which of the following functions should Fernanda use to achieve these goals? (Select all that apply)

=EDATE(TODAY(),3) =NOW()

Jessica is preparing a financial report and uses the formula =A1 / B1 to calculate ratios. However, she notices that she's getting a #DIV/0! error in some cells. Which of the following formulas can Jessica use to handle this error and display a custom message instead of the default error?

=IFERROR(A1/B1, "Division by zero error")

Zhang is troubleshooting his Excel sheet where he encounters multiple errors in his formulas. He needs to apply functions to identify and manage these errors effectively. Which of the following functions can Mark use to handle and identify errors in his formulas? (Select all that apply.)

=ISERROR(A1/B1) checks if there is any error in the formula, including #DIV/0!, #VALUE!, and others. =IFERROR(A1/B1, "Error") allows Mark to handle errors by displaying a custom message if an error occurs. =ISERR(A1/B1) checks if the result of the formula is any error except #N/A.

You need to calculate the total outstanding debt for patients with the physical therapy treatment plan. In the Excel worksheet, column E contains the treatment plans, and column G contains the corresponding outstanding debt amounts for each patient. Which function should you use?

=SUMIF

Definition of named ranges

A named range is a label assigned to a cell or a group of cells, making it easier to reference them in formulas. For example, you have sales data in the range A1:A10. After renaming this range to "SalesData", Instead of using =SUM(A1:A10), you can use =SUM(SalesData).

You need to customize a project management template in Excel to include team member assignments. Which of the following actions are appropriate for maintaining the template's structure and usability? (Select all that apply.)

Add a new column specifically for team member names

Which functions can be used in Excel to handle multiple conditions in data analysis? (Select all that apply.)

COUNTIFS can handle multiple conditions to count records. SUMIFS can handle multiple conditions to sum values based on criteria.

Mixed cell references

Definition: A mixed cell reference combines both relative and absolute references. You can lock either the column or the row by placing a dollar sign ($) before it or using the keyboard shortcut F4 Example: In cell C1 the formula =$A1+B$1, shows the column A reference is absolute, while the row 1 reference is relative. It also shows the column B reference is relative while the row 1 reference is absolute. When copied to cell D1, the column will stay the same (A), but the row will adjust which results in the formula = $A1+C$1

Relative cell references

Definition: A relative cell reference adjusts based on the position of the formula when copied to another cell. It changes according to the relative position of rows and columns. Example 1: If you enter a formula =A1+B1 in cell C1 and then copy it to cell C2, the formula will automatically adjust to =A2+B2. Note here that the row reference has updated, while the column reference has remained constant.

Absolute cell references

Definition: An absolute cell reference remains constant, regardless of where the formula is copied or moved. It is indicated by a dollar sign ($) before the column letter and row number. Example: In cell C1 the formula =$A$1+$B$1, shows the reference to cell A1 is absolute, so when copied to cell C2, it remains =$A$1+$B$1.

DATEVALUE(date_text)

Definition: Converts a date in text format to a date value. Example: =DATEVALUE("September 15, 2024") returns the serial number of the date.

TIMEVALUE(time_text)

Definition: Converts a time in text format to a time value. Example: =TIMEVALUE("2:30 PM") returns the serial number of the time.

DATE(year, month, day)

Definition: Creates a date value from individual components where the year, month, and day are in separate columns. Example: =DATE(2024,9,15) returns the date September 15, 2024.

TIME(hour, minute, second)

Definition: Creates a time value from individual hour, minute, and second components. Example: =TIME(14,30,0) returns 2:30 PM.

HOUR(), MINUTE(), SECOND()

Definition: Extracts the individual components (hour, minute, or seconds) from a time. Example: =HOUR(14:30:20) returns 14.

YEAR(), MONTH(), DAY()

Definition: Extracts the individual components (year, month, or day) from a date. Example: =YEAR(2024-09-15) returns 2024.

TODAY()

Definition: Returns the current date. Example: If today is September 25, 2024, =TODAY() returns 09/25/2024.

NOW()

Definition: This is a dynamic function, meaning it updates automatically whenever the worksheet is recalculated or reopened. Example: If the current date and time is September 25, 2024, at 2:30 PM, entering =NOW() will display 09/25/2024 14:30 (depending on your system's format).

You are reviewing a formula and want to ensure that the calculations are performed in the correct order according to the BODMAS rule. When evaluating the formula =10 + 6 / 2 * 3 in Excel, which operation is performed first according to BODMAS?

Division

Question 6 True or false, once a named range is created, any formulas that use this named range will automatically update if the size of the range changes, such as when new rows are added.

False

True or false, the AVERAGEIF function in Excel calculates the average of numbers in a range that do not meet a given condition.

False: The AVERAGEIF function calculates the average of numbers that meet a specific condition, not those that do not meet it.

Question 5 True or false, the formula =SUMIF(G:G, "Physical Therapy", E:E) correctly calculates the total outstanding debt (in column G) for patients with the physical therapy treatment plan (in column E).

False: The formula=SUMIF(G:G, "Physical Therapy", E:E) is incorrect. The criteria range should be column E (containing treatment plans) and the sum range should be column G (containing the outstanding debts)

Question 4 Alex is managing a workbook that contains named ranges for different data categories, including SalesData, ExpenseData, and ProfitData. He needs to update the ranges to include new data that has been added to his sheets. Which of the following steps should Alex take to update the named ranges using the Name Manager? (Select all that apply.)

Go to the Formulas tab, click on Name Manager, select the named range, and click edit In the Name Manager, select the named range, click Edit, adjust the cell reference to include new data, and click OK

You want to use Copilot in Excel to generate a formula that retrieves the performance rating of the third employee in a table, searching horizontally. Which function would you use?

HLOOKUP is used to search for values in the top row of a range and return a corresponding value from a specified row.

You are evaluating employee performance to determine bonus eligibility. To qualify for a bonus, employees must meet their sales target and have a performance rating above 100%. Which Excel function combination should you use to check if both conditions are true?

IF and AND

You are analyzing a dataset of 50 investments and need to find both the smallest and largest return on investment (ROI) values in your portfolio. Which Excel functions should you use? (Select all that apply.)

MIN MAX

Question 1 You've been tasked with calculating the fair market value of your percentage ownership in multiple investments. After successfully calculating the percentage ownership using division, which arithmetic operator would you use to calculate the fair market value of your ownership?

Multiplication

You are working on a financial model where you need to consistently apply certain fixed values across multiple formulas in different cells. Which scenarios are best suited for using absolute cell references in Excel? (Select all that apply.)

Referencing a fixed discount percentage across various cells Applying a fixed rate to multiple accounts

Shreya is working on a financial report with multiple sheets, including one for monthly sales data and another for annual summaries. She wants to ensure that her formulas referencing sales data are easy to understand and update if necessary. To achieve this, she decides to use named ranges for the monthly sales data. Which of the following actions should Shreya take to create a named range for the monthly sales data using the Name Box?

Select the cell range, click on the Name Box, type "Monthly Sales" and press Enter

You are using the formula =$A1+B$2 in Excel and plan to copy it across different columns to apply similar calculations. Which part of the reference will remain constant when the formula is copied across columns?

The column reference for A

True or false, the AVERAGEIF function can be used to calculate the average length of stay for patients diagnosed with hypertension by highlighting the column with stay durations.

True

True or false, the formula =(2 + 3) * (4 ^ 2) will first add 2 and 3, then calculate 4 squared, and finally multiply the results.

True: According to BODMAS, operations inside brackets are performed first, then exponents, and finally multiplication.

True or false, the DATEVALUE function can be used to convert text representations of dates into date values that Excel can recognize and use in calculations.

True: DATEVALUE converts text strings that represent dates into date values that Excel can recognize and use in calculations.

Question 6 True or false, the #VALUE! error typically occurs when there is an incorrect type of argument or operand, such as performing mathematical operations on text data.

True: The #VALUE! error occurs when there is a mismatch in data types, such as using text in a mathematical operation.

True or false, when copying the formula =A1 + B$2 from cell C1 to D2, the formula will adjust to =B2 + C$2.

True: The formula =A1 + B$2 will adjust to =B2 + C$2 when copied.

True or false, XLOOKUP can be used to search both vertically and horizontally, and it can return results from any column or row, regardless of its position relative to the lookup column or row.

True: XLOOKUP is versatile and can perform both vertical and horizontal lookups, returning results from any specified column or row.

Use case: HLOOKUP

Use HLOOKUP when your data is organized horizontally, and you need to search for a value in the first row and return data from a specified row within the same column.

VLOOKUP Use case

Use VLOOKUP when you need to search for a value in the first column of a table and retrieve data from another column in the same row.

Use case: XLOOKUP

Use XLOOKUP when you need more flexibility in your search criteria or when VLOOKUP or HLOOKUP cannot handle your data structure. XLOOKUP can search both horizontally and vertically and is useful for more complex data retrieval tasks.

You need to calculate the total value of your portfolio by combining the initial investment cost and the total returns on each investment. Which of the following steps are involved in achieving this calculation? (Select all that apply.)

Use the addition operator (+) to combine the total of the initial investment cost and the total returns. Use Copilot to automatically sum up the total initial investment costs and total returns.

You have created complex Excel formulas and want to make sure they follow the BODMAS rule to achieve accurate results. Which of the following actions can help ensure that your Excel formulas adhere to the BODMAS rule? (Select all that apply.)

Using Excel's formula auditing tools and features Testing the sample data to check formula results

You are working with a dataset that lists employee IDs in the first column and their corresponding names in the second column. You need to find an employee's name based on their ID using a vertical lookup function.

VLOOKUP XLOOKUP

You are evaluating different lookup functions for various data management tasks and need to determine when XLOOKUP offers advantages. In which scenarios would you prefer to use the XLOOKUP function over VLOOKUP or HLOOKUP? (Select all that apply.)

When you need to perform a lookup in a table where the lookup column is not the first column When you require the ability to return a specific value if the lookup value is not found