I’ve presented two one-hour webcasts over the past two months that might be of interest to beginning and intermediate Excel 2007 users. In February, I demonstrated Excel 2007 tables (the updated version of Excel 2003 lists); on April 10, I covered Excel 2007 charts, shapes, and SmartArt diagrams. The material’s not that advanced, but if you know of anyone who could use an overview and demo of either topic, be sure to pass along these links:
I bought a couple of books for my brother last week, but he’s misplaced his checkbook and couldn’t send the money. Sound like your brother? Maybe. J In this case, Doug decided to send the money using PayPal. He insisted that I receive the entire $85 he owed after PayPal took its cut, overestimated the fees, and sent me a couple of dollars more than the books cost. It’s no big deal, but I thought it would be interesting to put together a quick Excel worksheet that enables you to calculate how much you need to send so a recipient gets the proper payment after an intermediary deducts its fees.
The problem with this type of calculation is that it requires trial and error to find the proper value. As an example, consider the following worksheet:
Cell C2 contains the amount the recipient gets after fees, cell C3 calculates the fee (in this case, $0.30 plus 2.9% of the payment), and cell C4 displays the amount you have to send to make cell C2 come out to the right amount. Sure, you could plug values into cell C4 until you get the desired result, but you can also use Goal Seek to have Excel do the work for you.
To start Goal Seek in Excel 2007, display the Data tab and then, in the Data Tools group, click What-If Analysis, and then click Goal Seek (in Excel 2003, open the Tools menu and click Goal Seek). The Goal Seek dialog box appears.
Type the address of the cell you want to set to a target value in the Set Cell field (C2), type the target value in the To Value field (in this case, 100), and then type the address of the cell Excel needs to vary to find the target value in the By Changing Cell field (C4). When you click OK, Goal Seek attempts to find an input value that produces the desired result. In this case, it was able to find a solution.
Click OK to retain the value in the worksheet, or click Cancel to return the worksheet’s previous values.
I use Excel to maintain my projected personal and corporate cash flows based on my current balance and projected income and expenses throughout the month.
Column B contains income, column C contains expenses, and column E contains my running total. When I first created my worksheet, I used formulas such as =E4 + B5 – C5 to calculate the running balance (in this case for cell E5) after figuring in some income or an expense. The problem came when I deleted a row. For example, assume that I deleted the 8/2/2006 transaction from the list in the previous graphic.
When you delete a cell referred to in a formula, Excel displays the #REF! (bad reference) error code. Editing the formula in the first error cell causes Excel to fix the problem in every affected cell, but there is a way to avoid the errors entirely. To do so, create an OFFSET function to look up the previous balance, income (if any), and expense (if any). Here’s the formula I use for cell E5:
=OFFSET(E5,-1,0)+OFFSET(E5,0,-3)-OFFSET(E5,0,-2)
Managing running totals and balances using Excel 2003 data lists, or Excel 97-2002 worksheets, is somewhat involved. You can find a free article documenting the process on my web site.
All the best.
Curt