Saturday, March 08, 2008
Testing a String for Proper Case
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
Comments (1) • Permalink • Tell-a-Friend
Tuesday, June 19, 2007
The NPV Function Doesn’t Calculate Net Present Value
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
Comments (1) • Permalink • Tell-a-Friend
Monday, June 11, 2007
On Arrays and Accumulated Depreciation
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
Comments (0) • Permalink • Tell-a-Friend
