30 November, 2021

How to count any characters and digits in a text string using ARRAY formula

Let's say you need to find out how many characters, like e.g. a, or b, or w, or any other alphabetic character, or even digits, are there in a text string subject to your analysis.

Here is an example of a string located in cell A2:

Security guards at the hotel make sure guests remain in isolation, while police outside ensure no one enters the premises.

I want to count all characters (i.e. determine their frequency), from a to z (and A to Z) present within that string. This snip shows how easily it can be done using the ARRAY formula presented here:

24 November, 2021

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.

22 November, 2021

SUMMING UP selectively based on references - using ARRAY FORMULAS

Some business sells some products, e.g. vegetables, to various countries/ merchants. Records of sales are kept in Excel table, like in this simple example:

14 November, 2021

How to retrieve some of the LONGEST/SHORTEST TEXT entries from Excel list

Suppose you want to find the longest or shortest entry from a long list of text strings. Or, maybe you need to retrieve the 2nd, or 3rd or 4th longest/shortest entry from the list. How to do it?

Let's start with this small fragment of a big table, for example:

The first column of this table contains the subject list of arbitrary textual entries. All other columns are provided for explanatory purpose only.

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:

11 November, 2021

Array formulas summarize tables with multiple conditions

Let's assume that you are using Excel table for recording sales of some products by several agents. From time time you need to check how your business is doing.

The table is getting larger and larger. I set its size arbitrarily to 1000 rows, but this is up to the user needs. Here is just its small fragment:

08 November, 2021

CELLS: Blank, Empty, Space, Zero

Back to basics... 

What's the meaning of all these basic terms in Excel?

BLANK cell: empty or not? Not quite, may contain "" (zero length text string)

EMPTY cell: nothing is there (no text string, no zero); always evaluates to zero

Cell with SPACE: contains 'space' character (=" ")

Do you know that: 

02 November, 2021

VLOOKUP without limits (case-sensitive): using IF+ INDIRECT+MATCH+EXACT+INDEX functions

Here is a small data table, as an example of Excel table, created for the purpose of this exercise:

What if you are working with a real very big table, let's say 20 columns and 20000 rows, and want to retrieve quickly some specific information from such table, from any row and any column. The following table provides some examples of lookups - based on my small table - you'd might want to do: