Array Formulas in Excel

Hello all. So sorry I haven't been in touch over the past week. I've been extremely busy planning for the wedding and taking care of other details. Anyway, I wanted to pass along some great information (at least, great to me): I've been included in an article on accounting blogs! You might find it useful as many other accounting blogs are on there such as tax, management accounting, etc.

Anyway, on to today's blog. A few weeks back, a friend of mine had asked about using the SUMPRODUCT function, and I recommended that he use array formulas. In the comments to that blog, I briefly discussed array formulas and I'd like to expand on their usage today. Essentially, you will be interested in using array formulas (CSE's for "Ctrl-Shift-Enter" formulas) when you need to perform extremely complicated formulas on numerous cells/rows and then have a summary function to tie it all together. For example, this blog discussed the SUMIF and COUNTIF functions. So you know how to use them, but what if you wanted an "AVERAGEIF"? No such function exists in Excel, but CSE's will help us remedy this. The best way I can describe starting CSE's is by the summary function you want to utilize. In our example, we want to average values if a certain criteria is met. So our formula begins:

=AVERAGE(

The next step is to define the criteria. Let's assume we're interested, in averaging sales by state. Now realize that this is a somewhat contrived example. The easiest way to average across states is to do "=SUMIF(State_Col, "PA", Sales_Col)/COUNTIF(State_Col, "PA")". That is, sum the sales by state and then divide by the number of entries identified by COUNTIF. But, it will help us get started. So, to limit on state, we need an "IF" statement to limit our records. So, we simply add the if:

=AVERAGE(IF(

Now it gets a little complicated. Essentially, the next part of the formula will define whether or not the number should be included in the CSE. We want to examine each cell in the range of state names. Lets assume that the states are in cells A1:A10 and the corresponding sales figures are in cells B1:B10. The "IF" part of the formula wants to know what to examine, so we enter the state cells:

=AVERAGE(IF(A1:A10

Since we want to see if it's equal to a particular value (Pennsylvania, for example), we just enter that:

=AVERAGE(IF(A1:10="PA",

The comma is added so that we know what we want to include as the value. We want the sales figures, so we'll add:

=AVERAGE(IF(A1:A10="PA", B1:B10))

It's that simple so far. The last step (after you add the braces as shown above) is to hit "Ctrl-Shift-Enter" to complete the formula. If you don't do the last step, the formula will not work properly. But this will average sales for Pennsylvania as we wished. You may be wondering, what if I wanted to sum New York or Pennsylvania? To do that, you have to modify the "IF" statement. You'll have to add:

=AVERAGE(IF((A1:A10="PA")+(A1:A10="NY"),B1:B10))

You'll notice that I added another set of parentheticals and a "+" (plus) sign. This says "If the state is PA OR if the state is NY, show the sales figure". You'll have to consult the help files to find out why we use a plus sign, but know that it works. If you wanted PA sales over $500, you'd have to use a "*" (multiplication) sign as follows:

=AVERAGE(IF((A1:A10="PA")*(B1:B10>500),B1:B10))

We limited based on the state as well as the size of the sale. You can get as complicated as you like, stringing results together like this. Remember, though, that you ALWAYS have to finish your formulas by hitting "Ctrl-Shift-Enter".

For a further discussion of array formulas, check out this article that will give you some more insight. Thanks for reading!

Connect to the Web from Excel

Did you know that you can connect to the web from Excel? This can vastly improve your life at times, and not do much at all other times. Depending on how easily identifiable the information is, sometimes Excel can handle the challenge while other times it can not. To use this nifty little feature, go to "Data | Import External Data | New Web Query...". Under the import external data menu item, you could also choose to import text files or connect to a database and grab data from there. For this post, we'll stick to the web.

You might wonder, "Why do I need this feature in Excel?". As of now, it's not tremendously useful in my opinion because most web sites are designed for your viewing pleasure - not for data transfer. This is, however, changing rapidly with the growing use of XML (take, for instance, the XBRL initiative sponsored by powerhouses such as PwC and Deloitte). Therefore, it will eventually become very useful I'm sure. As one example of where you might use this technology, let's assume that you keep track of your portfolio in Excel. You're looking to download stock prices for certain tickers every month, so you want to create a macro to do it for you. Don't know about macros? Read here. Now that we have that covered, lets try to create our macro.

To start, we'll begin by simply examining the Yahoo! finance site, which is the easiest site to accomplish this task (as far as I know). Instead of using your browser, however, we'll use Excel. Go to "Data | Import External Data | New Web Query". From here, type in http://finance.yahoo.com and navigate to the input box asking you for a ticker symbol. Enter whatever symbol you like (perhaps PNC) and hit enter. From here, you'll have to click on the link on the left hand side of the page for "Historical Prices" and you can change the dates as you see fit. Excel will then open up the page with the quotes, which you can click on the yellow arrow to tell Excel that you want to download that table. Viola! Excel will import the table for you and you can change and / or manipulate the data! You can repeat this procedure for any website where you typically download information. To automate the task, simply record a macro before you do it and it will be available for next month. If you get clever, you can manipulate the VBA code and get the dates to change to whatever you want as well. For a primer on VBA, check out this post.

Anyway, you can use the previous procedure to create some very sophisticated macros. Alternatively, I've created an Excel add-in to download prices for you that you can get here. Try it out - if it doesn't work, write me an email or post a comment and I'll be sure to try and fix it. It was started by creating a macro as recommended in the previous paragraph, and then some heavy lifting was done to make it more flexible and user friendly. To use the add-in, download the file and save it somewhere on your computer. From Excel, go to "Tools | Add ins..." and click "Browse" ... then navigate to wherever you saved the file, click on it and hit "OK". Now you'll have to get a little creative - if you want the tool available whenever you want, right-click on a toolbar and click "Customize". Scroll down to the "Macros" section on the "Commands" tab and drag the custom button to some toolbar (whatever one you want). It should be a smiley face. Once it's placed on the toolbar, right click on the smiley face and click "Assign Macro". The macro name is "Show_Yahoo_Form" and hit enter. Close all dialog boxes, and then you can click on that button whenever you want to download stock prices! If you only want to try it out once, instead of adding a toolbar button just hit Alt-F8 and type "Show_Yahoo_Form" and the form will pop up. Good luck!

Scenarios - Providing for Clean "What-if" Analysis

Have you ever developed a spreadsheet whereby you wanted to analyze a few scenarios basically revolving around the same thing (i.e., just running different numbers)? For example, maybe you're analyzing an investment, but you're not quite sure what rate of return you'll get in the upcoming period. Or perhaps you're applying for a loan, and know that you'll get an interest rate between 5.5% and 6.5% (maybe for a new home ... which you could learn how to amortize by reading this article). Anyway, you need to see if you can afford the interest at the different rates, what your cash flow will be if the investment appreciates by 7% instead of 10%, etc. Instead of manually changing everything over and over, you should learn to use scenarios.

Instead of me writing an article, however, I'd like to point you to a great article I read some time ago (JoA): Add Muscle to What-if Analysis. If you have questions, I'd be happy to answer them - but I think the article is really well written so I won't rehash what he said here. Once you complete that article, if you found it interesting there was a follow up article here which you can also read for more information. Good luck!

Become a Power Internet User with Firefox

I know this is a little bit of a step away from my normal posts, but hear me out. If you have not yet heard about Mozilla's Firefox browser (which I would find very hard to believe, but I suppose it's possible), I'll tell you about it here and how you can harness the power of a better internet by using it. And in case you weren't aware, Firefox is COMPLETELY free! No holds barred.

To begin, I find that one of the most useful features available is the built in support of RSS feeds. What are RSS feeds? Essentially, they contact web sites that you designate (such as this blog!) and download the latest headers to your bookmarks folder for you to review whenever you like. If you think you'd enjoy the article (as evidenced by the article title), you click on the feed to go directly to it. If not, you do nothing. Getting feeds is fairly easy - many of the sites you visit have them enabled by default with a little orange box that says "RSS". In Firefox, you can simply click on the box and select "Subscribe to..." and that's it. It will ask you where to store the feed, and you can view it whenever you want thereafter. Sites like the Economist, The Wall Street Journal and others have created feeds - just go out and get them. Alternatively, you can search sites like NewsIsFree.com to find sites with feeds you might not know about.

The next topic of discussion is the quickness with which you can navigate if you learn the "quick keys". For example, hitting Ctrl-L will take you to the address bar where you can enter your next destination on the web. If you use bookmarks, you'll soon learn that you can add "keywords" that are essentially short-cuts to the website. For example, I have a bookmark for one of my favorite sites, Sourceforge.net (a site with tons of open source software). After I initially created the bookmark, I accessed the properties of the bookmark (by right clicking on it) and added the keyword "sf". Now, when I want to access the site I hit "Ctrl-L sf " and I'm there. Very useful.

You can also add "quick-searches" which are similar to shortcuts to websites, but they're actually shortcuts to search boxes. So, I go to the SEC's website and find the spot where I can search for company filings. On this page, I want to be able to quickly search company's filings so I right click in the "Company Name" box and choose "Add a Keyword for this search...", and give it a keyword of "sec". Now, whenever I want to search for a company filing, I hit "Ctrl-L sec company_name" and it will automatically search for whatever company I specify! This is much more efficient than going to the website, selecting the box and typing the company name, then hitting enter or clicking the "submit" button. I also have quick searches on Amazon, dictionary.com, etc.

There is also a built in search toolbar. To quickly access that, you'd hit Ctrl-K and type whatever you're looking for ... and it searches any search engine you specify. By default, I keep mine on Google, but Yahoo!, Amazon, eBay and others are available as well.

Finally, one last item to whet your appetite is the ability to quickly search a document for a word or link. To do that, you'd simply hit the backslash key ("/") followed by whatever you're looking for in the page. No more Ctrl-F to get some hideous box that gets in your way, blocks your view and makes you do so much work for something so simple. Want to find the next word that matches your criteria? A simple F3 does the trick. This search technique is extremely powerful when searching long documents (like a 10-K) when you may want to search, then read for a little and search again, etc.

Hopefully this article gave you a reason to at least check out the best browser on the block. Once you use it, you'll never go back. Especially after tabbed browsing (Ctrl-T) which allows you to open up a new site without opening a new window, extensions that allow you to enhance the interface or functionality of the browser, improved security and the host of other things this browser can do that Internet Exploder is no where near doing yet. Check out the download, and then read the website when you want to become a power internet user.

An Amortization Table Using VBA - Our First Steps

Well, I hope everyone had a great 4th of July this past weekend! I had a fantastic weekend, and this week is turning out to feel fairly short - so that's always nice. Anyway, let's get to the business at hand. I wanted to pass along a file to everyone that I created some time ago, and was recently used to create an amortization table for a project I was doing. Actually, this is a slightly modified version with lots of comments and no error checking. For the original (and more robust) file, see here. The first is for learning, the second is for using. The file is very simple, and you should not be intimidated; it simply asks you for a few pieces of information, you press a button and voila! an amortization table is created. I wanted to share this with you because it demonstrates a few techniques we've been discussing over the past month (named ranges and macros) as well as a few other items that I thought you might find interesting (VBA and data validation).

Lets start with the ones we already know. If you open the file and make cell B4 the active cell, you'll note that in the upper left hand corner of your screen there is the word "Principal" in your menu bar; this was done via named ranges. Furthermore, if you examine the formula in cell B10, you'll notice that there is a (very longwinded) formula containing references to several named ranges: Principal, Int_Rate, and n. This was done to help clarify the formula as well as to make it easier to examine later if I forget how to calculate a loan payment. To understand what each of the named ranges refers to, simply click Ctrl-F3 (if this doesn't work, go to "Tools | Protection | Unprotect Sheet" and then try it again). You can examine what each named range refers to ... you could alternatively pick a cell (again - beware of protection) and type "=Int_Rate" (or whichever item you want to see) and the value will be displayed for you after you hit Enter. Try it out.

Now, onto the next "easy" topic (before we touch on VBA) - data validation. You'll notice if you try to enter a negative interest rate, the spreadsheet won't allow it. Or if you tried to enter a string such as "Kevin" into the Pymt Freq, it also won't allow it. The underpinnings of this clever little trick lies in data validation. To understand what is allowed and not allowed, pick a cell and go to "Data | Validation" (again, unprotect if necessary). You'll easily follow what is valid information and what is not. Use this technique whenever you update something on a continual basis (weekly, monthly, etc.) and only certain values are allowed.

Finally, onto the real mean-and-potatoes of the spreadsheet - VBA. If you have no interest in VBA, feel free to stop reading here :) Otherwise, hit Alt-F11 and find the name of the spreadsheet in the data pane to the left (my file is called "Amort_BareBones.xls"). Within that tree, you will find a section called "Modules" - double click on the module named "GenerateSched". What you find in the middle of the screen is all VBA code that dynamically creates the amortization schedule for you. But note, that much of what was done here was accomplished via macros, and then expanded with VBA. To understand the code, simply read the comments - I tried to put a ton in so you could see what was going on. Notice, however, that there are only 8 lines of code (!), and the rest are comments. One of the lines of code isn't even strictly necessary (turning off the cut / copy mode). If you downloaded the more robust file, there are not as many comments, a lot more code, and you will probably not understand much of it if you have little or no experience with VBA. But feel free to try it out, experiment, delete, add, etc. You can always close without saving if you mess up :)

Hopefully the comments have helped you out, but if you have questions, post comments or send me an email. Comments are much preferred, however, because then everyone gets to see replies and / or questions that will hopefully help them learn. Good luck!

First Steps w/ SQL (MS Access)

In a recent post, I discussed a way to use Excel's SUMIF and COUNTIF formula. As another reader asked therein, these formulas are not quite powerful enough to handle multiple criteria. My suggestion to him (and to you as well) was to use a SQL based solution such as Microsoft Access (or an open source alternative such as MySQL, PostgreSQL or SQLite). Note, however, that the open source alternatives while free, do require a larger investment of time and energy in learning the ins and outs of the software.

Quickly, you might be interested in using a SQL based solution when the data is structured (i.e., all columns and rows - no spaces per se, typically no "custom formatting", etc.). Direct extractions from systems such as SAP or Oracle are perfect, as those systems ARE databases themselves - and therefore produce data ready to be used by most other dB systems if you export them correctly (text delimited files). If you can get the data in this format, you can do some extremely powerful things with a database such as summing across categories by date for a very explicit set of circumstances (e.g., within a date range, for a specific sales region, for a particular set of customers, etc.).

Getting the data into Access is pretty simple, as long as your data is (again) in columns and rows with no "custom formatting". You'd simply create a new database (you have to create and save a dB before you can proceed), then go to the "Tables" tab (on the left) and finally hit "New | Import Table" ... the directions from there will be data specific, but if you've ever imported data into Excel it is very similar. If you have problems, feel free to email me and I will try to help.

Now that you have your data in the dB, you can go to the "Queries" tab (again, on the left) and hit "New | Design View". This will bring up a dialog asking you what tables you want to query. Choose the table you just imported, and then click "Close". In the grid, you can drag fields from the table to the grid to include them in the query. To filter (e.g. on specific dates), enter them into the "Criteria" section of the grid for the field you are querying on. If you want to group by (e.g., sales region), right-click anywhere in the grid and click on "Totals". This will bring up another available row in the grid ("Total:") where you can do "GROUP BY" queries or "SUM" queries, "AVG" queries, etc. Alternatively, you can use one of the "Wizards" instead of the data grid. Either way, it's much simpler than creating formulas in Excel once you get the hang of it, and much more powerful.

I know this was an overly simplistic post and you most likely have tons of questions. My suggestion would be to simply open up the program and try it out. Experimenting and using the online help system can take you very far. I'd be happy to write about specific questions, but I could go on for another 5 pages just on the basics - and I'm sure you don't want that now ;) Good luck!