In MS Excel, given sales data in column A, which formula properly extracts the second highest unique value?
Show answer Hide answer
Answer: C. =INDEX(SORT(UNIQUE(A:A), , -1), 2)
Explanation
The UNIQUE function extracts distinct values from column A by removing all duplicate entries. The SORT function organizes those unique values in descending order when specified with minus one. The INDEX function retrieves the specific value residing at the second position of the sorted array. Modern spreadsheet software supports dynamic array formulas that process multiple values simultaneously. Nested functions execute inner calculations first and pass their results outward to container functions. Data analysis often requires isolating specific ranked items from large datasets containing repeated values.
Financial analysts use ranking formulas to identify second-place sales figures or runner-up performance metrics. Dynamic formulas update automatically when new data entries are added to the monitored column. Mastering advanced lookup and reference functions is essential for building robust automated reporting models. Option A returns the second largest value overall but fails to account for duplicate numbers. Option B sorts unique values in ascending order by default, returning the second smallest value. Option D extracts the second smallest unique value instead of finding the second highest value.
- Subject
- Computer Science
- Category
- Computer Science
- Topic
- —
- Difficulty
- medium
Related questions
Q1. While “Justify” aligns both margins in MS Word, how does the Justify alignment function in MS Excel?
Show answer Hide answer
Answer: B. Text in Rows
In Microsoft Excel, the Justify alignment distributes text evenly across multiple rows within selection. Unlike word processors, spreadsheet cells handle text wrapping differently across rows rather than margins. Selecti...
Q2. How do you navigate to the subsequent sheet in Ms Excel:
Show answer Hide answer
Answer: A. Ctrl + Page Down
Pressing Ctrl + Page Down navigates directly to the subsequent worksheet tab in an Excel workbook. This keyboard shortcut speeds up workbook navigation without requiring manual mouse clicks on tiny tabs. It allows users...
Q3. How do you move to the adjacent cell on the right within a row in Ms Excel:
Show answer Hide answer
Answer: A. Tab
Pressing the Tab key immediately shifts the active cell boundary to the right column. Keyboard navigation in spreadsheets allows rapid data entry without constantly reaching for the mouse. The Tab key acts as the standar...
Q4. What are the grid squares formed by rows and columns in Excel named?
Show answer Hide answer
Answer: A. Cells
The intersecting points of vertical columns and horizontal rows create individual rectangular boxes called cells. Each cell serves as an independent container capable of holding text, numbers, or mathematical formulas. S...
Q5. The greater-than symbol represents what kind of operation ________ ?
Show answer Hide answer
Answer: B. Logical
The greater-than symbol is a comparison operator used to perform logical evaluations between values. It evaluates whether the left-hand operand has a numerically larger value than the right-hand operand. The result of th...
Q6. What does the filter feature accomplish in Excel?
Show answer Hide answer
Answer: C. To temporarily hide certain data based on specific criteria
Filtering evaluates dataset rows against user-defined criteria and conceals those that fail to match. Hidden rows remain intact within the underlying worksheet structure while the view is restricted. Clearing the active...