Home Archives for Array Formulas in Excel
August 3, 2012 1 Comment

How to Use an Array Function in Excel – Learn How to Use the Frequency Function

Key Points for Frequency FunctionIn my experience, most of the people who I train have never heard of – let alone use – Array Formulas and Functions in Excel. However, once I demonstrate how they work, they become “hooked” and want to learn more about these “Magic Formulas!” In this tutorial, I demonstrate, “step-by-step” how to use the FREQUENCY() Function in Excel.

Previous Tutorial on The Company Rocks Website

If you watched my previous tutorial, I used the same data set and a Pivot Table to Group these 500 plus Sample Records by Age Bracket. Using a Pivot Table, you can quickly “group”  ages into brackets – without writing a single formula! However, on the down side, you must use a consistent “step-value” for these groupings – in this case by 10 years. When you need more flexibility in organizing your Age Brackets, use the Frequency Function in Excel.

How to Use the FREQUENCY() Function in Excel

  1. Create your “Bin Array.” In this example, enter – in ascending order – the ages that you want to find the “frequency of occurrences” between
  2. Select the “Array of Cells” that will contain your FREQUENCY() Function results.
  3. Write the FREQUENCY() Function –  1st argument is the  “Data_array”. In this case, I created a Named Cell Range called “Age.” this is mu “Best Practice Tip!”
  4. The 2nd argument is the “Bin_array.” Choose the vertical range of cells that you created in step 1 of this list. Make sure that the “size” of this Array matches the “size” of your Array Formula Selection.
  5. Complete the Array Function with the keyboard combination of “Ctrl + Shift + Enter.” This “CSE” combination is essential when entering all Array Formulas and Functions!

My Secure Shopping Website

I invite you to visit my secure shopping website – http://shop.thecompanyrocks.com – where you can preview all of the training resources that I offer. If you want to learn more about formulas and function, I offer a great resource: “The 50 Best Tips for Excel 2007.” Regardless of the version of Excel that you are currently using, you will pick up many great tips to improve your Excel skills!

Watch Tutorial in High Definition

Follow this link to watch this Excel Tutorial in High Definition on my YouTube Channel – DannyRocksExcels.

View My Tutorial Now on YouTube

 

 

Link to this post!
Filed Under: Excel Functions, Excel Training, YouTube Videos by Danny Rocks Tagged: , , , , , , ,
May 26, 2011 Leave a Comment

Two Ways to Turn Excel Data On Its Side – Transpose the Data!

Transpose Excel Data

Transpose Excel Data

How many times have you spent an hour or more creating an Excel worksheet only to discover – or be told – that you have the wrong structure? The values that you entered run vertically down in Column A. And  now, you need to have your values run horizontally across in Row 1.

Well, don’t despair! And, most important, do NOT retype your entries on another worksheet. Rather,watch this video to see how to “Turn Excel Data On Its Side”:

  1. Select Transpose from the Paste Select dialog box or the right-click shortcut menu
  2. Use the =TRANSPOSE() Function which is an Array Function

Watch as I demonstrate the advantages of each approach – they are different! If you are “curious” about how an Array Function works, the TRANSPOSE Function is a good one to practice with.

Click this link to watch this video in High Definition on YouTube.

Now Using Excel 2010

I filmed this video using Excel 2010. I will use Excel 2010 for all future video lessons unless there is a particular need to use another version to illustrate a point.

Download Workbook Used in This Video

Click on this link to download the Excel Workbook that I used in this video tutorial. It is stored on my “SkyDrive” at www.office.live.com

Get my best-selling DVD-ROM, “The 50 Best Tips for Excel 2007” for only $29.97!

Link to this post!
Filed Under: Download Files from Video Lessons, Excel Functions, Excel Training, Tips and Time Savers, YouTube Videos by Danny Rocks Tagged: , , , ,
February 7, 2011 Leave a Comment

Working With Array Formulas in Excel

There are at least two benefits to using an Array Formula in Excel:

  1. They will speed up creating your formulas by, in many cases, eliminating intermediate formulas.
  2. They will add a level of protection to your formulas because you cannot edit the individual cells in an Array Formula. You must edit “all” of the cells.
Array Formulas
Array Formulas

In this lesson, I first demonstrate how to select and use arrays with the SUMPRODUCT() Function in Excel. SUMPRODUCT is a very handy function to use when you want to first multiply (PRODUCT) the cells in two or more arrays and then total (SUM) the results.

 
Next, I demonstrate two ways to create Array Formulas. Notice that in the second example that it is important that you remember to select ALL of the cells that will contain the results of the Array Formula. It is vital that all arrays are of Equal Size (Same number of cells in the same dimension – horizontal and vertical).
 

Entering Array Formulas

Use the keyboard combination of Ctrl + Shift + Enter when entering an Array Formula. Do not press the Enter key alone – this will result in an Error for your formula! Some people refer to Array Formulas as CSE Formulas to remember the Ctrl + Shift + Enter combination. Notice, in the Formula Bar,  that Excel automatically adds { braces } around the Array Formula. Do not enter these manually.

Editing Array Formulas

You gain a level of protection for your formulas when you use Array Formulas because you cannot select a single formula cell to edit it. You must select all of the cells that contain the Array Formula before you can edit it – or delete it. If you don’t select all of the cells first, you receive an Error message in a pop-up window. Remember to press the “CSE keyboard combination” when you complete your edit of the Array Formula.

Learning to use Array Formulas is an essential skill to acquire if you want to master Excel. I guarantee that you will be able to write more accurate formulas when you use arrays.

Watch this Video on YouTube

Click this link to watch this Excel Video in High Definition, Full Screen Mode on my YouTube Channel – DannyRocksExcels

Learn how to “Master Excel in Minutes – Not Months!”

Working with Array Formulas in Excel [ 7:45 ] Play Now | Play in Popup | Download (1004)
Link to this post!
Filed Under: Excel Formulas, Excel Functions, Excel Training, Podcast Tips & Timesavers Tagged: , , , , , ,
October 27, 2010 Leave a Comment

How to Lookup a Value Using Multiple Columns in Excel

There are times when the VLOOKUP Function will not return the value that you want to lookup. Suppose, for example, that you need to lookup the “Title” for an employee where the First Name and Last Name are in separate columns. A VLOOKUP() will not help you in this example. Let me show you how to accomplish this.

Key Concepts Covered

  • Use the “&” (Ampersand) to Join two cells that contain Text Values
  • Use the Index() and Match() Functions for the Lookup
  • Create an Array Formula to perform the Lookup – Use “Ctrl+Shift+Enter” with Array Formulas
  • Create and use “Named Cell Ranges” in your formulas to make it easier to enter and understand the formula

Be sure to check out all of the other Excel Video Lessons on Lookups. Click here to go to the Archive of Lookup Function Videos.

I invite you to subscribe to my “Danny Rocks Tips and Timesavers”Video Podcast on iTunes – it is free!

You can learn how to “Master Excel in Minutes!”

How to Lookup a Value Using Multiple Columns [ 6:00 ] Play Now | Play in Popup | Download (1001)
Link to this post!
Filed Under: Excel Functions, Excel Training, Lookup Functions in Excel, Podcast Tips & Timesavers Tagged: , , , , ,
September 17, 2010 Leave a Comment

Two Ways to Create a Frequency Distribution in Excel

What is a “Frequency Distribution?” A good example is to see how frequently daily sales are “under $75.00” or how often they range “between $75.00 and $150.00.” Once you know how frequently a result occurs, you can better focus your attention on that particular segment of your business.

In this short 5 minute and 45 second Excel Training Video Lesson, I demonstrate two approaches to creating a Frequency Distribution Report:

  1. Use the =FREQUENCY() Function – this is an ARRAY Function.
  2. In a Pivot Table, Group the Row Labels to produce a Frequency Distribution.

An ARRAY Function has two “Got’cha steps:”

  1. Select all of the cells that will contain the results before your enter your Array Formula.
  2. Use the Ctrl + Shift + Enter keyboard combination to enter your Array Formula.

You can watch this video here on my website, you can download it via an RSS Feed or you can watch it as a Podcast at the iTunes Store.

Learn how to “Master Excel in Minutes – Not Months!”

Two Ways to Create a Frequency Distribution Report in Excel [ 5:45 ] Play Now | Play in Popup | Download (818)
Link to this post!
Filed Under: Excel Formulas, Excel Functions, Excel Training, Pivot Tables, Podcast Tips & Timesavers Tagged: , , , , , ,