[ad_1]
This article will show you how to find the most improved sales in four areas, really 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 rather than single values as with a normal formula. Let’s see how this makes life easier with an example.
I have a set of sales data, for four regions and their respective sales people, North, South, East and West as two months of sales data November 2014 and December 2014. The data should be set up as below-
Column B- Sales Person
James Great
Susan Breen
Max Jones
Stephen Parkin
Column C- Sales Area
North
South
East
West
Column D- November 2014 Sales
€ 13,415.00
€ 14,914.00
€ 10,338.00
€ 10,224.00
Column E – December 2014 Sales
€ 14,915.00
€ 14,414.00
€ 12,838.00
€ 13,624.00
Column F is Sales Difference where we store our first formula
Let’s take a look at which region had the most improved sales from November to December 2014.
You data set up would to be.
If we choose to use normal formula to find the most improved sales area we would need to follow the following steps-
1. Calculate the improvement (if any) of each of the sales area in a helper column to the right of the December 2014 sales figures in column F. We then subtract December 2014 sales from the November 2014 sales figures.
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 quicker way by using an array formula.
If we were to use an array formula, we can use Excel to perform the multiple calculations in just one cell for us. We do not need to store the range of values in Column E as Excel can store this in it’s memory. This is called an array constant.
So let’s repeat the process by
1. Starting with the MAX function
=MAX(
We then want to replace the E3 with E3:E6 and then replace D3 with D3:D6 with a result of the formula looking like this
=MAX(E8:E11-D8:D11)
2. To indicate to Excel this is an array formula we need to surround it with curly braces or brackets { }.
We do this by instead of hitting return after the formula is typed hold down CTRL+SHIFT+ENTER.
I usually ensure my cursor is in the formula, then hold down CTRL+SHIRT then hit ENTER until you get the hang of it!.
So the MAX function is using the array constant as an argument and results is still € 3,400.00.
Extra Bonus Excel Tip.
When you are working with array formulas, you can take a look at the array constants yourself. Using the same example.
1. Select the cell referencing that relate to your array in your formula.
2. Hit F9, you now see what Excel sees. I.e all of the values that make up the array
3. Elements in a vertical array constant are separated by semi colons and horizontal ones are separated by commas, in our example it is of course a vertical array constant so are separated by semi colons.
[ad_2]
