Excel

Data Validation

2008-04-24T09:43:00Z

I received a phone call from a Marketing Team wanting to know how to create a drop down list within a cell. Data Validation is the perfect way to accomplish this.

From the Data menu, select Validation. A screen similar to the following should appear:

Under the Allow: selection, choose List. In the Source: drop down, type in the items you wish to have appear in the cell, separated by a comma. It's that simple.

Interest

2008-04-21T03:42:00Z

I just got off the phone with a customer who wanted to know how to calculate compound interest. There are two easy ways to accomplish this in Excel. First, on a line by line basis, secondly, using exponents.

Line by Line:

Download a copy of the spreadsheet to follow along. Cell A2 contains the amount you are seeding your account with. Cell B2 is 1, the first year of interest. Cell C2 is the interest you expect to earn that year. Cell D2 is the result of multiplying cells A2 and C2, returning the interest in dollars earned. Cell A3 is the result of adding cells A2 and D2, capital plus interest earned. Cell B3 is the result of adding one to cell B2, increasing the year by one. Cell C3 is set to equal cell C2, which assumes the same interest rate every year. Cell D2 can be copied to cell D3 as the formula will remain the same. Copy cell range A3 through D3 down as far as you would like to the see the compound result.

Exponent:
The above can be accomplished using exponents. The above layout is essentially each year compounding, or building off the prior year. The formula to see the result of a multi-period compound effect is 1 plus the interest rate to the power of the number of periods, multiplied by the initial capital. In our example above, we can enter the formula =1.05^20 * 1000 to receive the same result as we did on the line by line basis.

Parsing

2007-04-01T06:14:00Z

Excel has numerous functions for parsing data. Frequently, clients will call me because they need to rearrange data in Excel or extract only certain information from a cell. Let's say a client would like to take the first and last name of their list and put the first name into column B. This is easily accomplished using functions like left & right. In our example, cell A2 will contain the name John Doe.

To get only the first name in cell B2, we can enter the following formula.

=LEFT(A2,FIND(" ",A2)-1)

If you entered the formula exactly as it appears above, you should see the name John in cell B2. The first parameter sets the string that we would like to extract data from, in our example, we are specify cell A2, or John Doe as our string. The second parameter set the number of characters starting from the Left that we would like to return. Since we know first and last name are typically separated by a space, we use the Find function to determine where the space is in the string. We do not wish to return the name and the extra space (although we wouldn't notice it), so we subtract 1 from the Find function.

For the next lesson, we'll put up a post on how to extract the last name using the Right function.