Array in Excel - Office Corner

new dictionary define in office corner combin combination

Software Articles Templates Dictionary Home

Array Formulas

Single and multiply value results

It is a formula working at edited array or a range of data. You have to know that there are 2 types of that formulas, first type that use COUNT, AVERAGE and SUM (formulas that return single value in a single cell) and there is a second type that you can realize that returns results of calculation in two or more cells.

Let's have a closer look at Single Value return Array Formulas.
The first thing you have to know is standard that is used to return single value result. To get this type of value from a range of cells you have to use normal braces like '(' and ')'. The best example is ROW formula. When we have cells range A1 to A100 and we use ROW formula like =ROW(A1:A100)
We will get a single value 1. It is a number of first cell (A1) in selected range.

How about two or more value return formulas.
In this example when we use ROW formula we have to use curly braces '{' and '}'. That formula returns a array of results (1, 2, 3, .., 100). When using array formulas, you typically use a container function such as SUM or COUNT to aggregate the array to a single number result. Using only =ROW{A1:A100} returns and formula error.

Array Formulas Exercises

1. Try to use Average formula on range of cells. Type different values in A1 to A10 and in A11 use =AVERAGE(A1:A10). Average is one of the most used formulas in math, economy and finance. It can help you count you school marks, your month budget or it can help you organize your time when creating a TO-DO list.

2. This time use SUM for cells from B2 to B20. Like in exercise 1 type different value and AT B20 type SUM formula =SUM(B2:B20). Remember you don't have to type from keyboard B2 and B20 it is easier to click with left mouse button first cell (B2) and holding SHIFT button click on the last cell (B20). You are sure you selected right cell range, because when you hold SHIFT button all cells from first clicked to last is selected.

3. Now try MAX and MIN formula. Type different values in cells from A1 to A15 and use =MIN(A1:A15) when it will return you a value edit formula and type =MAX(A1:A15). MAX and MIN formulas are helpful when you create your budget, or when you want to know where you spend your salary.

Check all Beginner's Tips
Learn how to use Excel functions and formulas
in three steps of difficulty >>