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:
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:
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:
Most of the time we organize our data in orderly Excel tables making their analysis pretty straightforward. Sometimes, however, we may have to deal with chaotic sets of numbers located all over different ranges/worksheets, like in this somewhat bloated example:
How to count and analyse them? Couple of examples follow.
Excel FUNCTIONS = Predefined formulas used for specific values in a specific order. They operate usually on data entered in range of cells and increase the Excel users productivity. Total number of the functions available in Excel is close to 700.
Some of the most important of them (most frequently used) are: SUM, IF, MIN, MAX, AND, OR, AVERAGE, COUNT, DAYS, VLOOKUP, DATE, ... etc. Examples of functions working with text: UPPER, LOWER, PROPER,... etc.
Excel FORMULAS = Expressions operating on values in a range of cells and operators. Can utilize several Excel functions and work with both numeric and textual data. ARRAY formulas are a special type of formulas, and can be very complex.
Here is the list and description of some of the most useful shortcuts used in conjunction with Functions and Formulas. I hope you'll find them helpful.
