Use SUM, AVERAGE, and MAX

We created an array formula that returned a single value in the first video in this course, Array formulas.

But an array formula can also return multiple values.

For example, to calculate a Subtotal for each stock, select the cells you want to contain the Subtotals, type an = sign, select the cells in the Shares row, type an * to multiply, select the cells in the Price row, and for an array formula, press Ctrl+Shift+Enter, not just Enter.

In each cell, the array formula multiplies a cell in the Shares row with the respective cell in the Price row and returns the Subtotal.

This worksheet contains Sales for a number of years. I want to know the average annual increase in Sales across all years.

To calculate the increase in sales from just 2013 to 2014, type an = sign, select the cell with the Sales for the subsequent year (2014), type a - sign, select the cell with the Sales for the previous year (2013), and press Enter.

This returns $8868.88, the sales increase from 2013 to 2014.

We could do this for every year, and then average the results, to get the average annual increase in Sales across all years.

That is the equation in F9. If we had data for many more years, or we had month to month data, this equation would be a nightmare to create and maintain.

Using an array formula, we type = sign, AVERAGE, opening parenthesis, select the Sales for subsequent years (2014 through 2018), type a - sign, select the Sales for the previous years, (2013 through 2017), type a closing parenthesis, and press Ctrl+Shift+Enter.

The array formula calculates the increase in Sales for each year, averages those values, and returns $11464.32, the average annual increase in Sales.

This worksheet contains the opening and closing prices for a stock.

I want to know what the biggest gain was in a day. To calculate the difference between the opening and closing price for a specific day, you type, = sign, select or type the cell with the closing price for the day, type a - sign to subtract, select the cell with the opening price, and press Enter.

With just a few rows of data, you could copy the formula down the column and pick out the biggest gain.

With a lot of rows, this would be more time consuming, and if the data changed, the result would be hard to use in another formula.

Using an array formula, I type, = sign, MAX (the MAX function returns the biggest, or maximum, value), opening parenthesis, select or type the cells with the closing values, type the - sign, select the cells with the opening values, and press Ctrl+Shift+Enter.

The array formula takes the closing price for each day, subtracts the opening price, and then, uses the MAX function to return the maximum value, 98 cents.

Up next: Edit and delete array formulas.