Judging by many on-line discussions that I have read in the past few months, Access 2013 includes significant enhancements, especially related to Cloud based applications.
Congratulations are definitely in order for the core authoring team, Teresa Hennig, Ben Clothier, George Hepworth, Doug Yudovich, and to the technical editors and contributors, Glen Kruger, Jack Leach, Jerry Dennison, Leo (theDBguy™), Patrick Wood, Peter Doering , Tom van Stiphout, Scott Barker.
No this isn’t a variation on the classic Abbott and Costello comedy routine. It’s a discussion of how to use an excel formula to arrange members of a group according to some sort of performance criteria.
One way of finding out who the top performers in a list are is to sort the list in descending order by the value that reflects the performance. Of course, that would mean having to re-sort the list every time you want to see the new ranking.
This article will demonstrate how to perform ranking without resorting the underlying list using formulas. These formulas use three excel functions, LARGE(), INDEX(), and MATCH() to rank individuals according to their total sales.
The companion workbook for this article, RANKER.xlsx can be downloaded from here. All data in the workbook is fictitious.
We have a large list of sales representatives. We want to know, without sorting the list, the names of the top 10 sales people. The leaders list should always display the current top 10 sellers based on current data in the sales representatives list.
At first glance, this problem seems to call for some sort of lookup functionality. Unfortunately VLookup() is not up to the challenge because that function can only use the first column of a table or list as an index to the list. Or list of sales representatives has the person’s id or employee number in in its first column.
The more general Lookup() function is also unsuitable because the index list it refers to must be sorted in ascending order.
LARGE The Large() function searches a list of values and returns the value that is in the relative position in the list that you specify. For example, you can find the largest value in a list without having to sort the list. The function is very flexible because it has an argument that lets you specify any specific relative position in the list from 1 (largest value) to the very last position (by specifying a number equal to the total number of items in the list. All this can be done without ever having to sort the list.
INDEX The Index() function returns the value found at a specific position in a list.
MATCH The Match() function searches a list and returns the number of the list row in which the value you have said to search for is found. The example used in this article specifically uses a one-column list. If the list contains duplicate values, Match will find one of the duplicates over and over for as many times as the value is duplicated.
Solving the Duplicates Issue
While the probability of two representatives having exactly the same Sales value may be low, the very possibility that a duplicate might exist necessitates a strategy to deal with duplicates should they occur.
In this example, we have used a bit of formula trickery to create a unique way of identifying duplicate values.
This formula when copied down a column will count the instance of a value in the specified range.
Notice the use of mixed references in the first argument. For this technique to work, the list may not be formatted as an Excel 2007/2010 Table. If the data is formatted as a Table, convert it to a range before entering the formula.
This expression is part of the more complex formula contained in cells F2 to F224 on the Demo_Notable worksheet. When copied down a column the reference to the start of the range remains fixed on E2 while the end of the range and the criteria cell reference are incremented for each successive reference.
However, that’s not the formula trickery I mentioned. In order to use the Large() function, we need numeric values. At the same time, in order to create a useful identifier that will not distort results, we need to work with numbers formatted as text. The trick is to convert intermediate calculations to text and then reconvert the final result back to a number, not just the textual representation of the number.
This is what the full formula looks like
When you copy this formula down the column, say to E3, Excel modifies the relative cell references. So in Row 3 the formula would become:
The idea is to convert the Sales value to text with a fixed number of digits and then concatenate the text value with the text representation of the count of duplicates. Simple adding the two values together arithmetically would distort the value and not bring us any closer to having unique value that will distinguish between duplicate instances. COUNTIF() is counts how many times the value of interest occurs in the specified range, which grows row by row as copy the formula down the column.
Here’s what the formula looks like when you copy it down several rows. Notice that the second argument of the CountIf() function changes the end of the range being counted for each row the formula is copied to. However, the range beginning is anchored to cell E2 because that part of the reference is absolute.
Caution: Using this technique with a very large list may be very slow. For lists of a few hundred items or less should not be affected.
The Final Solution
Six formulas drive the solution. Here are the formulas from the second row of the try it worksheet. Once the individual formulas have been created they can be copied down their respective columns (using autofill.)
The formula in F2 converts the sales value in E2 to Text and concatenates that value with a four character representation of the result of counting the frequency with which the value in E2 appears. This gives us a unique index value that ensures we will be able to find each instance of the duplicate value.
In I2 the formula finds the value in column E that is x positions from the largest value. H2 contains the value 1, so the formula in I1 returns the largest value from the list. This result is not quite adequate because it can’t distinguish duplicate values.
J2 finds the row number of the value in I2. When there are duplicate values in column E, the result in this column will always indicate the first row in which the value occurs.
The formulas in K2 and L2 are very similar to those in I2 and J2 EXCEPT the formula in K2 uses values in column F to determine rank. The forumlas in column F ensure that there are no duplicates to be ranked. The method uses has a significant side effect because it yields an higher rank for the first occurrence of a duplicate and progressively lower ranks for each subsequent occurrence.
The video accompanying this article includes a demonstration of the effect of duplicates on ranking.
The final formula in cell M2 uses the row number calcluation in L2 to find the name of the rep having a particular rank.
Introduced in some Office 2007 applications, the Fluent User Interface is Microsoft’s attempt to ‘expose’ (make more readily available) the commands used to work with an Office application. With Office 2010, all Office applications use this style of user interface. For the average user, the most visible aspect of the Fluent User Interface is the Ribbon, which replaced the menus and toolbar interface style of Office applications from ‘97 through to 2003.
On the positive side the Fluent User Interface does a creditable job of displaying, and making more easily available, the commands that serve the needs of the vast majority of users. In the menu/toolbar style that preceded the Fluent User Interface, some commands necessarily had to be buried in the menu structure. Only the most adventurous user stood any chance of accidentally discovering some of these commands.
The Ribbon, on the other hand makes it possible for the user to easily discover many more commands simply by exploring the contents of each tab. What users will encounter however, are differences in the appearance of the Ribbon depending on the current width of the Application window and the monitor’s resolution.
Notice the detail on the Home tab of the Excel 2010 Ribbon for example:
This is a screenshot of the Ribbon as it is displayed in a very wide window. For display purposes here, the image had to be somewhat resized. Compare that image with this one, using a narrower window:
In the first image, the Styles group has a rich assortment of buttons. In the second, the styles group has only three buttons. In to see the cell styles gallery, you have to click the Cell Styles button dropdown.
With progressively narrower windows more and more groups are collapsed to a few essential buttons which you must click in order to see all the possibilities a group offers:
Here the Styles group has been reduced to a single button:
Here, the Number and Cells groups are also reduced to single buttons:
When the window is extremely narrow, most groups are barely recognizable. Notice that none of the tabs can display their full name:
It is even possible to reach a point where there is no longer enough screen with to display the full Ribbon:
When that happens, ‘expander’ buttons appear, allowing you to navigate to hidden portions of the Ribbon.
Practically speaking, it is unlikely that you would ever use such an extremely narrow window as in the last graphic but keep in mind that screen resolution also affects how the Ribbon will display. The lower the resolution setting, the more likely it is that you will see a truncated view of the Ribbon.
Guest Blogger Jack Leach is a colleague of mine at UtterAccess where his pet project is the Access Wiki. Recently, in a mailing list that we both subscribe to, Jack posted his experience in building an Access application for his business. You can find his website at http://www.dymeng.com and his blog at http://dymeng.com/blog. Jack has graciously given his permission to OTM to publish his success story.
I’ve used Access to create a shop management software for my manufacturing business. Besides the average FE type stuff: customers, orders, purchasing, quickbooks integration, the software also handles all the backend stuff: visual scheduling, what-if scenarios, integrated quality control, everything right down to tooling tracking (of which there’s thousands). Min/Max for both customer pull-supply as well as shop supply, so on and so forth. The whole nine yards.
Even more so, I was able to integrate basic CAM (Computer Aided Machining – like CAD that outputs machine tool code for producing the parts) into the routing structure for the components we make. AutoCAD automation for reading customer drawings and auto-generating tolerance sets for quality reporting, etc.
The very first piece of the application – my first Access project – saved the company an estimated $20k/year by providing different machine code formats for different machine tool platforms.
We work with medical, military, aerospace and other demanding industries, which has all been fully supported by this for quite a few years.
I am among the first to point out that Excel is not a data manager. Yet, at the same time, Excel does have some useful features for working with data, especially small data collections. Such lists can be used directly for data analysis, for example, or indirectly as the data source for mail merges in Word.
Regardless of how the list will be used there are four fundamental rules for setting up an Excel data list:
- The list should have a header row of unique names for each column in the list
- There should be no blank columns in the list
- There should be no blank rows in the list
- Similar data should appear in one column
The last rule may be misunderstood because there is one other guideline for setting up any list. Data in any column should not be grouped. For example, in a name and address list, newcomers are tempted to list a person’s full name in a single cell. This can become problematic very soon.
For example, if you use a single cell for people’s full names, you can then sort the list only into ascending or descending order by the full name. Depending on how you actually enter the name information, a meaningful sort may prove to be impossible.
So, while first name, middle name and last name are similar types of information –they are all names, after all – they are really different and deserve their own cells and therefor their own columns. Separating names into Full Name, Middle Name and Last Name, allows you to then sort the list by Last Name; where several people have the same last name, those groups can be further sorted by First and Middle Names.
Separate columns for First, Last, and Middle names, makes your data far more flexible to work with in other ways. For example, having the first name separate from the last name makes it possible to address a letter to John Smith, but have a salutation in the letter like, “Dear John.”
The video illustrates just how easy it is to create a simple data list. In coming articles, I will discuss how to enhance the list to provide additional columns (fields) for mail merging and how to set up the list as mail merge data source.
Whether you purchase applications on-line and download the installation files or install new software from installing disks, keeping a central record of Application Licences and Activation Codes can greatly simplify managing the applications you have and having the information you need (User Name, associated email, licence number and activation code). OneNote provides a simple solution for individuals and small businesses who do not have ready access to Information Technology experts who specialize in such management.
Keeping the information organized simplifies the process of
- upgrading applications
- re-installing applications
- transferring applications to a new computer system.
There are, of course, a number of different ways you could organize this information in OneNote. My notebook has just two sections.
The contents section has only one page which serves as a table of contents for the notebook and as a quick list of all applications. Each entry on this page is a hyperlink to the main page for each application
I have organized the Applications Section by having a Page for each application. If I have received the main information by email then I simply send the email to OneNote using the link on my Outlook Ribbon. Once I have created this page, I copy the link to it and insert it as a hyperlink on the Contents page.
Occasionally, additional information about the application may come up. For example, I called support for one application. To keep a record of the discussion and its outcome I created a subpage for the application, titling the page Support followed by the date of the discussion. Having a separate subpage for each support call, if there is more than one, makes it easy to track down and review the applications support call history
An alternative approach to organizing application information might be to create a separate OneNote section for each application with as many detail pages as necessary.
However you organize the pages, a summary of key application information at the top of the main application page can be helpful. Include in this summary as much key information as you have used in obtaining the application in the first place. For example most of the applications I have installed have the following types of information:
- Developer/Provider Website
- User Name
- User Email
- Serial Number
- Activation Code
- Sales Contact
- Support Number/Address
- Installation Date
- Upgrade Versions and Installation Dates
If I have created an account with the Provider that I purchased the application from, the one piece of information that I do not store in OneNote, is my password. Similarly, if the application itself requires a log-in password, for security reasons I do not store the password in the OneNote workbook.
OneNote makes it quick and easy to store and update Application “Vital Statistics.” Investing a few minutes to record this information when you install a new application can save you hours of frustration in the future trying to remember where you put that scrap of paper that scribbled the details on.
Recently, a colleague distributed some rather important health and well-being statistics, illustrating the data with an exploded 3D Graph. Unfortunately, while 3D graphs are more pleasing to the eye than their flat cousins; the perspective necessary to create the 3D illusion, distorts the values being plotted.
Here’s an example using simple arbitrary data. First the exploded version:
Notice how, in the 3D version, the Cons wedge appears smaller than the Pros wedge, even though the two wedges represent exactly the same value (46%). In the flat version, on the other hand, the Pros and Cons wedges appear to have exactly the same size.
Is it the Exploded view that creates this illusion? Consider the following unexploded views. The illusion persists.
An unscrupulous presenter could easily use this illusion to distort the facts and unfairly influence his/her audience. (Remember the adage: “Figures don’t lie; liars figure”?) Think about a political race, for example. Depending on which position the presenter wants to improve the apparent advantage of, all he or she has to do is rotate the 3D chart accordingly to immediately improve the apparent advantage of the favoured position:
The Pros Have It!
The Cons Have It!
Of course, these examples include data labels to help the viewer’s interpretation. Omitting the labels can only make the dishonest presenter’s self-appointed task of deception easier.
So the next time you have to create a graph, think carefully about purpose of graphs and avoid the inevitable optical illusions inherent in 3D charts.
Here are some useful links that I have recently come across on the Web. The list is weighted in favour of Excel only because, at the moment, I am doing mostly Excel training.
A help guide to working with the Fluent User Interface (Ribbon) first introduced in Office 2007 and enhanced in Office 2010, replacing traditional menus and toolbars
BLOGS AND PEER SUPPORT SITES
A blog focusing on tips and how to techniques for getting the most out of Microsoft Office Applications
User peer support forum primarily focused on Microsoft Access but with an active Excel forum as well
Bill Jelen’s (Mr. Excel) site includes peer support forum and many pages of Excel help and tips from Mr. Excel himself
YouTube gateway to the ExcelisFun channel –hundreds of well documented videos illustrating how to do just about anything in Excel.
An excellent collection of Excel related links
Clever data validation example
Color Palette and the 56 Excel ColorIndex Colors:
Create an Excel UserForm
Excel Add-ins Directory:
Analytics and Visualization
Excel for Developers
Excel VBA – Reference Guide
Index of /Excel:
The Spreadsheet Page:
Excel Add-ins Directory
Use Excel spin boxes to help with financial modeling:
VBA for Microsoft Office Excel 2007
Yahoo Groups MS_Excel
Yahoo Groups Excel VBA:training.