Array formulas in Excel - Tutorials and examples

An array formula is a formula that can perform multiple calculations of one or more items in an array. Array formulas can return multiple values ​​or a single result. Today Dexterity Software will introduce readers to some common array formulas that are used often.

Array formulas in Excel - Tutorials and examples Picture 1

Example of a multi-cell array formula

You have a detailed list of quantity and unit prices of some items sold in the month:

Array formulas in Excel - Tutorials and examples Picture 2

To calculate the total sales, you must calculate the sales of each item, then add up.

Array formulas in Excel - Tutorials and examples Picture 3

Instead of the above, replace it with the above = SUM (C4: C13 * D4: D13) and press Ctrl + Shif + Enter .

Array formulas in Excel - Tutorials and examples Picture 4

Note: For an array formula, you must use Ctrl + Shift + Enter to end the formula otherwise it will produce an error.

Array formulas in Excel - Tutorials and examples Picture 5

Example of array formulas to create constants

You create the array constant by entering the list of items, then enclosing the list in curly braces. For example, you need to enter the list of Apple, Lemon, Orange. You choose cell A3: C3. In the formula bar, type = {"Apple", "Lemon", "Orange"}. Then press Ctrl + Shif + Enter .

Array formulas in Excel - Tutorials and examples Picture 6

And this is the result:

Array formulas in Excel - Tutorials and examples Picture 7

Some other examples use array formulas

In addition to the SUM function example as above, you can use array formulas with other functions like Left, Right, Mid, Max, Min, If .

LEFT, RIGHT, MID and array formulas

Consider the following example for the data array:

Array formulas in Excel - Tutorials and examples Picture 8

The MAX function uses an array of data

To find the maximum value of AB in the example below:

Array formulas in Excel - Tutorials and examples Picture 9

You must perform two calculations, first you calculate the difference of points A and B, then find the maximum value.

Array formulas in Excel - Tutorials and examples Picture 10

Instead of having to do the above two steps, you use the array formula = MAX (B4: B12-C4: C12) and press Ctrl + Shiff + Enter .

Array formulas in Excel - Tutorials and examples Picture 11

Above Software Tips showed you how to use array formulas in Excel. Good luck!

4 ★ | 1 Vote

May be interested

  • Array (Array) in C #Array (Array) in C #
    an array stores a set of fixed-size elements in the same type. an array is used to store a data set, but it is often more useful to think of an array as a set of variables of the same type stored in adjacent memory locations.
  • MS Excel 2003 - Lesson 5: Excel formulas and functionsMS Excel 2003 - Lesson 5: Excel formulas and functions
    formulas in excel are programs that perform calculations on data tables. these formulas perform very precise calculations such as addition, multiplication, or comparison of values ​​in a worksheet.
  • Array (Array) in C / C ++Array (Array) in C / C ++
    c / c ++ programming language provides data structures called arrays, stored in a set of data of the same type with fixed length. an array is used to store data sets, but it is useful if you think of an array of variables with the same type.
  • How to display formulas and print formulas in ExcelHow to display formulas and print formulas in Excel
    display formulas and print formulas in excel so you can see the formula directly in the data cell, not see the results anymore. to be able to display and print the formula so please follow the instructions below of tipsmake.com.
  • How to Copy Formulas in ExcelHow to Copy Formulas in Excel
    the excel program has a feature that makes it easy for users to copy formulas for entire rows or columns, but you don't always get the results you want. when you receive results that are not as expected or encounter #ref and /div0 errors, you will definitely feel unhappy. don't worry, though - you don't need to edit every cell in a 5,000-line spreadsheet. this article will provide some simple ways to help you copy formulas to other cells.
  • A complete guide to Excel 2016 (Part 14): Create complex formulasA complete guide to Excel 2016 (Part 14): Create complex formulas
    after learning about the recipe in excel 2016, tipsmake.com continues to guide you how to create more complex formulas. invite you to consult!
  • How to lock formulas in ExcelHow to lock formulas in Excel
    when sharing an excel file, you don't want other people to edit the formulas, see the formulas, then lock the formulas again.
  • Array (Array) in JavaScriptArray (Array) in JavaScript
    array object - array helps you store multiple values ​​in a single variable. it stores a set of fixed-size ranges of elements in the same type (type). an array is used to store a data set, but it is often more useful to think of an array as a collection of variables in the same type.
  • Introduction to 2D Array - 2-dimensional array in JavaScriptIntroduction to 2D Array - 2-dimensional array in JavaScript
    in the following article, we will introduce and show you some basic operations to create and access 2-dimensional arrays - 2d array in javascript. essentially, a 2-dimensional array is a concept of a matrix of matrices - a matrix, used to store information. each 1 element contains 2 separate indexes: row (y) - column and column (x) - column.
  • Basic functions in Excel, calculation formulas and illustrative examplesBasic functions in Excel, calculation formulas and illustrative examples
    if you are new to spreadsheets, basic functions in excel such as sum, min, max, if will help you solve your work quickly and effectively. let's learn with free download now.