How to calculate the most improved sales area with an array formula in Excel
Legal LawThis article will show you how to find the most improved sales in four areas, very quickly by using an array formula in Excel.
Array formulas are a really powerful tool to have in your Excel toolbox. An array formula works with a series of data values, or an array of values instead of single values as with a normal formula. Let’s see how this makes life easier with an example.
I have a sales data set, for four regions and their respective salespeople, North, South, East, and West as two months of sales data November 2014 and December 2014. The data needs to be set up as follows:
Column B – Seller
great james
Susan Breen
max jones
stephen parkin
Column C – Sales Area
North
South
This
West
Column D- Sales November 2014
€13,415.00
€14,914.00
€10,338.00
€10,224.00
Column E – Sales December 2014
€14,915.00
€14,414.00
€12,838.00
€13,624.00
Column F is the sales difference where we store our first formula
Let’s take a look at which region had the biggest improvement in sales from November to December 2014.
Your data settings would be.
If we choose to use the normal formula to find the most improved sales area, we would have to follow the steps below:
1. Calculate the improvement (if any) for each sales area in an auxiliary column to the right of the December 2014 sales figures in column F. We then subtract the December 2014 sales from the sales figures for November 2014.
2. We then need to use the MAX function to find the maximum value and therefore the most improved sales. The formula syntax is
=MAX(F8:F11)
We can see that Stephen Parkin has the most improved sales for the West area. But that took us two formulas, there is a faster way using an array formula.
If we were to use an array formula, we can use Excel to do the multiple calculations in a single cell for us. We don’t need to store the range of values in Column E as Excel can store this in memory. This is called an array constant.
So let’s repeat the process for
1. Getting started with the MAX function
=MAX(
We then want to replace E3 with E3:E6 and then replace D3 with D3:D6 with a formula result that looks like this
=MAX(E8:E11-D8:D11)
2. To tell Excel that it is an array formula, we must surround it with braces or brackets { }.
To do this, instead of pressing Enter after typing the formula, hold down CTRL+SHIFT+ENTER.
I usually make sure my cursor is in the formula, then hold CTRL + SHIRT and then press ENTER until I get the hang of it.
So the MAX function is using the array constant as an argument and the results are still $3,400.00.
Extra Excel Tip.
When you’re working with array formulas, you can take a look at array constants yourself. Using the same example.
1. Select the cell reference that relates to your array in your formula.
2. Press F9, now you see what Excel sees. That is, all the values that make up the array
3. Elements in a vertical array constant are separated by semicolons and horizontal elements are separated by commas, in our example of course it is a vertical array constant so they are separated by semicolons.