Count (Dracula?)

No, we are not talking about the infamous Count Dracula this week, but since Halloween is next Monday, we most certainly are going to discuss the varied and highly utile Count functions (and have a little fun with Dracula along the way…)!

There are several major types of Count Functions. In summary, they are:

1. COUNT: Simply counts the number of cells in a range that contain a number
2. COUNTA: Counts the number of non-blank cells in a range (including those with text)
3. COUNTBLANK: Counts the number of empty cells
4. COUNTIF: Flexible counting function (this can be a little tricky, so we will explore with some examples)

The COUNTIF function is a Wickedly Useful, and sometimes Tricky (Hey, next week is Halloween), method to derive valuable information from your data, so we are going to look at some examples (I think you will find them a real Treat!)

The reason this function can be challenging to some users is because of the prevalent use of Quotation Marks within the formulas. The following examples will demonstrate how it works (Note: In each case, the formula uses a range named Data):

Count the number of cells that contain the first name of Dracula - "Vlad" (BTW, not case sensitive):
=COUNTIF(Data,"Vlad")

Count the number of cells that contain the words "Vlad" and “Igor”:
=COUNTIF(Data,"Vlad") + COUNTIF(Data,"Igor")

Count the number of cells containing Any Text (ignoring the numbers):
=COUNTIF(Data,"*")

Count the number of 3-letter words:
=COUNTIF(Data,"???")

Count the number of cells containing text that begins with the letter "V":
=COUNTIF(Data, “V*”)

Count the number of cells that contain a value Greater Than Or Equal to 13:
=COUNTIF(Data,">=13")

Count the number of cells that contain a value from 13 to 33:
=COUNTIF(Data,">=13")-COUNTIF(data,">33")

With a bit of ingenuity, COUNTIF can indeed be a Treat to use. So, this Halloween, don’t forget the Count!  (I am sure Vlad will be pleased…)

Seeking Your Goal

Goals are, with little doubt, what keeps businesses focused on objective for which the company was founded and a guidepost for maintaining profitability.  The classic management tool that is built in to Excel is, of course, Goal Seek.

Goal Seek is, of course, a “What If” tool enabling scenarios. What If scenarios are essential instruments of analysis for nearly any business, and Goal Seek allows you to “Set” the value of the Output at a particular value and find out what value of the Input variable achieves that output. Goal Seek can save you an enormous amount of work, and make you look good in the process (and we all like to look “Good”…).

The following is a very Simple Example showing how this tool works. With reference to the example below, let’s say that you are a Call Center Sales Manager, and you know the approximate number of calls you are going to receive for the next month, as well as the sales goal that has been set by the corner office big wigs. You wish to know the Call-To-Sale-Conversion-Rate so you can advise your staff. An efficient Goal Seek calculator can be created in the cells of your Excel worksheet as follows:

Supposition: Number of Calls x Conversion Rate = Number of Sales (e.g. B2*B3 = B4)
1) Insert the number of Expected Calls into Cell B2
2) Add the formula, =B2*B3 into Cell B4
3) Using the Goal Seek tool find the Conversion Rate by Setting the Cell B4 to the Sales Goal of 485, and have it reach that goal by “changing cell” B3.
4) The result for the Conversion Rate is 12.1% in this instance.

This tool can, of course, be used to keener advantage with more complex scenarios, but our example demonstrates the overall concept and use of Goal Seek.  This handy utensil can be very useful for the boardroom or any strategic venue. Another vital addition to an Excel Guru’s tool belt!

Revisiting Intersections

As you may have gathered, I have a soft-spot in my heart (some might say my mind…) for obscure Excel tools and techniques. The Intersect Operator is most certainly one of those obscure tools, and it can be extremely helpful if you know how to use it.  

Although there are a great many ways to gather information from Excel databases and tables, some can involve rather cumbersome formulas that can take a significant amount of time to construct and verify.  On the other hand, the Intersect Operator, is not only Powerful and Versatile, it is also quite Effortless to use (“Effortless” is always a good thing…)!

This handy tool uses the vertical and horizontal ranges in a cross-tab Table or Database, and finds the value at the Intersection (ergo, the Intersect Operator…).  The syntax (Special Note: Be sure to use No brackets or commas…) is simply:

= (RangeName1 RangeName2)

For the Ranges, you can use the generic names such as =(C2:C42 A10:K10).  This certainly gets the job done, but it is advantageous in many cases to use Named Ranges, as they are typically much more effective.

As you probably know, you can very quickly name all of the ranges in your database by selecting all cells (use Ctrl + A), and the click Ctrl + Shift + F3. This will bring up the Create Names dialogue box as shown below.  Just click OK, and Presto - Named Ranges!

 

Example: Once your ranges are Named, you can then find a value with the modest function (I’m using Cell E5 for the example):

=(Los_Angeles Quarter3)  Note: Be sure to include the space between the column title and the row title.

 

There are many, many ways of obtaining this information in Excel, but the Intersect Operator is certainly an important technique to keep in mind.  It may be an “Effortless” way to get the information you are looking for.

Every Picture Tells a Story

Charts are the quintessential graphical way to display your Excel data and information. There is no doubt about that. The fact is, however, that there are times when you want the fine print details as well. Happily, there is a truly excellent tool in Excel that enables you have both!

The Camera Tool 

Though not shown by default on the Excel ribbon, the Camera Tool can be enormously useful when you are presenting a chart, and would like to include a Resizable Image of the source data included within your graphical exhibit.  To gain ready access to this handy gizmo, simply go to the Quick Access Toolbar in the upper-left of your worksheet, and from the small drop down button, click More Commands.  Scroll down to Camera and click Add. 

Bamm! You now have the Camera Tool at your fingertips whenever you need it.

So how do you use this little piece of magic? Glad you asked!

1)  Select the data table of which you wish to use with your chart,  and Click the Camera Icon on your toolbar (just like taking a photo with a camera)

2)  Then, go to the area on the sheet on which you want the data to appear (probably a blank area on the chart), and Left-Click 

3)  Resize and Reposition until it makes you happy 

Why not just copy and paste, you ask? Because the image you captured with the Camera Tool is dynamically linked to the original! This is not just a static image, folks; Every time you update your data table, (and consequently your chart), your data image updates as well!

I guess the lines from the old Rod Stewart song were right, “Every picture tells a story, don't it…”