Add Muscle to Excel

User Defined Functions empower calculations.

By Jeff Lenning

august 2007

» Key to Instructions To help readers follow the instructions in this article, we used two different typefaces: Boldface type is used to identify the names of icons, agendas and URLs. Sans serif type shows the names of files and the names of commands and instructions that users should type into the computer. ave you ever wanted to do something in Excel only to find that it has no function for that task? That shouldn’t stop you, because Excel has a built-in ability to help you customize your own functions. The key is Excel’s macro tool, which uses software code written in Visual Basic for Applications, or VBA. If you’ve never prepared VBA code, don’t worry. After I walk you through the steps, you’ll see how easy it is to use macro functions to perform calculations inside Excel formulas. With them you can perform calculations that would otherwise be impossible—or at least very difficult. AUTOMATE A TASK Consider this example: One of your clients, Ocean Ridge Technology, has a sales commission plan that is so unique there is no way Microsoft could have included the necessary worksheet function in Excel. However, since you prepare Ocean Ridge’s payroll and related monthly commission computations, automating this monthly task would speed your work significantly. Follow along as I go through each step to create the unique function. In Ocean Ridge’s commission plan, sales reps in the North region receive a base commission of $1,000 plus 10% of their total sales. Sales reps in the South region receive a monthly commission of 5% of the amount by which their sales exceed budget. So our goal is to create a function I’ll call =commission() that calculates the figures. The first step is to think through the process and then document each step you’d use to calculate the commission. For example, the following would be a useful statement: If region is North then Commission = 1000 + sales*10% If region is South then Commission = (sales-budget)*5% Here is the VBA code for the function. Function commission(region,sales,budget) 'computes commission based on region dim temp as integer temp = 0 if region = "North" then temp = 1000 + sales * 0.1 end if if region = "South" then temp = (sales-budget) * 0.05 end if commission = temp End function Notice the similarities between the initial statement we composed and the final code. Now I’ll walk you through each line so you’ll see how the code is composed so you can compose your own code later. The first line is: Function commission(region,sales,budget) I used the keyword Function rather than the usual macro keyword Sub to signal to Excel how I intend to use the code. The keyword Sub tells Excel that this code is a macro and should appear in the Macros list and run when activated by the user. The keyword Function tells Excel that it should show up in the User Defined Functions category and run when summoned through a worksheet cell formula. I gave the function a name, commission() , that describes the calculation and is easy to remember. That is the name you will use to refer to the function in your worksheet cell formula. Later you’ll see that the brackets () attached to commission will contain the function arguments and look like this: =commission() Tip: Avoid using function names that are similar or equal to existing Excel function names, like Sum() . Moving on to the next line: 'computes commission based on region Notice that the line begins with a single quote; that punctuation instructs Excel to ignore what follows, which in this case is only a comment you may wish to add to help you identify the function. The next line: dim temp as integer The keyword dim declares, or defines, a variable, which in this case is temp . The word temp is our variable name; values are assigned to it during the execution of the code. You have some freedom when setting your variable name, but it must start with an alphabetic character and should contain no spaces or special characters (such as ? or >). The keyword integer tells Excel that the type of data the variable temp will contain is an integer—that is, a whole number with no decimals. Other types of data include string (text) and date , among several others. The next line: temp = 0 This assigns the value of 0 to the variable temp . It’s generally a good idea to assign a default value to every variable you set up. By doing so, you avoid unexpected errors and conditions in your code later on, and it keeps your code nice and clean. The next lines: if region = "North" then temp = 1000 + sales * 0.1 end if The if command alerts Excel that it will be asked to process a standard if/then command—that is, if the region is North, then the subsequent VBA code should be executed. But if the condition is not true, then the code is to be skipped. The next lines: if region = "South" then temp = (sales-budget) * 0.05 end if These lines do the same for the South condition as the one above did for the North condition. The next line: commission = temp This line tells Excel which value to return to your worksheet cell. Assigning a value to the function name ( commission ) instructs Excel to put that value (that is, the results of your computation) into the worksheet cell formula. The last line tells Excel the code has finished: End function ADD THE FUNCTION TO EXCEL Now that the code is prepared, it has to be installed in an Excel module. Start by opening the Visual Basic Editor by clicking on Tools , Macro , Visual Basic Editor . Then click on Insert , Module (see screenshot below). Enter the code by typing (or copying) it into the space in the module just as we’d prepared it in the word processor (see screenshot below). PUT THE FUNCTION TO WORK Now that the custom function code is written, it can be used the same way you would apply any other Excel function. Using the spreadsheet shown in the screenshot below as an illustration, you can see how I entered the function name in the formula bar with the function arguments inside parenthesis: =commission(B5,C5,D5) . Inserting worksheet functions into cells is always accomplished either by manually typing them into a cell preceded by an equal sign, or by using the Insert Function, which is accessed by clicking on Insert, Function. This is true as well for custom functions; after selecting Function, click on User Defined and they appear for your selection (see screenshot). Insert Function also provides an easy way to enter function arguments by either typing in the cell location manually (B5) or by clicking the worksheet cell B5, as shown below. Additional resources for VBA programming are available through the Visual Basic Editor help menu and at www.microsoft.com. If you do a Google search with the key words VBA custom functions , hundreds of sites and code samples will turn up. Although it takes a little time to prepare a custom function, once it’s done it becomes an enormous timesaver. Now that you’ve seen how easy they are to create, you may want to start creating even more complex functions. Jeff Lenning, CPA, CITP, is the founder of Click Consulting, Seal Beach, Calif., which specializes in network support and application development. His e-mail address is jeff@clickconsulting.com, and his Web site is www.clickconsulting.com.

Discover the Power of Excel 2007

By Bradley C. Adams

february 2008

When most users open Office 2007’s Excel—launched with Microsoft’s new Vista operating system—their first reaction is, “ Where’s the file menu? How do I begin?” This article is designed to help you steer through the labyrinth of new and super-powered functions. Even more important, it will show you how to customize the screen to meet your unique needs so you’ll spend less time stumbling through the vast display of icons. THE RIBBON The first visual obstacle you’ll encounter is the Ribbon. The traditional toolbars of earlier editions have been replaced with this expanded toolbar and it will take a bit of adjusting for many—if not most—new users to overcome what looks like a puzzling collection of icons and buttons. The simple file menu toolbar was changed from this: To this: The Ribbon consolidates into one vast display many—but hardly all—of Excel’s menus and many of the floating toolbars. While it’s off-putting at first, once you sort the many options, you’ll probably find the new layout helpful in quickly preparing professional-looking reports. Most of the typical file menu commands can now be found by clicking on the Office Button in the upper-left corner of the screen. Here you can create a new spreadsheet or Open , Save , Print and access several other commands. All the menu items on the Ribbon are grouped by function. If you have trouble finding a feature, just ask yourself, “ What do I want to do in the spreadsheet? ” If, for example, you want to insert a graph, click on Insert . If you want to filter the spreadsheet, that’s manipulating data, so look under Data . If you’re still having trouble, click on Help (F1). Unlike the frustrating Help function in Excel’s XP/2003 version, this guide is quite useful. It’s in the upper-right side of the Ribbon . In addition to the standard tabs, Excel automatically displays a host of new ones each time you select a specific task. For example, if you’re working on a PivotTable function, the Ribbon will display additional tabs relevant only to that task. HELP FOR THE IMPATIENT Those who just can’t stand the Ribbon have an option. They can turn to third-party solutions that re-create the traditional file menu interface and still work within Excel 2007. If you wish to explore them, do an Internet search or go to such sites as ToolbarToggle (www.toolbartoggle.com) or Classic Menu (www.addintools.com/english/menuoffice). However, I recommend resisting the temptation to revert to the old file menu. As irritating as the Ribbon may seem initially, it will eventually pay off handsomely because some of the inherently complex tools, such as the PivotTable and Conditional Formatting , are made much easier to use and, in some cases, are even semi-automated. Be aware, however, that Excel 2007, as well as the other Office 2007 applications, works just fine in XP computers—so you don’t have to rush to make the complete changeover to the Vista operating system. Toolbars are not completely gone: the Quick Access Toolbar sits atop the Ribbon and to the right of the Office Button . By default, it contains three buttons: Save , Undo and Redo (see screenshot below). To add buttons to this toolbar, right-click on it and select Customize Quick Access Toolbar . Then select your choices and click on Add . Those selections will appear on your Quick Access Toolbar . MORE CAPACITY Accountants who typically work with large, complex spreadsheets will be delighted with Excel’s expanded capacity. It now can handle 1,048,576 rows, up from only 65,535 in the XP/2003 edition. And the number of columns rose to 16,384 from 256. That means you now have 17,179,869,184 cells per sheet, compared with 16,776,960 cells before. The table below shows all the areas where Excel’s data capacity has been expanded. FORMULA BAR Those who typically use large formulas or text notes can now view the entire formula without it overlapping into the worksheet because the formula bar can be expanded. To do that, hover the mouse pointer over the bar’s bottom border and the pointer will change to a double arrow; then left-click and drag the formula bar down to expand its size. Those who write many formulas will be especially delighted with the introduction of a new function that intelligently anticipates the formula you’re planning to write based on the first few characters you enter and offers a list of suggested possibilities. Word has been doing something like that for some time: when you begin typing a month or day of the week, it offers to complete the word. In Excel, however, if you start to write a formula, say, that begins with =v , Excel will list the possible functions or named ranges that start that way (see screenshot below). To select from that list, highlight your choice and press the Tab key. Once you get used to this intelligent function, you’ll wonder how you survived without it. Conditional Formatting —the function that lets you apply cell shading, borders, colored icons, arrows, flags and font formatting—has become quite sophisticated and automated in the 2007 version (see screenshot below). Some other things Conditional Formatting can do: Shaded data bars (see screenshot below, Column B) can be added to graphically enhance the underlying numbers by embedding a simple bar chart in the cells. Color scales (Column C) can automatically shade the cells to different colors based on their relation to values in other cells in the range. The default settings will shade the lowest value in red moving up to the highest value in green. Icon sets (Column D) are similar to color scales but rather than shading the cells it adds small icons, such as traffic lights, to the cells. To add an icon, select a range of data ( D2:D6 in screenshot below), then on the Ribbon , go to the Home tab and click on the Conditional Formatting button. From the dropdown menu, select icon sets, then pick the icon set you like (the traffic lights are used in the picture). Excel will automatically apply default logic based on the values of your cells. To set your own logic, click More Rules at the bottom of the icon sets menu. CELL STYLES Excel 2007 provides a quick and easy way to apply formatting to a range of cells. Start by selecting a range and click on Cell Styles on the Home Ribbon , which opens a menu of choices. As you mouse over a style, the spreadsheet will provide an instant preview. Cell Style even gives you the option of developing your own styles by clicking on New Cell Style and then building your own design (see screenshot below). TABLE STYLES Excel has adopted PowerPoint’s technique for creating formatting. With Table Styles you can convert a range of data to a table with just a few clicks. Select a cell inside the data range and on the Home Ribbon click on Format as Table and select your choice. First it will confirm the range and whether it has a header row and then it will apply the formatting, which typically includes bolding the header row, applying alternating line color to the rows and turning on AutoFilter . As if that’s not enough, it automatically updates the formatting as data are added, deleted or hidden (see screenshot below). Charting received a face-lift, too. You can now produce charts that rival the work of a professional graphics department. Much of the formatting control has been moved from dialog boxes and right-clicks of the mouse to the Ribbon . To create a chart, select a data range, go to the Insert tab on the Ribbon and click your choice of chart types. When a chart is selected, the Ribbon will add new charting items to the far right. Now things like color patterns, legend location and data labels can be changed with only a click or two. There also are additional chart styles that can really make charts pop (the one shown below is in the fourth row of style options). Change from this: To this—with a few clicks: REMOVE DUPLICATES One of the best new features of Excel 2007 is the ability to remove duplicates. I often receive large files with data dumped from another system, and I’m really only interested in one or two pieces of information. For example, before Excel 2007, if I asked for information about all employees in a company and instead was given the entire payroll file for the last year, I would have had to manually delete all the extraneous data and duplicate records, or at least write a formula to find the duplicates. Now, by selecting the Data ribbon, simply click Remove Duplicates and Excel will ask which columns to use to determine unique entries (see screenshot below). You can determine a value is unique by combining multiple columns. Once you click on OK the duplicates are gone. As you can see, Excel’s tools now have more muscle, more flexibility and more automation. But before you can make use of these tools, you must master the Ribbon and customize it. For those who have worked daily with the old file format design, this change will be a challenge. But it’s a challenge worth taking. Bradley C. Adams, CPA, is a senior auditor at Vanderbilt University and an adjunct instructor at Lipscomb University where he teaches accounting information systems. His e-mail address is brad.adams@vanderbilt.edu . Excel 2007’s New Alphabet Excel 2007 has introduced a new, super-powerful file format, *.xlsx . But be forewarned: Spreadsheets formatted this way are not always compatible with prior versions. This new format is based on XML (the same technology on which XBRL is based) and is an open standard that will allow compatibility with third-party programs. But rather than being one XML file, it is a compressed ZIP file that contains several XML files along with other files, such as graphics and sounds, that have been inserted into a spreadsheet. The files can be opened with programs like WinZip, and the individual parts can be extracted, edited and replaced without ever opening Excel. The most useful aspect of this feature is that Office no longer embeds a picture into a proprietary file but rather stores the original, unedited file and it can be retrieved. The fourth character, x , in the *.xlsx nomenclature identifies a file as not containing macros; but if the final letter is an m instead of an x , that means it contains macros. Identifying files that contain macros is a security measure; macros are favorite vehicles for malicious code. If a file has the extension xlsx , and you add macros to workbook, Excel will warn you to save the file in the xlsm format; otherwise your macros will not be saved. Here are several things you can do to ensure users of earlier Excel versions can still view your new *.xlsx files: Save a file in Excel 97-2003 format by clicking on the Office Button and selecting Save As and then Excel 97-2003 Workbook . Change Excel’s default file format so all files are saved in the old format. To do this, click on the Office button (upper-left corner) then click Excel Options (at the very bottom of the new screen). Select Save from the list of options at the left, then expand the dropdown menu and select Excel 97-2003 Workbook ( *.xls ). But be aware that all of the new 2007 format and function features will be lost when you save a file in the old format. Excel will run the Compatibility Checker to let you know specifically what functionality will be lost when subsequently opened in a prior version of Excel. For example, if you use the new function iserror() in a workbook and save it in the earlier format, Excel will warn you that if opened in Excel 2003 those cells will return a #NAME? error rather than their current results (see screenshots below). Save the file in PDF format. While the file loses its ability to calculate, the data are easy to view. You can download a free add-in for Office 2007 that will let you save your documents as PDF files. To download, go to http://office.microsoft.com and search for PDF . Have the person receiving the *.xlsx file download the Office 2007 Compatibility Pack. To get that, go to http://office.microsoft.com and search for Compatibility . After installing this for Office XP/2003, users can open, edit and save Word, Excel and PowerPoint documents in the 2007 formats.

Forecasting With Excel

Regression analysis can help predict revenues and costs.

By by James A. Weisel

February 2009

Your client, Dave’s BBQ, a local independent restaurant, is interested in determining the effect on sales revenue of certain advertising strategies. Dave has weekly data on advertising dollars spent as well as sales revenue from the restaurant and has come to you, his CPA, to help him determine the link between the two. It certainly seems reasonable that advertising, at least in part, drives sales revenue, but you need to measure the strength of that relationship and then use the information to help forecast sales revenue for future periods.

Creating financial forecasts from historical data can be daunting for managers. However, several tools in Excel are readily accessible and easy to use to help with forecasting revenues and costs. The use of regression analysis can significantly enhance the accuracy of your financial forecasts and make the budgeting process more efficient. This article guides you through the process of using one of these tools, linear regression. The purpose of this article is to demonstrate how to use Excel for forecasting—not to teach forecasting or regression analysis. The discussion below assumes a basic understanding of linear regression (for more details, see “Further Reading.”).

The screenshots demonstrate the tools in Excel 2003. The tools function much the same way in Excel 2007, but accessing them is slightly different. See the sidebar “Accessing Regression Tools in Excel 2007” for directions. To follow the analysis of Dave’s BBQ, download the illustration file here.

PLOT THE DOTS
Linear regression is a statistical tool that can help determine whether the link between a measure of activity (x) and an output measure, such as cost or revenue (y), is sufficient to be used to forecast that output measure. The x is generally referred to as the independent variable and the y as the dependent variable. In the case of Dave’s BBQ, we analyze weekly sales revenue (the dependent variable) versus advertising dollars (the measure of activity or independent variable). Scanning the data in Exhibit 1, it appears there is a positive correlation between advertising dollars and weekly sales revenue. In other words, higher levels of advertising seem to be associated with greater sales revenue. To confirm this assessment and generate forecasts, we will use a graphical approach to regression analysis.

Begin by highlighting cells B1:C21 where the advertising costs and sales revenue data exist. With those cells highlighted, start Excel’s Chart Wizard by selecting Insert, Chart from the menu bar as illustrated in Exhibit 2. In Step 1 of the Chart Wizard select XY (Scatter) from the Standard Types list as shown in Exhibit 3, then select Next. In Step 2 of the Chart Wizard you should see a thumbnail of the chart being created (see Exhibit 4 below). It should be noted that Excel assumes the first column of data selected represents the independent variable (x), while the second column of data represents the dependent variable (y); in this case x and y represent advertising and sales revenue, respectively.

Select Next again to move on to Step 3 of the Chart Wizard shown in Exhibit 5. Here you can specify the Chart Options including title, labeling the axes, and other aesthetic elements. After completing those tasks as desired, select Next. In Step 4 of the Chart Wizard, select the location of the chart. I normally leave the default to include the chart as an object in the current worksheet tab and select Finish as illustrated in Exhibit 6. Your worksheet should now look similar to Exhibit 7 with the completed chart and data all in a single worksheet.

Visually we can confirm our initial assessment of a connection between advertising and sales revenue. More advertising leads to more sales revenue. However, it would be nice to measure the strength of that relationship and determine the extent to which we can rely on that relationship to forecast sales revenue. Linear regression can measure and specify the relationship.

CONNECT THE DOTS
Now that the chart is created, we can include regression analysis directly in the chart. Right-click on any data point in the chart and select Add Trendline as shown in Exhibit 8. Leave the Type as Linear and move to the Options tab. Select Display equation on chart and Display R-squared value on chart, and click OK as illustrated in Exhibit 9. The completed analysis is shown in Exhibit 10.

R², called the coefficient of determination, is a measure of the degree to which changes in the independent variable (advertising) are associated with changes in the dependent variable (sales revenue). Note: While R² is one of the more ubiquitous measures to evaluate the forecasting model’s effectiveness, it is by no means the only measure. You are encouraged to become familiar with additional regression diagnostics. R² can take on values between 0 and 1. Values closer to 1 indicate a stronger relationship. Larger values of R² result in more reliable forecasts. We interpret that R² for Dave’s BBQ as 69.95% of the variation in sales revenue is associated with variation in advertising. The remaining 30% of variation in sales revenue is presumably due to random fluctuations, weather, pricing, quality of service, or other factors.

The trend line in the chart represents the forecast of sales revenue based on advertising using the following equation: sales revenue (y) = (35.202 × advertising (x)) + 21,792. In other words, there is a baseline of $21,792 sales revenue plus $35.20 of additional revenue for each $1 of advertising.

GENERATING FORECASTS USING =FORECAST
Now that we have completed analyzing the data, we can use one of the functions to predict sales revenue based on advertising. Create a forecast in any blank cell (for example, cell F2) by selecting that cell and typing =forecast and then using Ctrl+A to bring up the Function Arguments dialog box for the linear regression forecast function. Exhibit 11 shows the completed dialog box where we specify that cell E2 will contain a future value for x (advertising), the Known_y’s (actual sales revenue) in C2:C21, and Known_x’s (actual advertising dollars) in B2:B21. Click OK. As shown in Exhibit 12, insert some future advertising costs (for example, $200) and the value in cell F2 adjusts the forecast for sales revenue ($28,832 = (35.202 × 200) + 21,792) based on the linear regression equation noted earlier. We can insert various values for advertising in cell E2 and instantly see the predicted sales revenue.

OTHER USES OF LINEAR REGRESSION
While this article focuses on using linear regression tools in Excel for forecasting revenue, linear regression can also help model a variety of business situations. Regression can be used to establish relationships between costs and activity to improve the management of costs. A thorough understanding of the company’s cost functions is imperative for effective cost control.

Regression analysis could also be used to gain an understanding of the price elasticity of your company’s products. Understanding how changes in prices affect changes in sales volume can be critical to maximizing revenue for the organization.

Regression can also be used for time-series analysis. For example, suppose you wanted to analyze daily closing stock prices for your company over a period of one year. You would obtain a list of all the closing prices for the stock each day for the past year and list them in chronological order. Regression analysis could be used to identify patterns in the series of data.

CAUTIONS IN USING LINEAR REGRESSION
Users should be aware of several cautions before relying on regression analysis for forecasting. First, the analyst must be sure that an economically feasible relationship exists between the dependent and independent variables. R² is meaningful only if the relationship between the measures is economically plausible. In the case of Dave’s BBQ, it seems realistic that advertising and sales revenue are economically linked.

Second, the results of the analysis are only as good as the historical data. Errors in the data or use of too few data points may skew the analysis and generate unreliable forecasts of future measures. The forecasts are valid only within the relevant range of historical data. In other words, Dave’s forecasts of sales revenue are reliable only for a range of advertising of approximately $125-$300. As advertising spending moves further outside that range, the forecasts’ reliability diminishes without reanalyzing the data and including that new information.

Third, Dave’s BBQ uses what is known as simple, linear regression. The “simple” part refers to establishing the relationship between one dependent variable (sales revenue) and one independent variable (advertising dollars). There are several variations to regression analysis such as multiple linear regression whereby a dependent variable is associated with more than one independent variable. For example, revenue for Dave’s BBQ may be a function of advertising dollars as well as day of the week, month or other factors. Care must be taken to evaluate multiple explanatory variables to ensure unbiased forecasts. Use of additional, economically feasible, independent variables can increase R² and improve the reliability of forecasts.

Fourth, the “linear” part refers to an underlying assumption that a straight line can reasonably explain the association between sales and advertising. In numerous situations, however, variables have nonlinear (curvilinear) relationships. Consideration of those issues is beyond the scope of this article. Many times, a simple plot of the data suggests a nonlinear relationship, and the use of variants of linear regression may be beneficial.

A variety of excellent resources are available in print and on the Internet that describe in much greater detail the ins and outs of using linear regression analysis (and its variations) for financial forecasting. Cost and managerial accounting books typically include discussion on applying linear regression to financial modeling problems.

James A. Weisel, CPA, DBA, CMA, is a professor of accountancy at the School of Business, Georgia Gwinnett College, Lawrenceville, Ga. His e-mail address is jweisel@ggc.usg.edu.

---

Further Reading
Numerous books are available on the subject of applying linear regression (and its variants) to solving business problems such as Applied Regression Modeling: A Business Approach, by Iain Pardoe, Wiley, 2006. Additional, nontechnical examples of applied regression analysis can be found in Freakonomics, by Steven D. Levitt and Stephen J. Dubner, HarperCollins, 2006.

---

Accessing Regression Tools in Excel 2007
The tools described in this article are accessible in Excel 2007 as follows:

To insert an XY (Scatter) Chart begin by selecting the two columns of data, select the Insert ribbon and select Scatter with Only Markers from the Charts menu. To modify the aesthetics of the chart, click inside the chart to activate the Chart Tools ribbon and select the Layout tab. Various options to alter the titles, gridlines, etc., appear in the menu. To add a trendline, the regression equation, and R2 to the chart, right-click on any data point in the chart and select Add Trendline… and select the appropriate elements from the Trendline Options dialog box.

The =forecast function works identically in Excel 2007 and Excel 2003. Select any blank cell (such as F2) and type = forecast and then use Ctrl+A to bring up the Function Arguments dialog box for the linear regression forecast function.

Supercharge Your Excel Sum Operations

Add data by up to 30 criteria.

By J.D. Kern

JULY 2009

Many CPAs, frustrated by rigid and inadequate reports from their general ledger or other enterprise systems, turn to Microsoft Excel. Nimble but powerful, Excel often manipulates data faster and more effectively than less agile applications. But to perform certain tasks optimally, a CPA sometimes may have to bypass what apparently is Excel’s most relevant function and instead use another Excel function that at first may not seem suitable. This article presents such an instance, comparing the SUMIF and SUMPRODUCT functions and demonstrating an innovative approach that can produce the reports you need, quickly and easily.

Let’s begin by automating a simple but tedious and potentially error-prone data analysis and reporting process. Here, a well-known Excel function does the job perfectly. Later, we’ll look at a harder task that requires a more complex—but very workable—Excel solution.

Editor's note: Click here to download this article's sample Excel worksheets. Each of the article's four exhibits is an individual worksheet in a single downloadable Excel file.

Say you want to calculate the total sales for each member of a team, but your GL or other enterprise system can’t do the job. So you export the relevant data into Excel, where you use the SUMIF function [SUMIF (range, criterion, sum_range)] to cull and add up the sales transactions for each salesperson. It’s clear this function can save a lot of work by automating the addition of sales selected according to a single criterion, such as a salesperson’s name.

Exhibit 1 contains sales transactions for four salespeople, one of whom is Alice. To calculate her total sales, we use the formula in cell E3: SUMIF(A3:A15, D3, B3:B15), which correctly reports that Alice’s three sales ($100 + 300 + 350) add up to $750.

As you can see, SUMIF requires three pieces of data. The first is the list of criteria to check for the desired value (that is, sales by Alice). In this example, the salesperson for each transaction is listed in cells A3 through A15. That range is the first element in our SUMIF formula.

Second, SUMIF needs the selection criterion to apply when searching the range specified in the formula’s first element. Because we want to know the sum of Alice’s sales, we instruct SUMIF to search for Alice’s name—the contents of cell D3. That cell’s address is the second element in the SUMIF formula.

Finally, we specify which values to sum when the criterion in the formula’s second element is satisfied (that is, when the contents of any cell in the range A3 through A15 equal the contents of cell D3). For rows meeting that condition, SUMIF will total the related sales amounts in the range B3 through B15. That range is the final element in our SUMIF formula.

Using this formula, Excel summed in cell E3 all of Alice’s sales. To calculate sales for Jim, Samantha and Tom, insert similar formulas in cells E4, E5 and E6, respectively.

DOUBLE-BARRELED CRITERIA

Now let’s consider a harder case. Like the first one, it requires painstaking attention to detail. But this time, the process is more complicated. Instead of having to report only total sales for each salesperson, you have to calculate their sales for each month covered in the data you downloaded.

SUMIF can’t help you now; all it can handle is one criterion. You could make each salesperson’s name that criterion. But you also have to sort and add by sale date, and SUMIF’s three elements (salesperson for each transaction, individual salesperson, and sale amount) would be used up, leaving SUMIF incapable of evaluating sale date. What should you do instead?

Here’s where an apparently ill-suited Excel function, SUMPRODUCT, can help. At first, it may not seem like an ideal fit. SUMPRODUCT’s syntax [SUMPRODUCT(array1,array2,array3…)] is designed to multiply corresponding components in specified arrays, and then calculate the sum of those products. A powerful function, SUMPRODUCT can accommodate up to 30 arrays.

But why is SUMPRODUCT more useful in this case than SUMIF? To answer that question, we need to know more about how SUMPRODUCT works.

Take a look at Exhibit 2. Cells X1 to X3 contain the values 1, 2 and 3. Cells Y1 to Y3 contain 4, 5 and 6. Cells Z1 to Z3 contain 7, 8 and 9. A three-array formula would have this syntax: SUMPRODUCT(X1:X3,Y1:Y3,Z1:Z3), and it would return the value 270. The formula would arrive at this result first by multiplying and second by adding.

Specifically, it would multiply cell X1 (1) by Y1 (4) by Z1 (7) to get 28. It also would multiply row 2 (2 x 5 x 8) to get 80, and row 3 (3 x 6 x 9) to get 162. Then it would add these products (28 x 80 x 162) to get 270.

Let’s see how SUMPRODUCT functionality can accommodate our additional criterion, sale date. Exhibit 3 includes the sales data we used in Exhibit 1, as well as a column that shows the date of each sale.

SUMPRODUCT can report the totals by salesperson by month, as shown in columns F, G and H in Exhibit 3. In addition, it can do so with virtually the same formula in each of the 12 cells that contain a salesperson’s monthly total. To achieve this, we have to employ techniques that aren’t commonly used.

Examine the formula in cell H4, which totals the sales that Alice made in March: =SUMPRODUCT(--($E4=$A$3:$A$15),--(3=MONTH($B$3:$B$15)),($C$3:$C$15)). Notice that this formula contains elements that aren’t in the plain-vanilla SUMPRODUCT syntax shown above.

The first two elements in this formula contain conditions in addition to ranges. E4 (Alice) is a condition to apply to range A3:A15 (salesperson for each transaction), and 3=MONTH (March) is a condition to apply to range B3:B15 (sale date of each transaction). When these two criteria are met, the sale amount in element three, range C3:C15 (sale amount for each transaction), is returned. At the end of the operation, as explained above, SUMPRODUCT sums the products of the rows and reports that number as the result in cell H4—Alice’s total sales in March.

Let’s take a look at what SUMPRODUCT is doing here. We’ve given the formula three elements, but as you’ll note, we added something to the beginning of the first two—double dashes or minus signs. Not well-documented in Excel, double dashes will take the result of a TRUE-FALSE argument and make it a one (1) if TRUE or a zero (0) if FALSE (see sidebar, “Dissecting Double Dashes,” below). This has great implications when you use SUMPRODUCT as a conditional summing tool. In our example formula, the first element is a TRUE-FALSE question. We’ve asked Excel whether Alice’s name matches any in the list. When it goes through the list, Excel wants to tell us that some of the names do match. But with our insertion of double dashes, instead of a list of TRUE-FALSE values, we get ones and zeros, as shown in Exhibit 4.

The second element of our SUMPRODUCT formula poses a similar TRUE-FALSE question. It asks Excel to look at the date, and determine its month. The MONTH() formula will return the number corresponding to the month of a date. In this case, we’re checking to see whether the month is equal to 3, or March. Normally, we’d get a list of TRUE-FALSE arguments; but with our double dashes, once again we get a list of ones and zeros as shown in Exhibit 4.

The final element of our SUMPRODUCT formula is the list of sales figures. Since this is not a TRUE-FALSE comparison and doesn’t contain double dashes, Excel will use the sales figures themselves when it executes the SUMPRODUCT formula.

Since multiplication by zero equals zero, whenever the name doesn’t match Alice’s name, or the month isn’t March, the formula’s result is zero. When Alice’s name matches and the month is March, Excel’s result is the sales amount for that transaction. Added together, these are Alice’s sales for March—a conditional summing using more than one condition. Using absolute cell references, you easily can copy this formula into adjacent cells to quickly produce a table that lists sales by person and month.

Once you’ve familiarized yourself with this variation on SUMPRODUCT, you’ll be able to experiment with its full capacity of up to 30 arrays and tackle even more complex analyses.

J.D. Kern, CPA, is the corporate controller at Management Dynamics in East Rutherford, N.J. His e-mail address is jdkern@managementdynamics.com.

Editor's note: Click here to download this article's sample Excel worksheets. Each of the article's four exhibits is an individual worksheet in a single downloadable Excel file.

---

Dissecting Double Dashes

Let’s take a closer look at the double dashes preceding each of the first two elements in the formula in cell H4 of Exhibit 3. These operators are not inherently related to SUMPRODUCT, but they shape its output to better meet your reporting needs.

When Excel reads a SUMPRODUCT formula, it interprets nonnumeric values as zeros. When SUMPRODUCT compares an individual salesperson’s name to names in the sales transactions, it stores in memory as an intermediate result the value TRUE or the value FALSE, depending on the result of each comparison. Treating these nonnumeric values as zeros causes the final sum to be zero as well. But the insertion of double dashes in the formula in cell H4 returns the value $650, which is the sum of Alice’s two sales in March ($300 + 350).

Here’s why the double dashes didn’t result in a total of zero, and did come up with the right answer. Although the intermediate result of each SUMPRODUCT comparison is TRUE or FALSE, you can make Excel convert those nonnumeric results to numeric values. Because Excel will express the result of a mathematical operation as a number, you can use a simple mathematical process—negation—to convert a TRUE response into a negative one (−1) and to convert a FALSE response into a zero (0). That explains the first dash. The second dash converts the negative ones (−1) into positive ones (1), and it leaves the zeros unchanged. (Neither single nor double plus signs would do the trick.)

If you used the formula in cell H4 without the double dashes, you’d get TRUE values for the name comparisons (E4=A3:A15) in cells A4, A10 and A11, the transaction records containing Alice’s name. All the other cells in that range would return FALSE values. But if you added one dash, you’d get minus one (−1) in the three cells and zero (0) in the other ten. And if you added the second dash, the three cells would contain positive ones (1), and the others would contain zeros.

Following this logic, the second element of the formula in cell H4 would (with double dashes) return zeros (representing FALSE) in cells B3:B8, which describe January and February transactions. It would return positive ones (1, representing TRUE) in cells B9:B15, which describe March transactions.

We’re interested only in rows that have a 1 in column A and a 1 in column B (see Exhibit 4). Only two rows satisfy these conditions: rows 10 and 11, which contain A10=Alice, A11=Alice, B10=3/17/2009, and B11=3/18/2009.

In standard SUMPRODUCT fashion, the formula returns a zero value for every row that contains at least one zero—anything multiplied by zero equals zero. For rows with 1’s in columns A and B, the formula multiplies the corresponding column C value by one. A10 (1) x B10 (1) x C10 ($300) = $300; A11 (1) x B11 (1) x C11 ($350) = $350. Then, after finishing the multiplication, it sums the products ($300 + 350) and accurately returns $650 in cell H4.

Thus, the double dashes have proven their value as useful SUMPRODUCT enhancements. Exhibit 3 illustrates these results.

When you copy the H4 formula (=SUMPRODUCT(--($E4=$A$3:$A$15),--(3=MONTH($B$3:$B$15)),($C$3:$C$15)) to other cells, you’ll have to vary it slightly to accurately calculate each person’s sales for each month. Note that in cell H4’s formula, nearly every address is absolute (containing one $ for the column and another $ for the row). One address ($E4) has an absolute column coordinate ($) and a relative row coordinate (no $) to accommodate the change in salesperson name when the row 4 formula is copied down to rows 5, 6 and 7. The column coordinate in this address remains absolute because the salesperson name criterion is always in column E. For example, the formula in cell G5 for Jim’s total February sales would be =SUMPRODUCT(--($E5=$A$3:$A$15),--(2=MONTH($B$3:$B$15)),($C$3:$C$15)).

Importing Data Into Microsoft Excel 2007 From Databases and Web Sites

Simplify the way you move data into a spreadsheet.

By RAYMAN D. MESERVY, NICHOLAS L. BALL and MARSHALL B. ROMNEY

JUNE 2009

Spreadsheets contain many tools for analyzing and manipulating data. The trouble is much of the data CPAs need to analyze resides in company databases or on the Internet. So, after finding the required data, you may have to cut and paste them into a spreadsheet. This laborious and error-prone process is particularly troublesome if you are obliged to repeat it monthly or more frequently.

Wouldn’t the process be easier and more reliable if you could simply open a spreadsheet, which then would automatically pull in the latest data from a database, and perform your standard analysis? To meet that need, this article will demonstrate not only how to automate data retrieval from a database on your own PC, but also from a database on your company’s network server and from one residing on the Internet.

The following examples will use Excel 2007 and Windows commands. Earlier versions of Excel have similar data access features, but their commands differ greatly from those of Excel 2007. Note that the screenshots in this article are of maximized windows. The appearance of any of the illustrated screens will vary slightly if viewed through windows that are not maximized.

PULLING DATA FROM A LOCAL DATABASE

This process will work with any database stored on your own PC. To illustrate it, we’ll refer to a small Microsoft Access database on a server at Brigham Young University that we’ve made available to readers. Use a Web browser to download it by going to it-research.byu.edu/excel/SalesData.mdb. When you try to do this, one of two things will happen. Either you will be prompted to open or save the database’s files to your computer, or the database will open automatically in Access 2007. In either case, save a copy to your desktop.

If you do open the database, be sure to close it before proceeding. This example assumes the Access database is not open when you use Excel to import its data. Although it is possible to import data from an open Access database, the process differs slightly from the one this article describes.

Refer to Figure 1 as you perform the following steps:

1.a. Open Excel and click on the Data tab.

1.b. Click on From Access in the Get External Data group of icons in the upper left-hand portion of the screen.

1.c. Navigate to your desktop and double-click on the Sales- Data.mdb file. The SalesData.mdb database has only one table. If there were more than one table, you would be prompted to select the table you wish to import.

1.d.An Import Data screen will appear. Click on the OK button to bring the data into Excel.

It’s that easy to import data from a database into a table in your spreadsheet. The spreadsheet will look like Figure 2 except for the sales amounts in cells D2 and D3. As you’ll see below, it is easy to update the data in your spreadsheet to reflect any additions, deletions or changes to information in the database.

At this point, you can manipulate the data in the spreadsheet and perform any analysis you wish. You also can click on one of the dropdown arrows in row 1 to sort the information in the table by that particular column. Save this spreadsheet to your desktop, using the name Sales1.xlsx.

The Sales1 spreadsheet is a snapshot of the information stored in the Access database at the time you imported the data. Since company data are constantly changing, you periodically will need to update the data in the spreadsheet. To demonstrate how to update the Sales1 spreadsheet, we will modify the database data and refresh the data in the spreadsheet to show that the database changes are reflected in the spreadsheet. Follow these steps:

2.a. Close the Sales1 spreadsheet. Note that if you don’t close the spreadsheet before opening the database to modify its data, the database will open as a read-only file. That will prevent you from saving any changes you make to the data in the database.

2.b.Open the SalesData.mdb database.

2.c. Open the Sales table by double-clicking on it on the left of the Access screen.

2.d.Change the first two amounts in the Sales table to “2000” and “3000” from “20” and “30,” respectively.

2.e. Close the database.

2.f. Open the Sales1.xlsx spreadsheet.

2.g. Select the Data tab and click the Refresh All icon. See Figure 2. If you get a security warning because you are connecting to a database, simply click on OK.

As you can tell by looking at the Amount column, the data in the spreadsheet have been updated. You also may refresh the spreadsheet data by right-clicking on any cell in the spreadsheet data table and selecting Refresh on the popup menu. Note that if you modify the data in the spreadsheet, the information in the database will not change. Also, if you move the database to a new location, you will have to specify that new location when you are prompted to during the refresh process.

In addition, you can configure the spreadsheet to automatically obtain fresh data from the database every time you open the spreadsheet. Refer to Figure 3 as you do the following:

3.a. Select a cell in the range of the imported data in the spreadsheet.

3.b. Select the Data tab.

3.c. Click the arrow next to Refresh All and select Connection Properties. The Connection Properties window will open. (Figure 3)

3.d.Under Refresh control, check the box next to Refresh data when opening the file.

3.e. The next time you open the spreadsheet, you may need to click on Security Warning Options and select Enable this content.

From now on, whenever you open the spreadsheet, it will import fresh data from the database.

TAKE ONLY THE DATA YOU NEED

One of Excel’s more useful tools is the PivotTable, which enables you to summarize large amounts of data for easier analysis. Occasionally, you may want to analyze the data from a database table or query in a PivotTable without storing the unsummarized data in the spreadsheet. Refer to Figure 4 as you take the following actions:

4.a. Open a new spreadsheet.

4.b. Select the Insert tab, go to the Tables section, and click on PivotTable.

4.c. Select Use an external data source and click on Choose Connection.

4.d.Click on Browse for More at the bottom left of the Existing Connections popup screen.

4.e. Find the SalesData.mdb database on your desktop, open it by double-clicking on it, and select OK.

4.f. Create the PivotTable by checking the Sales Rep box and then the Amount box. Drag Location to Column Labels.

4.g. Save the spreadsheet as Sales2.

Remember that if the information in the database has changed, you will have to refresh it in Excel.

CONNECTING TO YOUR COMPANY’S DATABASE

Earlier we demonstrated how to connect to an Access database that is saved on your personal computer. You are likely the only user of that database. But you can also use Excel to import data from many types of databases, including those designed to support thousands of simultaneous users, thanks to Open Database Connectivity (ODBC), a protocol that most database management systems observe. To use ODBC with Excel, you must set up a connection file, which will enable Excel to download data from an ODBC-compliant database.

You can import data from your company’s database if its administrator provides you with the appropriate connection ports, user IDs and passwords. Usually, this is relatively simple and takes just a couple of minutes. Once you connect to the database, depending on the rights the database administrator has provided, you can query the database’s tables. Because you will export the database data to Excel and perform your data analysis and manipulation in your spreadsheet, your actions will not affect the data in the database.

To illustrate this, we will set up a sample ODBC Internet-based connection to a structured query language (SQL) server database. Refer to Figures 5 through 8 as you do the following:

5.a. Click on the Windows Start button, select Control Panel (use the Classic View), select Administrative Tools, and open Data Sources (ODBC). If you do not find Data Sources in Administrative Tools, consult your system administrator. You may be able to access the ODBC Data Source Administrator popup screen by opening a file, C:\Windows\system32\odbcad32.exe, which brings up the ODBC Data Source Administrator screen shown in Figure 5.

5.b.Set up the connection by selecting the File DSN tab, clicking on Add (Figure 5), selecting the SQL Server driver, and clicking on Next. Note that you may need to scroll through many driver options before finding the SQL Server driver choice.

5.c. Save this connection with a name you would like to use for the data source. In the example, we used accountancy. Click on Next and then on Finish.

5.d.For Description, type in accountancy or any description you want. (Figure 6)

5.e. Enter 128.187.60.102 as the server. If you were connecting to a server with an Internet domain name, you could enter that Internet domain name instead of the network IP address of the server as described in this step. Click on Next.

5.f. Click on With SQL Server authentication using a login ID and password entered by the user. (See Figure 7.)

5.g. Enter accountancy as the login ID and demo as the password. Click on Client Configuration.

5.h. Uncheck Dynamically determine port, and identify the port as 5901. (See Figure 8.) Then click on OKand on Next.

5.i. Click on Change the default database to, enter websql, and click on Next and on Finish. You can click on Test Data Source to ensure you have successfully connected to the database. Finish by clicking on the next two OK buttons you see.

Now that you have set up a connection, you can import the database data into Excel. Do the following:

6.a. Open a new Excel spreadsheet, and save it with the name accountancy.

6.b.On the Data tab, click on Existing Connections from the Get External Data group of commands.

6.c. Under Connection files on this computer, double-click on accountancy (or whatever you named the connection in step 5.d). In some cases, the operating system configuration may make it necessary to browse for the accountancy.dsn file. Usually, it is located at C:\ProgramFiles\Common Files\ODBC\Data Sources\.

6.d.Enter accountancy as the login ID and demo as the password, and click on OK.

6.e. Select the table or query that you want to connect to. (See Figure 9.) Try opening product, whose owner is Accountant. It should be near the top of the list. You may also refresh the data or open them directly into a PivotTable.

CAPTURING ONLINE DATA

Excel can also retrieve information from a Web page. First, create a new workbook in Excel, and save it as Fortune100. The first 100 companies in the Fortune 1000 list for 2008 can be found at money.cnn.com/magazines/fortune/fortune500/2008/full_list/.

Using the above address, you can import data from the Web directly into Excel. Refer to Figure 10 as you do the following:

7.a. Open the Fortune100 workbook you just created in Excel, and select the Data tab.

7.b. Click on From Web in the Get External Data group of icons.

7.c. In the Address field, type in the above Internet address, and click on Go.

7.d.Excel will display a copy of the target Web page. Notice the yellow arrows on the left side of the New Web Query window. Clicking on the yellow arrows identifies which portion(s) of the Web site will be downloaded into the spreadsheet. For those sections you choose to download from, a green check mark will replace the yellow arrow, and the data to be downloaded will be highlighted. Click on the yellow arrow nearest to the Rank and Company labels above Wal-Mart Stores, the first company listed. This will highlight and download company names, annual sales and profits.

7.e. Click on Import to begin the download. Click on OK to download the data into the current worksheet.

You can refresh these data by following the process described earlier.

Once you’ve experimented with these procedures, you’ll be comfortable trying them out in actual practice. And after you configure your spreadsheets to automatically download new data, you’ll be better able to focus on your primary objective: interpreting the latest data—wherever they originate.

Rayman D. Meservy, Ph.D., and Nicholas L. Ball, Ph.D., are assistant professors and Marshall B. Romney, CPA, Ph.D., CFE, is a full professor, all on the faculty of the Information Systems Department of Brigham Young University’s Marriott School of Management in Provo, Utah. Their e-mail addresses, respectively, are meservy@byu.edu, nick_ball@byu.edu and mbr@byu.edu.

AICPA RESOURCES

JofA article

For users of Excel 2003: “Click … and the Database Loads Into Excel,” Sept. 06, page 53