Strange Excel Functions
June 18, 2019
You'll get my next newsletter in 2 weeks -- we're on the summer schedule now.
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
There are lots of sample files on my Contextures site, and I always zip them. Someone asked if I could leave the files unzipped, but I've decided to leave things as they are. You can read the full story on my Contextures blog, but the main reasons are preserving the links, and getting through firewalls.
While I was deciding whether to keep zippng the files, I created a macro to quickly list all the files in a folder, with their name, size, and dates. You can download a sample file with that macro from the Excel Folder File Macros page on my Contextures site.
Another macro on that page lists Excel files only, and includes the Content Created date. That can be quite different from the Date Created that you see in Windows Explorer.
Excel has two functions with 1-letter names -- T and N -- that you've probably never used. Here's a quick look at them, and please let me know if you've found a good use for either function. I've used N a few times, but not T. You can download the T and N sample file to see the formulas.
T Function: If a cell contains text, it returns that text. If the cell contains a non-text value, it returns an empty string. In the screen shot below, you can see the difference between the T function and ISTEXT, which returns TRUE or FALSE, so you need a longer formula to get the text.
N Function: This function returns a value converted to a number. In the screen shot below, you can see results for N, ISNUMBER and a double unary (two minus signs). N is handy if you want to convert TRUE and FALSE to numbers, or see the numeric value of a date.
Another use for N is adding hidden notes in a cell. Because N returns a zero for text, you can add it to the end of a formula, without affecting the result. The note can be seen in the formula bar, but doesn't add a cell marker, like a normal comment would.
=SUM(B3:C3) + N("Store01 closed in June")
Here are a couple of Excel articles that you might find useful or interesting.
Spreadsheets - This article has spreadsheet tips for journalists, but it's an interesting read for people in other professions too. The formula for % difference is correct, but the explanation is confusing. (Level - All)
Excel Sessions - If you didn't make it to last week's Microsoft Business Applications Summit, you can watch some of the Excel sessions online. Click the Sessions on Demand heading, choose a category, and then choose Excel in the Product drop down. (Level - All)
We had a cool, wet spring, and everything seemed to bloom late this year. Our lilac shrub finally has blossoms, and brightened up our back yard. The sky was clear blue for a few days too, and the Toronto Raptors won the NBA championship last Thursday, so spring is ending better than it started!
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2019/20190618ctx.html
I'll also post any article updates or corrections there.
That's it for this week! If you have any comments or questions, send me an email.
Last updated: June 15, 2019 12:12 PM