Enter A Formula In Cell D8 That Calculates The Difference How To Calculate The Most Improved Sales Area With An Array Formula In Excel

You are searching about Enter A Formula In Cell D8 That Calculates The Difference, today we will share with you article about Enter A Formula In Cell D8 That Calculates The Difference was compiled and edited by our team from many sources on the internet. Hope this article on the topic Enter A Formula In Cell D8 That Calculates The Difference is useful to you.

How To Calculate The Most Improved Sales Area With An Array Formula In Excel

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.

Video about Enter A Formula In Cell D8 That Calculates The Difference

You can see more content about Enter A Formula In Cell D8 That Calculates The Difference on our youtube channel: Click Here

Question about Enter A Formula In Cell D8 That Calculates The Difference

If you have any questions about Enter A Formula In Cell D8 That Calculates The Difference, please let us know, all your questions or suggestions will help us improve in the following articles!

The article Enter A Formula In Cell D8 That Calculates The Difference was compiled by me and my team from many sources. If you find the article Enter A Formula In Cell D8 That Calculates The Difference helpful to you, please support the team Like or Share!

Rate Articles Enter A Formula In Cell D8 That Calculates The Difference

Rate: 4-5 stars
Ratings: 3413
Views: 21369735

Search keywords Enter A Formula In Cell D8 That Calculates The Difference

Enter A Formula In Cell D8 That Calculates The Difference
way Enter A Formula In Cell D8 That Calculates The Difference
tutorial Enter A Formula In Cell D8 That Calculates The Difference
Enter A Formula In Cell D8 That Calculates The Difference free
#Calculate #Improved #Sales #Area #Array #Formula #Excel

Source: https://ezinearticles.com/?How-To-Calculate-The-Most-Improved-Sales-Area-With-An-Array-Formula-In-Excel&id=8879441