Excel Quiz 59

Cell A2 contains a project start date. You need a formula to calculate the exact date that is precisely 6 months before the start date.

Which function should you use?

You have a single column of 12 months of data in cells A2:A13. You want to wrap this data into a grid containing 4 rows and 3 columns.

Which modern dynamic array function correctly reshapes this data?

When writing a hard-coded 2D array constant in an Excel formula (for example, {1,2;3,4}), what does the semicolon (;) represent?

You are creating a custom named function to calculate a 10% discount on a given variable called "price".

Which is the correct syntax for writing this LAMBDA function before assigning it to a name?

You want to apply Data Validation to cell A2 so that users can only enter a future date (a date strictly greater than today).

Which custom formula should you put in the Data Validation rule?

You are calculating an array formula =A2:A10 / B2:B10. Some cells in column B contain a zero, which will result in #DIV/0! errors.

What is the most concise way to return the text "Error" instead of #DIV/0! for the affected rows while calculating the rest normally?

You have a large dataset spanning from A1 to Z100. You want to extract and display only columns 2, 5, and 10 as a dynamic array.

Which modern Excel function allows you to do this easily?

You have a table with Employee Names in rows (A2:A10) and Months in columns (B1:M1). You need a formula to find the sales figure for "John" in "March".

Which formula performs this two-way lookup correctly?

You need to combine text strings from cells A2 to A10, separated by a comma and a space (", "), but you must completely ignore any blank cells in the range so you don't get extra commas.

Which formula accomplishes this most efficiently?

You want to count how many employees in the range A2:A50 have a last name ending with the letters "son" (e.g., Johnson, Anderson).

Which criteria string should you use inside your COUNTIF function?