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
Curt – I didn’t know about this function, so thank you for this! Users have a habit of deleting rows for reasons like (“oh, the entry in col D was wrong so I deleted the row and tried again”…), so running summaries on another page based on this logic makes life easier than running macros to replace all the snafu’d formulae that occur when this sort of thing happens. Which will make my boss happy due to reduced loud swearing coming from my desk.
Thanks! :-)
That’s a very useful article in the Daily Dose. I discovered the use of OFFSET for this kind of table a few years ago, and it has greatly reduced frustration and swearing from my workspace. The linked article about lists and tables is also a good one; I make great use of lists in Excel 2003, and think it’s the one feature that for me made upgrading to 2003 worthwhile. It’s good to have a link to a clear article about lists. I’d like to see what you say about the new Tables in 2007.
LOL! Interesting article. Though, it provides a certain credence to “Spreadsheets are the devil”
While the demonstrated application may be practical it also illustrates why spreadsheets are the devil’s work. Even without the requirements of SOX, the first thing I learnt in my introductory accounting course was that, once recorded, a financial transaction is *never* *ever* altered. The correct way to “fix” a problem is to use one — I prefer two — additional entries. The first reverses the previous error, the second provides the correct data.
You will see this in action at many places, not just in the financial context. At a grocery store (at least all the ones I’ve ever used) if the scanner makes an error in recording a product, the checkout clerk doesn’t go in and fix the error. Instead, another transaction (or two) are used to correct the problem. For example, if the scanner rings an item twice, the clerk will create a separate credit for one unit.
OFFSET ig useful. But it has a disadvantage: It doesn’t produce Precedents. I’ve taught students and collegues to be in the Total cell and click Tools – Formula Auditing – Trace Precedents multiple times to see that everything’s there. Offset formulas are stopping this tool.
Great trick, great article, just wanted to mention this.
Best wishes Harald
An interesting work around, on the other hand you can enter the opening balance in B4 and enter the formula:
SUM($B$4:B4)-SUM($C$4:C4) in cell E4 and copy the formula down column E as far as you like.
Deleteing rows has no effect except to remove the transaction (To be fair, deleting row 4 will mess things up…).
Inserting a row also works correctly for the running balances below the inserted row even if you don’t coppy the column E formula into that row.
There is the additional benefit that if you enter text into any column B or C cell for come reason (e.g. ‘Void’) the formula still work whereas with the Offset you get a VALUE! error.
I use a somewhat similar alternate to this. Instead of using the offset function, I define a name. In this case, we need a formula to always refer to the cell above, so I’d define my name as follows:
Go to cell A2, select Insert -> Name -> Define. Name = “UPONE”, Refers To = “=A1?. The important part here is that it is not an absolute reference. Now when you enter “=UPONE” in any cell, it will always return the value in the cell directly above.
Now I would change your formula in E5 to read “=UPONE + B5 – C5? for the same result. I left the B5 and C5 as is since the error you describe isn’t applicable unless you delete a column–in which case you’ve really messed up and would want to see the error (except for if you’re my former boss and would love the formula to work with the expenses column missing).
Harald, you’ll find that this technique works with formula auditing. I also think it is much more readable, though I’ll admit that if you need a define name called something like UPTHREEANDRIGHTELEVEN this may not be ideal.
I find this technique really works best when you have a formula like “=SUM(A1:A100)” in cell A101. When someone adds a row in between A100 and A101 the formula will not pick it up in the total. However, if you have the formula as “=SUM(A1:UPONE)” it would always work.
I work with very large running totals and have always used the method that Ian Scott put forth.
I have not used the offset, I think it is a good work around but I need the ability to audit the sheet in a big hurry and if I cannot use Trace Precendents I would be lost. And I never delete a row I only change by adding or subtracting in another row. Sorta like a bank Credits and Debits
zfraile,
Nice perspective! Although my first reaction is to take Ian’s approach, many coworkers of mine struggle with mixed absolute and relative references. The dreaded two-index-finger-typing boss may not understand the inner workings of UPONE, he will appreciate the fact that it always does what it says it does.
-Jason
UPONE is really a neat way of doing it, thanks.
Thanks to everyone for your alternative approaches to the problem. I agree, Tushar, that you shouldn’t delete rows if your list could be audited. I only intended this technique to be used in informal situations. I use QuickBooks (at my accountant’s insistence) to maintain my auditable corporate financial record; I use these Excel lists to provide an informal snapshot of my future cash flow.
Curt
I forgot to note that I really like zfraile’s elegant technique of using a named range defined with a relative reference. In the context of this cash flow worksheet, you could define the named ranges PREVIOUSBALANCE, CURRENTINCOME, and CURRENTEXPENSE.
Curt
Quickbooks? Yuck – me too. Have you downloaded the SDK yet? I’ve been planning to do some posts about using the SDK with VBA, but boy is it a lot of work.
I haven’t even considered using the SDK. I have a very simple business, and I don’t want to add any complexity where none is required.
Curt
Quickbooks!! I used it for half a year on the suggestion of my accountant. It was a waste of money and time, I could never figure out how to get the views I could do in five minutes in Excel, and I discovered I was carrying two sets of books, Quickbooks and my trusty old Excel. Finally I asked the accountant, and it turns out he recommended it because he thought it would make my life easier, he only needed numbers in a table, and he preferred Excel worksheets. Duh, Ron, I’m an Excel programmer. Quickbooks came off the PC.
If anyone wants Quickbooks 2006 for the cost of shipping, it’s yours. Email me at jp at peltiertech dot com.
The UPONE defined name is clever, but it needs to be defined as =!A1 if it’s going to be used in multiple worksheets. If defined as =A1, Excel adds the worksheet name to the definition.
For more general cases in which defined names would be awkward, there are alternatives to OFFSET, which is a volatile function, so a drag on recalc speed if used extensively in large workbooks. INDEX calls can usually be used instead, even if they have to use $1:$65536 as first argument and call ROW() and COLUMN() in the other arguments.
Re my QuickBooks offer, I have a winner. The disk and a couple books are on their way in this morning’s mail.
Curt wrote, “I agree, Tushar, that you shouldn’t delete rows if your list could be audited.”
Curt, my intent wasn’t to comment on the specifics of your solution but rather on the more general issue of the trustworthiness of Excel based solutions. Nor is it meant to knock Excel. It is the “free form” nature of spreadsheets (coupled with the power of programming languages like VBA) that gives them tremendous power and usability. But, that comes at a price, a piece of which is the lack of an audit trail. Another component is understandability and trust in the result. If you are the senior executive who has to sign off on something and risk criminal or civil prosecution if it turns out you “lied,” I imagine it would add an element of uneasiness in some.
[Recently, I was asked to help with a workbook that “well, it works almost all the time but sometimes the numbers don’t seem quite right and we cannot figure out what’s going on.” It took me several days to just understand what was what and I still wouldn’t bet any money on that! References to cells here and there, in this worksheet and that, up and down, back and forth, left and right, named formulas, named ranges, names that mapped to error values, very hidden names, gooblydegook array formulas designed to “save” a cell or two, and a godforsaken amount of VBA with almost all global variables. With a “solution” like this, it isn’t SOX I would worry about — and, forget about mission critical stuff. *Any* business decision that relied on this workbook would be, IMO, a crap shoot.]
Has anybody tried this on PocketExcel???
I’ve tried a bunch of methods the one that works like this is =SUM(E4+B5-C5)
Thanks!
Eek –
First of all, putting SUM() around an otherwise healthy formula is redundant and unnecessary. And redundant.
Second, what if you delete the 4th row, or manually rearrange rows? Your formula loses its reference, which was Curt’s whole point.
I use the offset() function like this between rows, to prevent errors when deleting rows. However, I don’t think I’d ever use the offset() function like this for picking up data on the same row. If you were to insert an additional column, say for an account number or additional description, or move the income or expense columns, all of your running subtotals would be hosed. I just don’t see any gain to using offset() to pick up something on the same row.
Thanx a lot, i was facing same problem and now solve with help of your kind guidance.
The offset function really works.
thank, bright idea. off-set function is best way out from the problem.