Showing posts with label max. Show all posts
Showing posts with label max. Show all posts

14 November, 2021

How to find some LARGEST or SMALLEST numbers in a range

There are situations when we need to find out what is the highest or the lowest value in your table or any array of numbers. And sometimes we may need to determine what is the second or third highest or lowest value in a given range. In all such cases ARRAY formulas are very helpful, so I'm providing some examples of using them.

Let's look first at finding the highest/lowest values in an array/table of numbers. Here is a small sample range of numbers:

26 October, 2021

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.

10 July, 2021

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):