Summing up a variable number of the Largest or Smallest values in a range

Here is a range of numbers provided as an example. It could be any unsorted range/list of numbers you're working on:

Your data set is large and for some specific analytical reason you'd like to track the sum of several largest and smallest numbers in your set. The ARRAY formulas come to help and are very efficient in such cases.

Find ADDRESSES of Specific CELL CONTENTS in Excel Workbook

Let's say we are dealing with Excel table several columns wide and hundreds and hundreds, or even thousands of rows long. Just for illustration I'm providing here a small 'fake' table, a fragment of a big one:

Working on such a large table we may need to find addresses of cells containing some specific values, name, date, number, etc., and we need to look for them quickly.

Complex Array formula

An array is a row or column of values, or a combination of them. Array formulas can be very useful in many applications where normal Excel formulas don't work. An array formula is a formula that can perform multiple calculations on one or more items in an array.

Array formulas can return either multiple results, or a single result. We can use array formulas to perform complex tasks, such as:

• create quickly sample datasets
• count some values contained in a range of cells
• sum numbers that meet certain conditions, such as the lowest values in a range
  
• sum every Nth value in a range of cells
• find specific values in a cell or a range of cells
• etc.   
  

The following example shows you how the single-cell array formula can find the last filled cell in a given row and show its value. Here is the formula (all entered in a single cell):