This article will show you how to find the most perfect sales in four areas, very quickly, using the array formula in Excel.
Array formulas are a really powerful tool that can be used in the Excel toolbar. An array formula works with a series of data values or with an array of values, rather than with single values, as with a regular formula. Let's see how this makes life easier with an example.
I have a set of sales data for the four regions and their respective sellers, North, South, East and West as two months of sales data in November 2014 and December 2014. Data should be configured as below
Column B - seller
James the Great
Susan brin
Max jones
Stephen Parkin
Column C - Sales Area
north
south
East
west
Column D - November 2014
€ 13,415.00
€ 14,914.00
€ 10,338.00
€ 10,224,00
Column E - December 2014.
€ 14,915.00
€ 14,414.00
€ 12,838.00
€ 13,624.00
Column F - Sales Difference where we keep our first formula
Let's see which region had the most improved sales from November to December 2014.
You have configured the data.
If we want to use the usual formula to find the most perfect trading area, we will need to follow these steps:
1. Calculate the improvement (if any) of each of the retail space in the assistant column to the right of the December 2014 sales figures in column F. Then we deduct the sales in December 2014 from the sales in November 2014.
2. Then we need to use the MAX function to find the maximum value and, therefore, the most improved sales. Formula syntax
= MAX (F8: F11)
We can see that Stephen Parkin has the most improved sales for the Western Region. But it took us two formulas, there is a quick way to use an array formula.
If we used an array formula, we can use Excel to perform multiple calculations in just one cell for us. We do not need to save the range of values in column E, because Excel can store it in it. This is called an array constant.
Therefore, we repeat the process
1. Starting with the MAX function
= MAX (
Then we want to replace E3 with E3: E6, and then replace D3 with D3: D6 with the result of a formula like this
= MAX (E8: E11-D8: D11)
2. To point to Excel, this is an array formula, we need to surround it with curly braces or {}.
We do this instead of clicking on the return after the formula is typed, while holding CTRL + SHIFT + ENTER.
Usually I guarantee that my cursor is in the formula, then press CTRL + SHIRT, then press ENTER until you get it!
Thus, the MAX function uses an array constant as an argument, and the results are still € 3,400.00.
Extra Excel Bonus Tip.
When you work with an array of formulas, you can take a look at the array constants. Using the same example.
1. Select the cell reference that belongs to your array in your formula.
2. Press F9, now you will see what Excel sees. Those. all values that make up the array
3. The elements in the constant of a vertical array are divided by semicolons, and the horizontal ones are separated by commas, in our example it is, of course, the constant of the vertical array, therefore, semicolons are separated.
