Excel Blog

Coin Tosses and Stock Price Charts

2008-06-14T06:18:00-07:00

Prior to the 1960’s, most investors believed that future securities prices could be predicted (and that great riches were to be had) if only they could discover the secret. Many investors still believe this today, despite much evidence that suggests that they would be best served by simply owning the entire market (investing in index funds) rather than trying to pick individual stocks. The efficient markets hypothesis (EMH) essentially states that techniques such as fundamental and technical analysis cannot be used to consistently earn excess profits in the long run. The EMH began with the observation that changes in securities prices appear to follow a random walk (technically, geometric Brownian motion with a positive drift). The random walk hypothesis was first proposed by mathematician Louis Bachelier in his doctoral thesis in 1900, and then promptly forgotten. Further evidence of randomness was occasionally reported by others, most famously by the statistician M.G. Kendall in 1953. Many others followed in the late 1950’s and 1960’s. One of the best-known stories regarding the randomness of changes in stock prices is told by Burton Malkiel in A Random Walk Down Wall Street (a fascinating practitioner-oriented book now in its ninth edition). In that book he tells of having fooled a friend, who was a committed technical analyst, by showing him a "stock chart" that was generated by coin tosses, rather than actual stock prices. Apparently, the friend was quite interested in learning the name of the stock. The purpose of this post is not to debate market efficiency (so please don’t leave comments on that subject — it is almost a religious debate among some), or to even state that the EMH is correct. Instead, as this blog is focused on Excel, it is to demonstrate how we can simulate coin tosses and use those simulated tosses to generate "stock charts" of the kind that Malkiel discussed. Generating Coin Tosses with Rand() Our first task is to generate a sufficient number of coin tosses to create the chart. There are many ways that we might go about doing so, and I will first show the easy way. Realize that a coin toss can be represented by a binary variable, where 0 is tails and 1 is heads. This is a binomially distributed variable with a probability of a "success" of 50% (p = 0.5), assuming that the coin is fair. We could use the random number generator from the Analysis ToolPak add-in to generate these tosses, but it suits my purposes better to create a formula: =Round(Rand(),0) The Rand() function generates a uniformly distributed random number between 0 and 1. I apply the Round() function to the result so that if the random number is 0.5 or greater it will get converted to a 1. If it is less than 0.5 it will get converted to a 0. We will treat a 1 as a head, and a 0 as a tail. Furthermore, if the result is a head the stock will go up, and if it is a tail then the stock price will decline. Now, we know that stock prices tend to rise over time so we will say that daily price changes are slightly asymmetric. Specifically, let’s say that the price will rise by 0.25% if a head is tossed, or fall by 0.20% if a tail is tossed for a given day. Also, we need to set a starting price, so let’s choose $100. Create a worksheet that looks like the one in the picture below: Enter the data as shown in A1:B5. In B6, just link to the beginning price with the formula: =B3. In A7 we will calculate our first coin toss with the formula: =Round(Rand(),0) In B7 we will calculate the new stock price with an IF() statement: =IF(A7=1,B6*(1+$B$1),B6*(1-$B$2)) Finally, copy the formulas from A7:B7 down the sheet to row 156. That will give us 150 coin tosses and 151 days of stock price "history." We can now create a line chart of the resulting stock prices: Notice that I have marked a few typical technical chart patterns that make an appearance in the coin flip-generated chart. There is a double top, a resistance level, and an up trend. Press F9 to recalculate the sheet and you will get a fresh round of coin tosses and a new chart. If you do that enough times, you can see all of the chart patterns that are discussed in such books as Technical Analysis of Stock Trends by Edwards and Magee (often called the Bible of technical chart patterns). A More Realistic Chart The chart generated above is perfectly functional and makes the point, but it doesn’t look as realistic as it could. There are at least two improvements that could be made: Use a more realistic model for price evolution over time. Generate a High-Low-Close chart instead of a simple line chart. Typically, stock prices are simulated using a stochastic process known as geometric Brownian motion. Using this model, we can generate returns by using the following formula: That formula simply says that the change in the stock price is equal to the mean change per unit of time (known as the drift) plus a random shock. The shock is simply the standard deviation of the returns times a standard normal deviate (mean of 0, standard deviation of 1). If the standard deviation is 0, then the stock price would simply increase by the amount of the drift in each period. So it is the second term that determines the random up and down fluctuations in the price. We can calculate the next stock price by simply multiplying the previous price by one plus the result of that formula. I no longer need the coin toss metaphor, but I’m going to keep it. It doesn’t hurt anything, and it makes the demonstration more interesting. So, the closing price will go up or down by the absolute value of the result of the Brownian motion equation. For heads the price will go up, for tails it will go down. Note that the coin toss is still determined by generating a uniformly distributed random number as done originally. To generate the high and low prices for the day, I use the same formula. However, the base price is the day’s closing price instead of the previous day’s price. This way, I can tie the high and low to the same day’s close. This also allows me to make sure that the high price is always greater than the close, and the low price is always less than the close. Still, the amounts by which the high and low deviate from the close are determined randomly using the same mean and standard deviation. To do all of this requires that I generate four random numbers for each day. The first is for the coin toss. The other three are the standard normal variables that get fed into the Brownian motion formula to determine the high, low, and close for the day. I use VBA to generate these numbers and to change the scale of the Y axis in the HLC chart so that it looks reasonably good. The code is quite straightforward, so I won’t cover it here. If you are interested, you can download the example workbook (Excel 2003 version) and examine the code by opening the VBA editor (Alt-F11). Note that the random numbers are in columns A:D, which are hidden from view. Simply unhide those columns to see the output. The picture below shows the resulting worksheet. Note that there are two buttons on the worksheet. Clicking the "Flip Coins" button will cause new coin tosses to be regenerated and the new values for the chart to be calculated. The "Copy Chart to Clipboard" button does exactly that. The purpose of that button is to make it easy to capture a particularly interesting chart so that you can show it to your technical analyst friends just like Malkiel did! The workbook discussed in this post is available in Excel 2007 format and Excel 2003 format.

Modifying the Ribbon in Excel 2007

2008-03-27T08:52:00-07:00

I have recently found a need to modify the Ribbon interface in Excel 2007. I thought it would be much harder than it actually is. Rather than going through a tutorial of my own, I thought that I would post some excellent Ribbon modification tools and tutorials. Before doing this, you need to understand that Office 2007 files are really just archives (.zip files) of several XML files. So, if you want to explore the file you can just change the file extension to .zip and then extract the files from the archive. Windows Explorer can do this. Open Windows Explorer, find your file, change the extension, and then right-click it and choose Extract All from the menu. You will also need to know the names of the controls on the Ribbon, and what type of control they are, in order to change them. Fortunately, Microsoft provides a list of all of them in a file available on Jensen Harris’ blog or on this Microsoft Download Center page. Ribbon Modification Tools I’m aware of three tools that make modifying the Ribbon very easy. Of course, you can do it manually if you know how to to insert a custom XML file into an Excel 2007 file, and how to set the relationships properly. However, these tools will make your life much easier: The Office 2007 Custom UI (free!) editor from OpenXMLDeveloper.org. This is a fairly barebones tool, but it is the one that I’m using. It is basically a text editor where you type in your XML (known as RibbonX code) to apply to the Ribbon. To use it, just open an existing Excel 2007 file (it can be a blank file that you just saved, or even an Excel 2007 template file so that you can reuse it for many other files) and then enter the XML. When you save the file, the Custom UI editor will save the changes into the file and make sure that the relationships (the .rels files inside the Excel file) are set correctly. It will also check your XML to make sure that it is valid. Mike Alexander has posted a worksheet-driven ribbon customizer (free!) on his blog at DataPig Technologies (you’ve got to love the name of his company). This is a very easy to use Excel 2007 workbook. Download it, fill in a few cells, and your Ribbon will be modified automatically. This is probably the easiest way to get started. Ribbon Customizer is available as a free starter edition or in a $29.99 Professional version. I haven’t yet tried this one, but it looks to be excellent and very easy to use. With the free trial, you really can’t go wrong. Ribbon Modification Tutorials When I first looked at modifying the Excel 2007 user-interface, I quickly found some tutorials on the Microsoft Developer Network (MSDN). These tutorials provide some good information that you should eventually read, but it seems to be aimed primarily at professional developers using Visual Studio Tools for Office (VSTO). The very best tutorials that I have found were written by Nick Hodge over at Excel User Group. Part 1 of his tutorial gives some good information about the new XML-based file format, and then parts 2 and 3 dive into customizing the Ribbon using the CustomUI editor that I mentioned above. Part 4 gets into some of the other controls that you can use. Supposedly, there is a part 5 coming someday to wrap up the series. These tutorials are very readable and I highly recommend them Ron de Bruin has a shorter tutorial with some downloadable examples available at his site. Books that Cover RibbonX There are at least three books that I know of that are dedicated to, or at least cover, the Ribbon: John Walkenbach has updated his “Power Programming” book to Excel 2007. Walkenbach has written several great books on Excel over the years, and Excel 2007 Power Programming with VBA is no exception. This book is a must have for anybody who writes Excel VBA. Chapter 22 covers working with the Ribbon. I very happily own this edition, as well as the last several. RibbonX: Customizing the Office 2007 Ribbon is written by three Microsoft MVPs and looks to be very good. I’ll buy this one soon. RibbonX For Dummies is also available, and it is dirt-cheap ($6.00) at Amazon as of this writing. I hope that you find these resources to be useful. Customizing the Ribbon actually turns out to be fairly painless once you understand how to do it.

Analyze Google AdSense Data Using Pivot Tables

2008-03-16T08:59:00-07:00

Webmasters who use Google’s AdSense service to place ads on their site know that the analytical tools that AdSense supplies are pretty weak. Fortunately, it is easy to get report data from the site and into Excel where we can use all of the analytical tools that Excel provides. The purpose of this post is to demonstrate how webmasters can use PivotTables to analyze AdSense data. There are many reports that you can create in AdSense, and they are all candidates for analysis within Excel, but I will be demonstrating how to use pivot tables to analyze the "This month, by day" report that is standard. PivotTables are an excellent tool for slicing and dicing data, but many Excel users aren’t aware of them. If you aren’t familiar with them then I would suggest that you check out the excellent Pivot Table Data Crunching for Microsoft Office Excel 2007 (or the previous edition for older versions of Excel) by Bill Jelen and Michael Alexander. Hopefully, this tutorial will get you started and let you see the potential for pivot tables. The instructions given here will apply to Excel 2007, though I will give enough information for you to implement the worksheets in Excel 2003 and earlier. The graphics shown in this post may be hard to read because I have to size them to fit the space available. You can download the example spreadsheet (Excel 2003 version) and follow along. Goals of this Project The goal of this project is very simple. I want to be able to analyze AdSense statistics so that I can see the performance of a website by month, day of week, week of month, etc. For example, I may want to know how well the website performs on Mondays in the first half of the winter months. The PivotTables developed here will do that and more. Please note that I am not an expert in Web Analytics, AdSense, or webmastering. So, it is entirely possible that I am missing some obvious ideas. If so, please leave a comment and let me know. Getting the AdSense Report Data As noted above, you can get a monthly report that shows the date, number of page impressions, number of clicks, the page click-through rate (CTR), the page eCPM (earnings per thousand impressions), and total earnings. The picture below shows that report for the first five days of March: Take particular note of the CSV link in the upper-right corner of the picture. That link allows you to easily download and open the report in Excel as a .csv (comma-separate values) file. So, the first step in our exercise is to grab the data from your account and get it into Excel. Log on to AdSense and then generate the "This month, by day" report. You will probably want more than just this month’s results, so you can change the date range on the Advanced Reports page. When the report is displayed, click the CSV link to open the data in Excel. In the future, you can run this report once a month and just paste the new data at the bottom of your spreadsheet. The way that we are going to set up the worksheets using Tables (Lists in prior versions) means that the analysis will automagically include the new data without any extra work. Setting Up the Worksheet Select all of the data and convert it to a table. In Excel 2007, click on the Insert tab and choose Table. (In Excel 2003, click the Data Menu and then choose List --> Create List.) You will now have a Table with an AutoFilter on the headers. While the AutoFilter can be useful in its own right, the best things about using a Table are: It will automatically expand when you add data at the bottom. This means that any formulas, charts, or PivotTables that reference data within the Table will automatically include the new data. Formulas that are entered into a column within the table are automatically copied down the entire column, and are filled in when new data is added. We can create formulas that reference data in the table using syntax like this: Table4[[#This Row],[Date]]. That formula gets the date from the current row in Table4. Now that we have defined the Table, we are ready to add some formulas that will be helpful when we create the PivotTables. Calculate EPC We will start by adding a formula to calculate EPC (earnings per click). Since we are working with daily data, this is actually the average EPC for each day. To calculate EPC, we simply divide the days total earnings by the number of clicks. In G1 type EPC as the column heading. Notice that the Table expands to the right to include the new column. Now, in G2 type = and then click on F2, type a /, and then click on C2. Entering the formula in this way will cause Excel to create the formula using Table references. Your formula should be: =Table4[[#This Row],[Earnings]]/Table4[[#This Row],[Clicks]] and it should automatically fill down the entire column. If it doesn’t, then you should see a SmartTag that offers to fill down for you. Break Down the Date Next, we will break the date into its components (Year, Month, Day). This will allow the PivotTable to aggregate the data based on those time units. In H1 enter Year, in I1 type Month, and in J1 type Day. Now, in H2 add the following formula: =YEAR(Table4[[#This Row],[Date]]) That will extract the year from the date column in the table. The formulas for the Month and Day columns will be similar, but we need to set up a couple of lookup tables. Realize that the Month() and WeekDay() functions return numeric results. For example, if the month is June then the Month() function will return 6. Similarly, the WeekDay() function returns a number (1 through 7) indicating the day of the week. We want to convert the month numbers into month names, and the day of week numbers into the names of the days. In P2:P8 enter a series from 1 to 7, and in Q1:Q8 enter the day names starting with Sunday. Create a similar list in S2:T13 with the numbers 1 to 12 in column S, and the month names in column T. We will now use the VLookup() function to return the name of the month and the name of the weekday. Here are the formulas for these columns in the Table: I2: =VLOOKUP(MONTH(Table4[[#This Row],[Date]]),$S$2:$T$13,2) J2: =VLOOKUP(WEEKDAY(Table4[[#This Row],[Date]],1),$P$2:$Q$8,2) Again, those formulas should be automatically copied down to every row in the table. You should now have the appropriate month names and day of week names in columns I and J. Calculate Day of Month and Week of Month Finally, we will add two more columns to help us really drill down into seasonality. We will add formulas to calculate the day of the month and the week of the month. For best results in the PivotTable filters, these should be converted to strings, so we will make use of the Text() function. In K1 type Day of Month as the column header, and then in K2 enter the formula: =TEXT(DAY(Table4[[#This Row],[Date]]),"#") That simply uses the Day() function to get the day of the month, and then converts the result to a string. Calculating the week of the month (First, Second, Third, Last) is slightly more complex. We will take the day of the month and divide by 7. Now, we need to convert that result into an integer in the range of 1 to 5 by rounding upwards to the nearest integer. For example, if the day is the 18th then 18/7 = 2.57. Round that upwards to the nearest integer (3) using the RoundUp() function. We now know that it is the third week of the month. We can now use the Choose() function to select an appropriate label for that week. In the Choose() function we supply an index number and an array of values. In this case, the 3 will get converted into Third. In L1 type Week of Month, and in L2 enter the following formula: =CHOOSE(ROUNDUP(Table4[[#This Row],[Day of Month]]/7,0),"First","Second","Third","Last","Last") Notice that I have repeated "Last" in the Choose() function. That way, both week 4 and week 5 will be labeled as the Last week of the month. The reason for this is that there will be, at most, three days in the fifth week of the month. You could certainly change those to "Fourth" and "Fifth" if you so desire. Finally, realize that you could create another column to calculate the season of the year (Winter, Spring, Summer, Fall) using similar logic. We have now completed the basic Table construction. Your worksheet should look like the one below: Creating The First PivotTable You can learn a lot by just using the AutoFilter in the Table that we have created. For example, if you want to look at all of the data for just Fridays, you could filter column J so that it just shows the rows that are Friday. However, PivotTables are much more powerful. To create the first PivotTable, first click anywhere in the Table we have created. Now, click on the Insert tab and the click the PivotTable button in the Tables group. You should create the PivotTable on a new worksheet. A new worksheet will appear, and you will see the PivotTable Field List in a pane at the right side. In the Fields section, select all of the fields to be added to the report except for the Date. We don’t need the date field because we have already extracted the relevant parts of the date. As you select fields, they will be added to the PivotTable in the worksheet. At first it will be relatively incomprehensible, but we will fix that now. In the Areas section you will see buttons with the field names. We can drag these to different areas to rearrange the PivotTable. Start by dragging the Day, Day of Month, Week of Month, and Year fields into the Report Filter area. This is what allows us to make the PivotTable show only results for, say, Mondays in the Third week of the month. Or, we could show results for the 14th day of all of the months, or only certain months. Next, drag the Month field to the Row Labels area. This will cause the PivotTable to summarize all data by the month of the year. You can have more than one field in the Row Labels area. For example, you might drag the Year field into this area (above the Month Field) to summarize the data by year and month. If you want to easily compare months from different years, just drag the Year field below the Month field. By default, the data in the PivotTable is the sum of the values. However, for our purposes the averages of the values is more useful. So, in the Values area click the arrow next to each field name and select Value Field Settings. Choose Average for each of the fields. Note, though, that you can choose other functions to summarize the data. For example, we might want to see the standard deviation of the EPC by month. In that case you can change the Value Field Setting for the EPC field to StdDev. That would allow you to see if certain months (or times of the month) have more or less variable click values. The PivotTable is now complete. Experiment with it by choosing different filters, and by dragging fields from one part of the PivotTable to another. Here is the PivotTable showing a summary of all of the data by month: Here is the PivotTable showing the data for Mondays that are in the second week of the month: In the example worksheet I have created a second PivotTable. It uses the same data, but it uses the day of the week for the Row Labels area. This second PivotTable isn’t really necessary, but I like to see the data summarized both by day and by month at a glance. However, you can use just one PivotTable and rearrange it as needed, depending on what types of patterns you are looking for. I hope that you have found this to be useful. An Excel 2007 workbook is available for download (Excel 2003 version). It contains the sample data and the PivotTables created in this post. Please note that the data in the workbook is completely random. It is against Google’s terms of service to disclose your AdSense statistics.

Testing a String for Proper Case

2008-03-09T01:28:00-07:00

Have you ever needed to check a string in a worksheet cell to see if it is in proper case? By "proper case" I mean that the first letter of each word in the string is capitalized and the other characters are lowercase. For example, you may have a list of people’s names (maybe for a mailing list) and you need to be sure that the names are written properly (e.g., John Smith, not john Smith or John sMith). It seems that this should be easy in Excel, but it isn’t obvious (at least there is no easy, built-in function that you can use to validate the string). In this post I will demonstrate three methods. Two are pretty straightforward, and the third uses an array formula. The array formula is complex, but it is interesting at least, and parts of that solution may have other uses. I also provide a VBA function at the end, so I guess that makes it four ways to solve this problem. This question arose on Yahoo! Answers where the questioner asked: Cell A1 has data, I want to create a formula that would look into the cell, and if its case is "Proper", then I get TRUE, otherwise FALSE. any suggestions would be highly appreciated? The Proper() FunctionNow, Excel has a text function that you might expect to work: Proper(). However, the Proper() function takes a string argument and converts it, if necessary, into proper case. As an example, if A1 contains "john smith" (without the quotes) then: =Proper(A1) would return "John Smith," exactly what you want. What the function doesn’t do is return a boolean (True/False) result to indicate that the string is in proper case. Furthermore, you cannot directly use the function in a string comparison because comparisons in Excel are case-insensitive. So, using the same example as above, this formula will always return True: =A1 = Proper(A1) no matter what you type into A1. So, ="john smith"=Proper("john smith") will be true, even though it really isn’t. Again, Excel ignores the case of the letters in the comparison. Solution 1: Using the Exact() FunctionThe simplest method that I have found for this problem is to use the Exact() function. This string function is case-sensitive, so it can be used to test if the letter cases are identical to the proper-case version of the string. Still assuming that A1 contains the string to be tested ("john smith" or some variant with different cases) then: =EXACT(A1,PROPER(A1)) will always return False if the string is not in proper case. Simple and to the point. Problem solved. Solution 2: Using the Substitute() FunctionA slightly more complex version, with no advantage over using Exact() that I can see, is to use the Substitute() function. This function substitutes new text for existing text in a string. So, we can substitute a short string (say, "x" or just " ") into the proper-case version and then check to see if the function returns the short string: =SUBSTITUTE(A1,PROPER(A1),"x")="x" That will also return False if the original string (in A1) is not in the proper case, and True if it is. Solution 3: An Array Function SolutionUsing an array function for this problem is overkill, but I admit that it was the first technique that I thought of. While this is far from the easiest way to solve this particular problem, I think that the process has some valuable lessons. In the text below, you will learn about ASCII character codes, how to "explode" a string into its individual characters (kind of like the PHP Explode function, but we aren’t using a delimiter), and how to generate a dynamic array of consecutive numbers (you could also use that technique to generate many other arrays, such as every other number), and how to compare two arrays to see if they are identical. The First Step Towards a Solution: The Code() FunctionWhat we really need to do is to compare the ASCII values of the characters (that link has a good explanation of ASCII values and a list of them for each letter). The key is to realize that the ASCII character codes are different for the uppercase and lowercase versions of a letter. So, "J" is 74 while "j" is 106. The Code() function will return the ASCII character code for a letter, or for the first letter of a string (it ignores all characters after the first). Take a look at the output of the Code() function in the following examples: =Code("J") returns 74 =Code("j") returns 106 =Code("John Smith") returns 74 because it only sees the capital J at the beginning. Great! All we need then is a way to compare the ASCII codes of each letter in the string to its proper-case version. Splitting a String into Its Individual CharactersIn order to compare the ASCII values of each character in the string, we need to separate the string into its individual characters. Ideally, we will do this all within a single formula rather than entering each character into its own cell. So, we need to use an array formula. This will require the use of the Mid() function and a dynamic array. I’ve dealt with string functions before in the Dealing with Unusual Date Formats post. The Mid() function is defined as: MID(text, start_num, num_chars) and it extracts the number of characters that you specify (num_chars) from a string (text) starting at the position (start_num) that you specify. For example: =MID("john",1,1) will return "j" because that is the first character in the string. We need the Mid() function to extract each character in the string, not just the first one. If we enter the function above as an array formula (Shift+Ctrl+Enter, not just Enter) it will still return a single "j." So, we need to specify an array for the start_num argument: =MID("john",{1,2,3,4},1) That will give us {"j","o","h","n"} as the result. You won’t see that array in your worksheet cell (you’ll just see "j"), but you can check the result after entering the formula by highlighting it in the Formula Bar and pressing F9. Now we are getting somewhere, but we still have a bit of a journey. Converting the Characters into their ASCII CodesRemember that we have to compare the ASCII codes of the characters, not the letters themselves. Again, this is because a string comparison would be case-insensitive. So, we will change our array formula from above so that it also uses the Code() function that was mentioned earlier. We want to feed the output of our Mid() function into the Code() function. Take a look at the results for two of these formulas: =CODE(MID("john",{1,2,3,4},1)) returns {106,111,104,110} =CODE(MID("John",{1,2,3,4},1)) returns {74,111,104,110} Note that we get a different array of ASCII codes from "john" and "John." If the strings were identical, the arrays would be identical. The second version is the proper case version. We can now make use of the Proper() function and see where this is going: =CODE(MID("john",{1,2,3,4},1)) returns {106,111,104,110} =CODE(MID(PROPER("john"),{1,2,3,4},1)) returns {74,111,104,110} In this case, I used the exact same string ("john"), but in the second version I first converted it to the proper case using the Proper() function. All that we need to do is to compare the arrays from these two versions for any arbitrary string. If they are the same for every character code, then the original string is in proper case. A Dynamic Array from 1 to the Length of the StringOne remaining problem is that the formula above will only work for a four-character-long string, or the first four characters of a string. So, we need to change the {1,2,3,4} part into a dynamic array from 1 to the length of the string. For example, "John Smith" has 10 characters, so we need that array to be {1,2,3,4,5,6,7,8,9,10} if we want to evaluate every character. I’ve mentioned before that the Row() function can be used to create a dynamic array of sequential numbers, and that is exactly what we need. For example: =ROW(A1:A4) when entered as an array formula will return {1,2,3,4}. We could use =ROW(A1:A10) to get the 10 sequential numbers that we would need to evaluate every character in "John Smith". Unfortunately, that formula isn’t dynamic because the range (A1:A10) is hard-coded. To create a dynamic range, we need to use the Offset() function. So, we can do this to get our 10 sequential numbers: =ROW(A1:OFFSET(A1,9,1)) That creates a range from A1:A10, which is then fed into the Row() function to produce {1,2,3,4,5,6,7,8,9,10}. Note that we are offsetting from A1 by 9 rows, not 10. If you offset from A1 by 1 row, you get to A2. If you offset by 2 rows, you get to A3. So, we need to use an offset that is 1 less than the last row that we want. Also, note that offsetting from A1 is arbitrary. You could offset from any other column (e.g., B1, C1, etc). However, if you were to offset from A2, then the array would start at 2 instead of 1. Finally, remember that we really want an array from 1 to the length of the string. So, replace the 9 in the above formula with the Len() function minus 1. For example, =ROW(A1:OFFSET(A1,LEN("John Smith")-1,1)) will return {1,2,3,4,5,6,7,8,9,10}, which is exactly what we want. If we change the string from "John Smith" to "John" (or anything else) then our array will change. It will be exactly as long as the string. It is a dynamic range. We are getting close. Let’s plug our dynamic range into our Code(Mid()) formula from above, and assume that the string is in A2: =CODE(MID(A2,ROW(A1:OFFSET(A1,LEN(A2)-1,0)),1)) returns {106,111,104,110} for "john" in A2. If we change the string to "John Smith" we get {74;111;104;110;32;83;109;105;116;104}. It works automatically because the array is dynamic. Comparing Two Arrays to Test for EquivalenceThe last formula above will give us the array of ASCII character codes for each character in the string in A2. We need to compare that array with one formed by the proper-case version of that same string: =CODE(MID(PROPER(A2),ROW(A1:OFFSET(A1,LEN(A2)-1,0)),1)) That formula is identical to the previous one, except that we first convert the string in A2 to proper-case using the Proper() function. If the string in A2 is "john smith" (all lowercase characters) then the two formulas would return: =CODE(MID(A2,ROW(A1:OFFSET(A1,LEN(A2)-1,0)),1)) returns {106;111;104;110;32;115;109;105;116;104} =CODE(MID(PROPER(A2),ROW(A1:OFFSET(A1,LEN(A2)-1,0)),1)) returns {74;111;104;110;32;83;109;105;116;104} Notice that the arrays are different in the first and sixth positions, so we know that the string cannot be in proper case. If instead, the string in A2 was "John Smith" then the first version of the formula would return {74;111;104;110;32;83;109;105;116;104}, which is identical in every position to the proper-case result. So, how do we do this comparison in a formula? If we divide one array by another, Excel will produce a third array that contains the result of dividing the corresponding array positions. In other words, position 1 is divided by position 1, position 2 is divided by position 2, an so on. If the string in A2 is "john smith" then dividing the two arrays will give us {1.43243243243243;1;1;1;1;1.3855421686747;1;1;1;1}. Note that we have a 1 in every position except for the first and sixth. Since not every position is a 1, we know that the two arrays are not the same. The formula to do this comparison is: =CODE(MID(A2,ROW(A1:OFFSET(A1,LEN(A2)-1,0)),1))/ CODE(MID(PROPER(A2), ROW(A1:OFFSET(A1,LEN(A2)-1,0)),1)) The only difference between the numerator and denominator is that the denominator is working with the proper-case of the string. If the original string is in the proper-case, then the result array would be {1;1;1;1;1;1;1;1;1;1}. If it isn’t in the proper case, then the result array would contain one or more positions that are not 1’s. Wrapping It UpThe seeds of our solution are now sown. We just need to harvest the final answer. Realize that the sum of the numbers in the result array will be equal to the length of the string if, and only if, the string is in proper-case. Using the above examples: =SUM({1.43243243243243;1;1;1;1;1.3855421686747;1;1;1;1}) returns 10.8179746011071 for the string "john smith" =SUM({1;1;1;1;1;1;1;1;1;1}) returns 10 for the string "John Smith" So, all we need to do is to compare the length of the original string to the sum of our result array. If they are the same then the string is in proper-case. So, the formula would be (enter it using Shift+Ctrl+Enter): =LEN(A2) = SUM(CODE(MID(A2,ROW($A$1:OFFSET($A$1,LEN(A2)-1,0)),1)) / CODE(MID(PROPER(A2),ROW($A$1:OFFSET($A$1,LEN(A2)-1,0)),1))) For the string "John Smith" in A2 the formula would return TRUE. If the string in A2 was "john smith" it would return FALSE. Any version of the string other than "John Smith" will return FALSE, which is exactly what we set out to do. That is a pretty ugly formula, but it does the job. If you can’t use VBA (many companies don’t allow it), then you really don’t have a choice but to construct such a beast. Read on for a VBA solution. Solution 4: A VBA Function to Replace the Worksheet FormulaIf you are able to use macros at your company, then VBA provides a simple way to solve the same problem. Here is an IsProper() function that you can use in your workbook: Public Function IsProper(S As String) As Boolean ‘Returns True if the string is in the proper case If StrComp(S, StrConv(S, vbProperCase), vbBinaryCompare) = 0 Then IsProper = True Else IsProper = False End If End Function To use the IsProper() function, press Alt-F11 to open the VBA editor. Find your workbook in the project window and right-click it. Insert a Module and then paste in the code. Now, the IsProper() function can be used in your workbook in place of the monstrosity of a worksheet function that we created earlier. You can download a sample workbook that has both the worksheet formula and the VBA version. The example workbook is a macro-enabled Excel 2007 file, but you can open it in Excel 2003, or earlier, using the Compatibility Pack.

Charting Economic Time Series with Shaded Recessions

2008-02-04T00:46:00-07:00

If you read the business press or government economic reports, then you have undoubtedly seen charts that show recessionary periods with shading. In this post I will demonstrate how to create this type of chart in Excel 2007. The same technique is applicable to all versions of Excel, though the mechanics of creating the charts in earlier versions are slightly different. Users of other spreadsheets, such as Open Office Calc should be able to use a similar procedure. Obviously, if we are to chart an economic data series and shade the recessions then we need data. For this post I will be charting the US civilian unemployment rate. This data is available from the Bureau of Labor Statistics, though I got it from the St. Louis Fed’s FRED database. We also need the dates of US recessions, which are available from the Business Cycle Dating Committee of the NBER. The chart that we are trying to create is pictured below: Creating the chart of the unemployment rate is simple. Just select the dates and the unemployment rate series. Go to the Insert tab and insert a Scatter chart. Charting Strategy The more challenging problem is getting those shaded bars for recessionary periods. Here is the strategy in a nutshell: We need to create a boolean variable (true/false, or 0/1) to indicate the presence or absence of a recession. In this case, we will use a formula to return a 1 if the date is during a recession and a 0 otherwise. Then, we will plot this series as a column chart and use a secondary Y-axis (at the right) for the scale. The reason that you don’t see the secondary axis in the picture above is that it is hidden. The result is a mixed chart. That is, it has a series that is charted as an XY Scatter chart and also one that is charted as a column chart. Structuring the Worksheet and Data The unemployment data is straightforward. Just paste the data into columns A and B in the worksheet. In column C we are going to calculate the boolean variable mentioned above. We also need the dates of the post-war recessions from the NBER. Go to column F and enter the dates of the expansion peaks, and in column G enter the recession troughs. The worksheet should look like the fragment below: A note about the recession dates: The NBER data gives a particular month that a recession began and ended. I chose to set the peak date to the beginning of that month, and the trough date to the end of the month. This simplifies the formula slightly, and it really doesn’t matter much in the grand scheme of things. Building the Boolean Formula Now we need a formula that will compare the relevant date in column A to the dates in columns F and G. If the date falls between any two of the dates in F and G, then it is in a recession. Clearly, then, we need to do some kind of a lookup in the table of recession dates. Your first thought might be to use VLookup(), and you probably could do it that way. However, I chose a different approach that I think is more efficient and probably works better. My approach is to use a combination of the Index() and Match() functions. The Match() function will look for our date in column F and then return its position, or the position of the largest date that is less than our date. So, the first date that we will be looking up is 1/1/1948. In this case, the Match() function will find that 2/1/1945 is the largest date that is less than or equal to our date (11/1/1948 is larger) and will return 1 (the first item in the list). We will need that 1 again in a moment to serve as the "key" to the ending date of the recession. Next, the Index() function will be used to get the actual date of the last economic peak (2/1/1945). So, at this point here is the formula that we will use to find the date of the last peak: =INDEX($F$2:$F$12,MATCH(A2,$F$2:$F$12)) Note that $F$2:$F$12 is the list of peaks, and A2 is the date that we are looking for. Now that we have the peak date, we need to find the corresponding trough date. That date will be in the same row as the peak date, so we will use the same Match() function to get the index number. We then feed that into the Index() function to get the trough date: =INDEX($G$2:$G$12,MATCH(A2,$F$2:$F$12)) Notice that I changed the array for the Index() function to $G$2:$G$12. That is what causes it to look in the trough column. So, we now know the dates of the last peak and trough in the economy. All we need to do is to see if our date is between those two dates. If it is, then our date falls in a recession. If it isn’t, then our date is in a period of expansion. Fortunately, Excel stores dates as an integer (the number of days since January 1, 1900) so we can do some simple mathematical comparisons. We want to check to see if our date is simultaneously greater than the peak date and less than the trough date. If it is, then the date is in a recession. To test to see if two (or more) conditions are simultaneously true, we use the And() function. So, we can do this: =AND(A2>=INDEX($F$2:$F$12,MATCH(A2,$F$2:$F$12)), A2<=INDEX($G$2:$G$12,MATCH(A2,$F$2:$F$12))) If that evaluates to true, then the date is in a recession. We could wrap that formula in an IF() statement and return a 1 or 0, but we don’t need to do that. The And() function will return either TRUE or FALSE, and Excel treats TRUE as a 1 and FALSE as a 0. Because of that, we can actually chart a series of TRUE/FALSE values. However, I prefer to see 0 or 1 so I will force the conversion to a number by multiplying by 1. The resulting formula for C2 is: =AND(A2>=INDEX($F$2:$F$12,MATCH(A2,$F$2:$F$12)), A2<=INDEX($G$2:$G$12,MATCH(A2,$F$2:$F$12)))*1 Copy that formula down to C722 to do the calculation for all dates. Add the Recession Boolean Series to the Chart Adding another data series to a chart in Excel 2007 is a bit different than in previous versions. It used to be that you could just drag and drop a range onto an existing chart to add another series. That functionality has, unfortunately, been removed in Excel 2007. Still, it isn’t difficult. Just right click in the chart area and choose Select Data from the shortcut menu. Finally, click the Add button and then enter the data ranges. At this point, your chart will look rather odd, as seen below: Given the vastly different scales of the data series, we need to do some reformatting. First, select the recession data series and then Format Selection. In the Series Options section of the dialog box choose to plot the series on the secondary axis. That will clean it up considerably, but we aren’t done. Now go to the Design tab, click Change Chart Type and set the chart type to Clustered Column. Select the series again and then change the fill color to a light gray (or whatever other color you like) and set the border color to No Line. Go to Series Options and set the Gap Width to No Gap (0%) to get solid filled bars. Now that the data series is set, we can reformat the X axis and add a chart title. The X axis scale should be set such that the minimum is about 17,500 (roughly the first date) and the maximum is 39,500 (a bit after the latest date). Finally, move the legend to the bottom, add a title, and remove the grid lines. At this point, your chart should look like this: Note that I have circled the secondary Y axis. We need to set the scale so that it has a minimum value of 0 and a maximum of 1. This will make it so that the gray bars go to the top of the chart. Now, we need to hide the axis. Set the Line Color to No Line, and then go back to Axis Options and set Major tick mark type and Axis labels to None. We now have the final version of the chart: This technique can be applied to any long-term economic data series, and makes it easy to see how the data behaves during recessions and expansions. I hope that you have found this to be useful. You can download a sample worksheet with the data and chart from this post. Note that I have placed the chart in the Chart1 sheet.

Native Excel 2007 Viewer Now Available

2008-01-16T20:04:00-07:00

Microsoft released a new version of the Excel Viewer program on 14 January 2008. This version natively (without conversion) supports the viewing and printing of Excel 2007 files as well as all previous Excel files back to Excel 97. Here is the description from the site: With Excel Viewer, you can open, view, and print Excel workbooks, even if you don’t have Excel installed. You can also copy data from Excel Viewer to another program. However, you cannot edit data, save a workbook, or create a new workbook. This download is a replacement for Excel Viewer 97 and all previous Excel Viewer versions. While I’m here, let me remind you that there is also a Microsoft Office Compatibility Pack available that allows one to open and edit Office 2007 files from Office 2003 programs. Interestingly, Andrew Z. over at OpenOffice Ninja says that this works great for other office suites as well. Both of these are free downloads from Microsoft.

Disallow Special Characters Using Data Validation

2008-01-15T05:54:00-07:00

Excel’s data validation is a wonderful tool for ensuring that only acceptable data is entered into a cell. This is particularly helpful when users other than the one who created the worksheet will be filling in the data. The data validation tool allows you to set up rules that govern the types of data that are allowable, to present input messages that inform the user about the rules, and to display error messages if the rules are violated. Quite obviously, this can reduce errors in the worksheet. The most powerful type of data validation is the Custom type, which is based on formulas. In this post I will present a method to eliminate disallowed characters from data that is entered into a validated range. I posted this solution on Yahoo! Answers in response to a questioner who said: End users will be entering free form text into a Excel file which I will then need to upload into my system. I want to be able to restrict the characters they can enter. I do not want them to be able to enter special characters (& : , ; / # etc) in multiple columns, only letters and numbers…. I don’t really want to just use the Input Messages to notify users that they cannot enter x, y, z; but truly restrict their input to force correct data which I can use without manipulation. Imagine that you create a spreadsheet that will be distributed to various departments to be filled in with important text and/or numbers. One of the first things that you will probably do is to use worksheet protection to keep users from entering data into cells where they shouldn’t. You can do this by unlocking the cells where data should be entered and then protecting the worksheet. This works very well, but it doesn’t control what the user can enter into the unprotected cells. This is where data validation (and a wee dose of VBA) comes to the rescue. Suppose that the users will be entering data into A2:A30. Presumably, there will be formulas elsewhere that rely on this data. In order to validate the data, we need to search through the text/numbers that are entered to check for the invalid characters. So, we first need to create a list of the invalid characters. In C1 enter “Disallowed” and then in C2:C6 enter: %, &, /, :, and #. Next, we need to be able to dynamically identify the currently active cell in the worksheet (where the user may be entering data). Unfortunately, I don’t know of a way to do this without using VBA, so we’ll do a bit of coding. VBA has an event called Worksheet_SelectionChange that gets fired every time the active cell is changed on the worksheet. There is also an event called Workbook_SheetSelectionChange that fires every time the selection changes in any worksheet in the entire workbook, but we are going to use the worksheet version to minimize the overhead. We only need one line of code to enter the address of the active cell into our worksheet where we can work with it: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Range("f1").Value = Target.AddressEnd Sub That will place the address of the newly active cell as a string into cell F1. We can now use that address to feed into the Search() function. The Search() function will look through a string and identify the location of the first occurrence of a character (or string). So, that will tell us where the character is, but only if it is in the string. That won’t quite work. However, the Search() function will return a #VALUE error if the character isn’t found. Now, that’s useful. We just need to know if a particular character is in the string, or not (1 or 0, yes or no). So, we can use an IF() statement, combined with the IsError() function to figure this out. Enter the following formula into D2: =IF(ISERROR(SEARCH(C2, INDIRECT($F$1))),0,1) If there is an error (the character is NOT in the cell), then this will return 0. If the disallowed character is in the string, it will return 1. Note that I am using the Indirect() function, which I have used before (see this post). In this case, the Indirect function will get the active cell address from F1 and return the string that is contained in the active cell. That string will then be fed into the Search() function where it will be processed by our formula. Now, that will tell us if the first disallowed character was entered. However, we have a whole list to check against, so copy the formula from D2 to D3:D6. The worksheet should now look like the one in the picture below: Finally, we need to set up the data validation rule. Select A2:A30 where the data validation rule will be applied. Then, in Excel 2007, go to the Data tab and choose Data Validation from the Data Tools group. In the menu, choose Data Validation. (In Excel 2003 or earlier, choose Data and then Validation from the menu.) Finally, in the dialog box choose Custom from the Allow drop-down list. The formula that we enter needs to result in a True/False answer. Recall that we previously set up formulas (in D2:D6) that are 0 if the disallowed character is not in the cell, and 1 if it is. So, if we add the results of those formulas and get 0 then we know that the user did not enter a disallowed character. If the sum is greater than 0, then they did. Therefore, we want to allow entry only if the sum of D2:D6 is equal to zero. The formula is: =SUM($D$2:$D$6)=0 This will be true only if there were no disallowed characters entered. Note that we need to use absolute cell references so that we can apply this data validation rule to a whole range of cells. The Data Validation dialog box should look like the picture below: Notice that there are two additional tabs in the Data Validation dialog box. In these tabs you can define an “input message” that tells the user what they should enter, and an “error message” that will pop up if they violate the rules. Personally, I find that the input message can be a bit annoying and usually leave it blank. The error message, though, can be very useful to users who otherwise might be confused about why their entry was rejected. If you have followed along, try entering a string that contains a disallowed character into A2. You should get an error message. Click the Retry button and enter a clean string. It should be entered without any problems. You can download an example worksheet. Note that this is a macro-enabled Excel 2007 file, so you will need to allow the macro to run if you get a warning message. If you are using Excel 2003 or earlier, you will need the free compatibility pack to open the file.

Chart the Yield Curve Using Yahoo! Finance Data

2008-01-04T21:50:00-07:00

The U.S. Treasury yield curve is of tremendous importance in the financial world, so those of us who teach finance often find it desirable to show a chart of the current yield curve. I’ve used many sources over the years, but my favorite disappeared a few years ago. The data is readily available, so creating your own yield curve isn’t that difficult. It is a bit of a hassle, though. In this post I’m going to show how you can create a “live” U.S. Treasury yield curve using Excel’s Web Query feature and data from Yahoo! Finance. If you go to the Yahoo! Finance Bonds Center you will see a page like the one pictured below: What we want to do is grab this data and use it in an XY Scatter chart in an Excel workbook. Obviously, we could just copy the data and then paste it into Excel, but then the data will be stale almost immediately. Much better to have the worksheet fetch the data from the Website in real-time (or something close). This is where the Web Query comes into the picture. Excel has had the Web Query feature for several years, so you are probably already familiar with it. In Excel 2003 (and earlier), you can create a Web Query by choosing Data --> Import External Data --> New Web Query. In Excel 2007, go to the Data tab and then click From Web in the Get External Data group on the Ribbon. Whichever version of Excel you use, that will launch a dialog box containing a mini browser. Simply enter the URL to be taken to the page that contains the data that you want to capture. The picture below shows the dialog box in Excel 2007 (it is very similar in previous versions): Notice the yellow arrows that point to tables that you can capture with the Web Query. Click the one next to “US Treasury Bonds Rates” and then click the Import button. Excel will now place a Web Query into your worksheet in the location that you specify in the next dialog box. Note that if you click the Properties button before placing the query, you can specify things like the refresh frequency, data formatting, and so on. Once you place the query in your worksheet, you will see a brief message stating that Excel is getting the data and then you will see the data. If you need to change the query, or refresh the data, just right click anywhere in the query data and choose the appropriate menu item. Ok, we now have the data in our worksheet, but it isn’t really ready to chart. First, note that the maturities in column A are text (3 month, 6 month), etc. We need to manipulate this to convert it into consistent units (years, in this case). Second, the yields are shown in percentage form rather than decimal (e.g., 3.08 instead of 0.0308). This doesn’t matter much, but I want my Y-axis to show percentages, so I’ll have to manipulate and format these numbers as well. Now, since we are going to create an XY Scatter chart, we need the manipulated maturities to be in the furthest left column, but we can’t just stick another column in the middle of our query data. So, we’ll work on another worksheet and pull the data in from the original worksheet. Go to Sheet2 and in A1 enter the formula: =Sheet1!A1 (better, just type = and then click on A1 in the original sheet). Now, copy this down and across the range A2:E9 to pull in all of the data. We have a copy of the data that we can now modify as much as we want. First, let’s fix the problem with the maturities. In A3, the maturity is shown as “3 Months,” but we need it displayed in years. That means that we will need to use some Text functions (see my post on Dealing with Unusual Date Formats for more information) to extract the number of months and convert it to a fraction of a year. Essentially, we want to extract the 3 from A3 and then divide by 12. We’ll also do the same in A4. Now, we could just extract the first character of the string, but that won’t work for every cell in the column (and I like to use one consistent formula wherever possible). So, our strategy will be to find the location of the first space in the string and then extract everything to the left of that. In A3 on Sheet2 enter this formula: =LEFT(Sheet1!A3,FIND(” “,Sheet1!A3))/12 That will extract the number of months (3 or 6, depending on the cell). Great! Unfortunately, it won’t be correct for the other cells because we don’t want to divide, say, 2 years by 12. For the maturities entered as years, we simply want the number (e.g., 2, 3, 5, etc). Clearly, then, we need an IF() statement. However, the IF() statement needs to be able to figure out whether to divide by 12 or not. We’ll do this by checking to see if the text after the number says “Month” or “Year.” One problem with that, though, is that Yahoo! might change the text to plural instead of singular and that would break our test. So, let’s just extract the first character and see if it is “M” or “Y.” The formula to extract that character is: =MID(Sheet1!A3,FIND(” “,Sheet1!A3)+1,1) = “M” which will be either TRUE or FALSE. Note that we are just checking for an “M.” If it is there, then we need to divide by 12 to get the fraction of a year, otherwise we just need the raw number. So, put the following formula into A3: =VALUE(IF(MID(Sheet1!A3,FIND(” “,Sheet1!A3)+1,1) = “M",LEFT(Sheet1!A3,FIND(" “,Sheet1!A3))/12,LEFT(Sheet1!A3,FIND(” “,Sheet1!A3)))) Note that I have surrounded the whole thing in the Value() function to convert the resulting string into a number. This isn’t strictly necessary, but it doesn’t hurt and it does make the numbers align properly. Copy that down, and you are done with this part. The other change that we want to make is to convert the yields into decimal form. Go to B3 and change the formula from =Sheet1!B3 to =Sheet1!B3/100, and then format the number to show the % sign. Now, copy that to the other yields. All that remains is to create the chart. In Sheet2, select A2:B9 and also E2:E9 (hold down the CTRL key while selecting the second range). Now, create the XY Scatter chart. This gives me a yield curve chart that shows today’s yields (from B2:B9) compared to yields from one month ago (E2:E9). Label the X and Y axes however you like. For the chart title, we’ll create a dynamic string that will include today’s date. In A11 on Sheet2, type “Chart Title” as the label, and then in B11 enter the following formula: ="U.S. Treasury Yield Curve “&CHAR(13)&TEXT(TODAY(),"mmm. Dd, yyyy") That will create the title with today’s date and insert a line break (Char(13)) so that it looks nice. To actually get that title into the chart, just add a title as usual (it can say anything) and then select it. In the formula bar, type =A11 and then press Enter. When you have the chart formatted the way that you want it, right click in the chart and choose Move Chart. Well move it so that it is an object in Sheet1. By putting the chart back into Sheet1, we never again have to look at Sheet2. Any time you want an updated yield curve, just open the workbook, right click in the query data on Sheet1 and refresh it. Your chart and data will be updated automatically. The picture below shows the result: You can download the example worksheet. Note: This is an Excel 2007 file. If you are using Excel 2003, you will need to use the free Compatibility Pack from Microsoft if you haven’t already installed it. Also, you may get a security warning about Data Connections being disabled for security reasons. If so, you can just click the button and enable the content. For more information regarding Excel and bonds, please see my bond valuation and bond yields tutorials.

Display Scenarios Using a Drop-Down List

2007-09-24T01:06:00-07:00

In my finance classes at Metro State, I frequently use Excel as a presentation program. I prefer it to PowerPoint because it allows me to do a live demonstration of concepts, rather than showing static slides with static pictures. One tool that is very often useful is Excel’s Scenario Manager. If you aren’t familiar with it, the Scenario Manager is a tool that lets you specify values to place into certain cells when you choose to display a scenario. So, you can have one worksheet, but display any of several scenarios (best base, base case, worst case, etc). The problem with this tool is that you have to load it (Tools --> Scenarios in Excel 2003 and earlier, or under “What If Analysis” on the Data Ribbon in Excel 2007) in order to change the scenario. It isn’t that difficult, but it really interrupts the flow of a presentation. The purpose of this post is to show a way to use the Scenario Manager, but use a drop-down list to display the scenarios. My example spreadsheet is a very simple NPV calculator, as shown below: I set up three scenarios using the Scenario Manager: 1. “Low WACC” with an 8% required return. 2. “Base WACC” with a 10% required return. 3. “High WACC” with a 12% required return. I’ve purposely kept this simple for demonstration purposes. There is only one changing cell (the required return in B1). I can now change the spreadsheet by launching the Scenario Manager, choosing a scenario, and pressing the Show button. However, the Scenario Manager’s dialog box is modal. That is, you can’t do anything to the worksheet until you dismiss the dialog box. It just sits there blocking part of the worksheet and preventing you from doing anything else. We can fix that problem by using the Scenario Manager as a database for our scenarios (that is, use it to store them). To switch between scenarios, we add a drop-down list to the worksheet, and add a very tiny amount of VBA code. You can see the the drop-down list in the upper-right corner of the picture above. To add one to the worksheet, go to View --> Toolbars and turn on the Forms tool bar (in Excel 2007, choose Insert on the Developer Ribbon). Click on the Combo Box control, and then click and drag in the worksheet to place it. Now, we need to format the control so that it knows where to get the strings to put in the list, and where to put the number of the selected item. The picture below shows how I have it formatted: The “Cell link” is where the control lets you know which item has been selected. We can use that information for all kinds of purposes. In this case, we are going to write a VBA sub that will read that and then change the scenario that is displayed, without launching the Scenario Manager. Open the VBA Editor by pressing Alt-F11 and then go to the code module for the worksheet (it will be listed under the file name and have the same name as the worksheet). Paste this code: Sub ChangeScenario()' Changes the scenario on the worksheet according to the choice made' in the drop-down list.Scenarios(Range(ActiveSheet.Shapes("DropDown").ControlFormat._LinkedCell).Value).ShowEnd Sub (Note that the underscore indicates a line continuation. The second line of code should be on the same line, but I had to make it fit into a limited width for the blog.) That code will be run when the user chooses a new item from the drop-down list. It simply tells Excel to display a certain scenario whose number is found in the “LinkedCell” of the drop-down list. Note that I have renamed the drop-down list to DropDown. To do this in any version of Excel, right-click the control and change the name in the Name Box to the left of the Formula Bar. That’s it. A very simple way to view scenarios without having to launch the Scenario Manager. This example is very simple, and could have been done without using Scenario Manager at all. However, if you have more complicated scenarios (with many changing cells), then this is a great technique and will improve your presentations. You can download my example spreadsheet if you want to give it a try.

Adding an Average Line to a Chart Using Defined Names

2007-09-04T07:54:01-07:00

Please Note: This technique appears not to work in Excel 2007 (SP 1). For some reason, not explained by Microsoft as far as I can tell, some (but not all) named ranges are no longer allowed in the Series() function. This appears to be yet another problem with the new charting engine. So, this post only applies to Excel 2003 and earlier versions. I was perusing Yahoo! Answers looking for interesting questions and I ran across one that asked how you can add an line that represents the average of a data series to a chart of that series. The most obvious way to do this would be to use a helper column with the Average() function in each cell. It would be the same value all the way down the column, and then you could just add that series to the line chart. Of course, the problem is that you have to remember to copy the Average() function down every time you add a new data item. There has to be a better way. Of course there is, and this post will show my solution. It involves using defined names (AKA range names). Defined names are a powerful tool, not because they allow you to give a name to a static range (or cell) on your worksheet, but because defined names are really formulas. In Excel 2003, go to Insert --> Name --> Define to access the Define Name dialog box (in Excel 2007 Define Name is located on the Formulas ribbon). Notice the box at the bottom that is labeled “Refers to:” In that box, the default entry is something like =Sheet1!$A$1 (it will be the address of the active cell or range). It is the equals sign that is the giveaway. These defined names are actually formulas. The first thing that we want to do is to create a defined name that references our original data, and that will also expand as we add new data. In other words, we want to create a dynamic range name. To do this, we need to create a reference using the Offset() function and the Count() function. The goal of these two functions will be to find the last data point in the column. The Count() function will tell us how many numbers are in the column, and the Offset function will create a reference to that last data point. In my sample worksheet, I have “Random Data” as the label in A1, and then 23 numbers below that. So, my data is going to be in column A. Now, go to Insert --> Name --> Define and then enter the name ChartData at the top. Now, click in the “Refers to:” box at the bottom and enter the following formula: =Sheet1!$A$2:Offset($A$2,Count(Sheet1!$A:$A)-1,0) That will create a dynamic reference from A2 to the last data point. Make sure that you use absolute references (the dollar signs) in the formula. Note that I used Count(A:A) to get a count of the numbers in column A and then subtract 1. I am subtracting 1 because otherwise the Offset function would reference one row after the data ends. Now, create a line chart of the original data (I just generated some random numbers for my sample worksheet. Click on the line for the data series and then look in the formula bar. You should see the Series() function that Excel uses to define the chart. The formula will look something like this: =SERIES(Sheet1!$A$1,,Sheet1!$A$2:$A$24,1) The first reference is the name of the data series, and the second is the range that holds the data. We want to replace the data range with our dynamic range name so that the formula looks like this: =SERIES(Sheet1!$A$1,,Sheet1!ChartData,1) You can do that directly in the formula bar by editing the Series() function. We now have a dynamic chart. If you add new data to the series, the ChartData dynamic range will automatically expand and the chart will expand to include the new data as well. We are now halfway done. At this point, we want to add another series to the chart that represents the average of all of the data points. It will be a straight line all of the way across the chart, and it will move up and down as the average changes because of the new data that will be added. Again, we will use a defined name. However, in this case we need to create an array function because we need to show the average at each data point. In other words, the new series will have exactly as many data points as the original data series, but each point will be the average. To do this, I need to create an array such as {0.49;0.49;0.49;0.49}. So, I need the average to repeat the same number of times as there are data points. There are probably other ways to do this, but here is what I used: =IsNumber(ChartData)*Average(ChartData) Create a defined name called Avg and set it to that formula. Note that the IsNumber() function evaluates each data point in the original series to see if it is a number or not. If so, then it returns TRUE. I know that every data point is a number, so I’m using this to generate an array of TRUE with the same number of values as the original data. Now, when I multiply that array of Trues by the average, Excel will convert TRUE to its numeric representation (which is a 1), and create an array that contains the average repeated N times (such as the example above). At this point, all that remains is to add the Avg series to the chart. To do that, just right click in the chart and choose Source Data. Go to the Series tab and click the Add button. In the Values box, type: =Sheet1!Avg and the click the OK button. You should now have a horizontal line representing the average in your chart. The picture below shows an example: An example workbook is available.

Dealing With Unusual Date Formats

2007-08-28T08:57:00-07:00

I’m sure that you have all been through this: You go to a Website (or a database, probably from a mainframe) to get some data. The problem is that the dates are formatted in some obscure way that Excel doesn’t understand. In this post, I’ll give you an idea of how to convert these dates into something that you can work with. Yesterday I was working on a project that involves charting some economic data series. Frequently, charts of economic time series show shaded areas that indicate a period of recession (I’ve got a post that shows how to do that). So, I needed to know the dates for post-war recessions. Of course, the NBER is the ultimate source of this data and they happen to have a very nice page where you can get the data. The problem is that the dates are in a somewhat odd format. For example, the most recent recession began in the first quarter of 2001 and ended in the third quarter of 2001. Those dates are shown as “March 2001 (I)” and “November 2001 (IV)”. These are perfectly readable by human beings (I am sometimes proud to claim membership in that group), but Excel doesn’t recognize them as dates. I should note that Excel would recognize them as dates if it wasn’t for the quarter notation at the end of the string (e.g., the “ (IV)” part). So, if you type in “March 2001” Excel will convert that to a date (March 1, 2001). Also, realize that there are many odd date formats out there (e.g, 20010301 might indicate March 1, 2001), and this is only one. These problems don’t have to be corrected by hand. Instead, you should use the various string functions (Excel calls them Text functions in the Insert Function dialog box) in combination with the date and time functions to parse the strings into something that Excel understands. The useful string functions for our purposes here are: * Find - As the name indicates, this function is used to find the first occurrence of a string inside of another string. It returns the position of the string, if it exists. This function is case-sensitive, so “A” is not the same as “a”. * Search - Same as Find(), but it is not case-sensitive. * Len - Returns the length of the string as the number of characters. * Left - This function lets you extract the first N characters from a string. * Mid - Similar to Left(), but you extract text from the middle of the string. * Right - Again, similar to Left() and Mid(), but you extract text from the right side of the string. There are other string functions, but these are the ones that you might use for this purpose. Let’s take the “March 2001 (I)” string as our input. Assume that the string is located in cell A1. We could extract the month by using: =Left(A1,5) That tells Excel to grab the first 5 characters from the string located in cell A1. Similarly, we could get the year by using: =Mid(A1,7,4) which says to start at the 7th character and grab 4 characters. If we put those functions into two different cells, then we would have a string (March) in the first cell, and a number (2001) in the second. Unfortunately, that doesn’t do what I want. Instead, I want Excel to recognize March 2001 as a date. To do this, I can combine the two formulas using concatenation: =Left(A1,5)&” “&Mid(A1,7,4) This will give me the string “March 2001”. Note that even though this is a string, Excel will recognize it as a date and you can do date math (add or subtract days, etc) with it. Great, but in most cases we aren’t really done yet. I want to convert it to a particular day of the month. Excel assumes that March 2001 means March 1, 2001. In some cases that is fine, but in others you may need it to mean the 15th, the last day of the month, or whatever. In my case, I want the beginning of the recession to be the first day of the month (March 1, 2001) and the end of the recession to be the last day of the month (November 30, 2001). Furthermore, the formulas that I gave above work great for March, but they fail for every other month because I hard-coded the number of characters. Let’s take the second problem first. I want to get rid of the hard-coded number of characters so that I can extract the name of any month. To do this, I’ll use the Find() function. Find() will be used to locate where the first space occurs. Using the example from above: =Find(” “,A1) will return 6. That indicates that the first space is the 6th character. (Note that I have left out the optional starting character argument. If you want to, you can start search at, say, the 10th character.) This tells me that the last character of the name of the month is the 5th character in the string. Now, to get the first N characters, I’ll do this: =Left(A1,Find(” “,A1)-1) That gives me the name of the month. To get the year, I need to use the Mid() function: =MID(A1,FIND(” “,A1)+1,4) This locates the first space, and then grabs the next 4 characters. Since the year, in this example, is always 4 characters I can hard-code the 4 without worry. The result is “2001”. I can now concatenate these results: =Left(A1,Find(” “,A1)-1)&” “&MID(A1,FIND(” “,A1)+1,4) which results in “March 2001” or “November 2001” depending on the date that is in A1. At this point, my problem is to convert “November 2001” into the date “November 30, 2001”. To do this, I could use the DateValue() function to directly convert the string to a date, but that will give me “November 1, 2001” and I want the 30th. So, I will use the Date() function instead. Now, Date() requires three arguments: Year, Month, Day. So, I need to supply each of those arguments. For example, I could do this: =Date(2001,11,30) Except that I would calculate those arguments by breaking the string apart using the text functions as described above. Unfortunately, there is yet another problem: Some months have 30 days, others have 31, and February might have either 28 or 29 days. Therefore, we can’t just set the Day argument to 30 as I did above. Fortunately, Excel has a function called EOMonth() that will figure out the last day of any given month and year. I can use this function as follows: =EOMonth(Date(2001,11,1),0) So, I gave EOMonth() the first day of November 2001, and it figured out that the last day of the month is the 30th. Ok, let’s wrap this up by combining all of the above. Suppose that cell A1 contains the string “November 2001 (IV)” and I want to convert that to a date. Specifically, I want the date to be the very last day of the month. The formula that I would use is: =EOMONTH(DATEVALUE(LEFT(A1,FIND(” “,A1)-1)&MID(A1,FIND(” “,A1)+1,4)),0) Note that I get away with using DateValue(), instead of Date(), because the EOMonth() function automatically figures the last day of the month for me. The formula can now be copied to other cells so that it can convert other dates automatically. Presumably, you wouldn’t go to all of this trouble if you only have a couple of dates to convert. However, if you have many (maybe hundreds or thousands) then this combination of string and date functions can save you a lot of time and effort.

Parallel Install of Office 2007 and Office 2003

2007-08-07T09:02:00-07:00

I’ve been using Office 2007 for about a year now, but I had it installed on a PC that I built last year to experiment with the Vista and Office beta releases. I’ve since upgraded those beta versions to the full RTM (release to manufacturing) versions, but I don’t use that PC as my day-to-day machine. If you are like me, you may need to run both Office 2003 and Office 2007, and it is a real time saver to be able to do that without booting up another PC. The purpose of this post is how to run both versions on a single PC. It is very simple, actually. Place the Office 2007 installation CD into your drive and it should automatically start the installation program. If not, browse the CD and double-click on Setup.exe. You will be prompted to enter your 25-digit (!!!) license key and then asked to agree to the EULA (end-user license agreement). After that, you will be presented with the following screen: In order to install alongside Office 2003, you must select the Customize button. As an aside, you should do this anyway so that you can choose exactly what gets installed. Clicking the Customize button will take you to a screen where you can choose how to do the installation: Notice that there are 3 choices: 1. Remove all previous versions - This will do exactly what it says; it will replace your previous version of Office with Office 2007. 2. Keep all previous versions - This will install Office 2007 while preserving your previous version of Office. If you choose this, you will be able to use either Office 2007 programs or Office 2003 programs, or both at the same time. 3. Remove only the following applications - This choice allows you to choose which applications get replaced. So, if you only want to have two versions of Excel, then you can uncheck Excel. That way you won’t have two versions of Word, Powerpoint, etc. I chose option 2, so I have complete installations of both versions. Before clicking the “Install now” button, you will want to go through the other tabs. Especially important is the Installation Options tab, as shown below: This is where you get to specify the features of each application that get installed. I suggest that you go through each of these carefully and choose your options. I have shown the drop-down list that appears under Excel Add-ins because the add-ins that ship with Excel (e.g., Solver) are not installed by default. You can install them later, but you will need the Office 2007 CD and we all know how easily they can be misplaced. Install everything that you may ever want so that you don’t have problems in the future. Disk space is plentiful and cheap these days. That’s all there is to it. Click the Install Now button and get on with it. For those of us that need both versions, this is a great way to do it. One final note: I assume that this requires a full version of Office 2007 and that an “upgrade” version won’t work, but I’m not sure of that.

Video: Using Controls to Change Chart Data

2007-07-30T09:13:00-07:00

One of the best things about Excel is its seemingly infinite flexibility. In this video I demonstrate how you can use controls from the Forms toolbar to change the data in charts. There are so many ways to use this technique for presentations that it was difficult to choose one. I decided to show a very simple example that compares some financial ratios for GE over time to the industry averages. The basic idea is that you use a control (a drop-down list in this example) to choose which of several data series to display in the chart. As part of the formatting of the control, you will select a “cell link” where the control will communicate to you which item has been selected. I use this number in the Offset function to grab the data. When you select other items from the list, the chart updates automatically. In this video I also demonstrate how to create a dynamic chart title. That is, the title of the chart will change to reflect the data that is charted. I have two versions of the video: a larger, Flash-based version is hosted on my academic Web site, and I have another version posted on YouTube. The YouTube version is presented here. I will post a written tutorial on the Web site soon.

Google Spreadsheets and Google Finance

2007-07-18T09:21:00-07:00

Call me an Excel bigot, if you must, but I haven’t really played with Google Spreadsheets much. I’m not a big fan of online applications. But, my mind is open and I recently learned of a really great feature: Google Spreadsheets can link to data in Google Finance! This means that you can have live data in your spreadsheet without needing a Bloomberg feed. You can also attach working spreadsheets to your Web (or Blog) pages. Let’s see how well this works: So, how does this work? Easy. First you will need a Google account. If you have a GMail address, you are good to go - just use the same user name and password to sign in. Then, go to http://spreadsheets.google.com and open a new worksheet. Once you’ve got that open, you want to use the GoogleFinance function. This function is defined as: GoogleFinance("symbol", “attribute") where “symbol” is a ticker symbol, and “attribute” is the name that Google uses for a particular piece of data. You can enter these directly in the function (as strings - surrounded by quotation marks), or they can reference strings in the spreadsheet. For example, in the spreadsheet above I am getting the company name by referencing the ticker symbol in column A and using the “name” attribute: GoogleFinance($A1, “Name") You can find out more about the available attributes at Google’s support page for this function or at the Google Finance blog. BTW, I didn’t see the “name” attribute listed anywhere but I tried it anyway. It worked! There are probably a bunch of undocumented attributes available, but you’ll have to guess at them. There are some negatives. First, as far as I can tell you cannot get historical data this way. Too bad. It would be fabulous if you could do something like: =GoogleFinance("Goog”,"PriceHistory", “7/18/2007") and then copy it down to get say five years of monthly prices. Alas, you can’t. Another negative is that there is a limit of 250 calls to the GoogleFinance function in any one spreadsheet. Despite the limitations, this is exciting functionality. I’m going to play with this some more. I’ll report back if I discover anything useful. I think that you can view my spreadsheet online. BTW, once you’ve created your online spreadsheet, you can easily export it to an Excel file (.xls) on your own PC at any time.

Loan Amortization Tutorial Added

2007-07-15T09:33:01-07:00

For some reason, I get quite a few requests for amortization schedules, so I have created a new loan amortization schedule tutorial and posted it to my Excel tutorials. It starts out by describing in detail how to amortize a loan using formulas, moves into using the IPMT and PPMT functions, and then creates a full amortization schedule. At the end, I made it a bit fancier by adding IF statements, conditional formatting, and charts.

You can access the tutorial at this link.

I also created a tutorial for the TI 83, TI 83 Plus, and TI 84 Plus calculators. One of the cool things about

The NPV Function Doesn’t Calculate Net Present Value

2007-06-19T09:53:00-07:00

One of the things that has always driven me nuts (because I have to repeatedly explain it in class) is that the NPV function in spreadsheets doesn’t really calculate NPV. Instead, despite the word “net,” the NPV function is really just a present value of uneven cash flow function. It has always been this way, so I’ve learned to live with it. I suspect that this goes all the way back to Visicalc (which you can download and run in a DOS window). Net present value is defined as the present value of the expected future cash flows less the initial cost of the investment. “Net” always means that something has been subtracted, so it is puzzling that spreadsheets have done this (there are actually a number of crazy errors that have been propagated over the years to maintain compatibility). In any case, there are two common ways to calculate the real NPV in Excel: 1. Use the NPV function, but leave out the initial outlay. Then, outside of the NPV function, subtract the IO. (Note, the initial outlay is often entered as a negative number, so it will actually be added.) 2. Use the NPV function and include the initial outlay in the range of cash flows. In this case, the “NPV” will be in period -1 so we must bring it forward one period in time. So, multiply the result by (1 + i), where i is the per period discount rate. You can download an example spreadsheet, or look at the picture below. The spreadsheet demonstrates three methods of calculating NPV in Excel. The first two correspond to the two methods mentioned above. The formulas are: 1. Method 1: =NPV(B1,B5:B9)+B4 2. Method 2: =NPV(B1,B4:B9)*(1+B1) Obviously, both give the same result and which one you use depends on personal preference. For the third method, I used an array formula that I think is somewhat interesting. Most people probably wouldn’t use this method as it would likely confuse others. Still, it is worth mentioning and it may lead you to solutions to other problems. What I am doing is using the PV function, but as an array instead of the usual way of using the function. This way, I can get the NPV in one step. The formula in B13 is: =SUM(PV(B1,A4:A9,0,-B4:B9)). Remember to enter it by pressing Ctrl+Shift+Enter, otherwise you will get a #VALUE error. You’ll note that I have two arrays in the function: The first is an array of the period numbers in A4:A9. The second is the array of cash flows in B4:B9. This will return 6 answers as you can see if you highlight the PV function in the formula bar and press F9. Surrounding it with the Sum function adds all six answers so that we end up with a single number (the NPV). Essentially, the above formula runs the PV function six times - once for each cash flow - and then adds the results. However, notice that I have included the initial outlay in my range of cash flows. This works because the NPer argument of the PV function is 0 for the initial outlay so the formula calculates the net present value as of period 0, instead of period -1 as we saw in “Method 2.”

Video: Creating Step Function Charts in Excel

2007-06-17T10:25:00-07:00

A couple of months ago, I created my first screencast video using Camtasia Studio from TechSmith. This is a great tool, but I need to work a bit on my video creation skills. Today, I posted the video on YouTube, and I hope that people will find it useful. The video shows how to create a step function chart in Excel.

In this case, I used the Fed Funds Target rate as my data source, but there are many other data series that could be used. Any data that is usually constant, but occasionally changes to a new level is a candidate for this type of chart. Watch the video and let me know

On Arrays and Accumulated Depreciation

2007-06-11T10:14:01-07:00

What could be easier, or more common, than calculating accumulated depreciation? Generally, in your spreadsheets you probably have each years’ depreciation expense listed in its own cell and all you do is add them up. Nothing wrong with that, but there is another way. I like to read the forums over at AnandTech and help out whenever I can. Not long ago, a user asked about calculating accumulated depreciation using the DDB (double-declining balance) function. He needed to calculate the accumulated depreciation to date for an asset so that the book value could be calculated. My solution was quite simple, but it makes use of arrays and I don’t think most Excel users are aware of this functionality. Set up the spreadsheet as shown below: We’ve got a simple setup, and nowhere is each years’ depreciation expense listed. Instead, we’ll calculate the accumulated depreciation in B6 using the DDB function. First, note that the DDB function is defined as: DDB(cost, salvage, life, period, factor) Note that the current period is listed in B4, so I could calculate the depreciation for Year 3 (just that year) with: =DDB(B1, B2, B3, B4) (leave out the optional “factor” argument for standard double-declining balance). The answer is that the depreciation in Year 3 is $360. But that isn’t what we really want to know. Instead, we want to know how much the total depreciation has been over years 1, 2, and 3 (or some other ending period). This is where the array comes in. In many functions, Excel will allow an array of values where it only asks for a single value. You can define the array as a comma-separated list contained in braces. In this case, to specify the “period” argument as an array, we would type: =DDB(B1, B2, B3, {1,2,3}) If you press the Enter key at this point, you will get only the first period’s depreciation ($1,000). So it seems that this trick doesn’t work. However, try this: Select three adjacent cells, say A8:C8 and then click in the formula bar. Type in the formula as shown above, but don’t press Enter. Instead, press Ctrl+Shift+Enter and you will get three answers ($1000, $600, $360). That gives you the depreciation for each of the first three years in separate cells. We’re not quite done yet. The goal is to get the accumulated depreciation for those three years using only one cell. We can do this by surrounding the array formula in a Sum function (select a single cell this time, say B6): =Sum(DDB(B1, B2, B3, {1,2,3})) and press Ctrl+Shift+Enter to insert the function. You will see that the answer is $1,960 as shown in the table above. The three answers from DDB get fed into the Sum function, resulting in our single answer. Note that when you enter an array formula like this, Excel will display it in the formula bar like this: {=Sum(DDB(B1, B2, B3, {1,2,3}))} Do not type the outer braces. They are there only as a visual reminder that this is an array formula. Pretty cool, huh? Sure, but we aren’t done yet. The whole purpose of using spreadsheet programs (as opposed to paper) is flexibility. Notice that the {1,2,3} is hard-coded, so it will only work for that time period. The primary purpose of B4 (Current Year) is to provide the number of the last period for which we want to calculate the accumulated depreciation. If I change B4 to 4, the answer in B6 will still be $1,960 (i.e., incorrect - it should be $2,000). I need some way to dynamically change the array of years for which I want to calculate the accumulated depreciation. This is where the problem gets interesting. I need to dynamically generate an array of values from 1 to N, where N is the number specified in B4. To do this, I’m going to use two functions: Row and Indirect. The Row function simply tells you what row a cell address refers to. For example, Row(A10) will return 10. If I create an array formula: =Row(A10:A13) I will get {10,11,12,13} as the answer. You can type that into the formula bar and then, without pressing Enter, highlight the formula and press F9. That will show you the result. So now I know how to generate an array from 1 to N. Just do something like, =Row(A1:A3). The only problem is that, again, the address is hard-coded. This is where Indirect comes into play. Indirect is a great little under-used function. Basically, what it does it to take a cell address as a string and return a reference to that cell address. So, if I type =Indirect("B4") into a cell, the return value will be the value from cell B4. It is an indirect way to get to that value. If I changed the string, the return value would come from some other cell. I’m going to use the function a little bit differently, but that’s the idea. Here’s my strategy: I want to generate an address of a range from A1:A(whatever). So, I’ll build up a string that I can feed to the Indirect function, and then feed the output from Indirect into the Row function. To build the string, I’m going to take the value from B4 (the current year). A formula that will do this is: ="A1:A"&Text(B4,"#"). In the original spreadsheet (above) B4 = 3, so that will result in the string “A1:A3”. If I change B4 to 5, then the string will change to “A1:A5”. Now, if I type: =Row(Indirect("A1:A"&Text(B4,"#")) the output will be {1,2,3} or {1,2,3,4} or whatever, depending on the value in B4. Again, test this in the formula bar using F9. You can’t type that into a single cell and get an answer because it is an array formula. So, here is the final formula for B6: =Sum(DDB(B1, B2, B3, Row(Indirect("A1:A"&Text(B4,"#")))) Take a look at the example spreadsheet and let me know what you think. I think this demonstrates a number of very useful Excel techniques.