Beware of Nested IFs
September 22, 2015
Understand circular references, avoid nested IFs, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
The IF function is invaluable, and you can use it to run a test, before doing something else. For example, check if a cell is empty, before calculating a total. IF can be combined with other functions too, to create powerful formulas.
However, I've seen some complex and messy nested IF formulas, that are hard to understand, and difficult to maintain. Use the IF function when you need it, but often another function, such as VLOOKUP, could do the job better.
Jon Peltier has released Version 3.0 of his Excel Charting Utility. There is a $10 discount if you buy before September 30th, and additional discounts if you want to upgrade from a previous version.
The new version will work on both Windows and Mac systems, and will save you hours when building complex Excel charts. If you build Excel charts more than occasionally, you need this!
Here are a couple of Excel articles I read recently, that you might find interesting and useful.
Circular References -- If you've used Excel for more than a few weeks, you've probably seen a Circular Reference warning at some point. Charles Williams takes a look at circular references, explains why they calculate slowly, and why you should avoid them. (Level - Intermediate)
Office 2016 Release -- Office 2016 will be released today, September 22nd, and Microsoft's Excel Team have outlined which Business Analytics tools are included in the different Office plans. It was very confusing in past versions, so I hope it's easier to find what you need in Office 2016.. (Level - Intermediate)
More Excel Articles -- Find links to more articles in the latest Excel Weekly Roundup on my Contextures blog. And for a bit of spreadsheet humour, you can see what people are saying about Excel, in my weekly collection of tweets.
Next Wednesday, September 30th, it will be Excel's 30th anniversary -- it was first released on Sept. 30, 1985. Do you remember when you started using Excel? Was it decades ago, or just this year? Was it exciting or painful? What were you working on?
If you'd like to share your earliest memories of Excel (with or without your name), please email me your story, and I'll try to include it in an Excel anniversary compilation for my blog on September 30th. (edited if necessary).
The earliest Excel files that I can find on my old 3.5" disks were dated April 1987. My old Mac Book, which still runs, has Excel 3.0 installed, and I was able to open one of those old files. The menus and toolbars look different from what we see now, but the basic structure hasn't changed.
NOTE: If you have any problems with the links in the email, you can see this newsletter on my website -- copy this link and paste it into your browser: https://www.contextures.com/newsletter/excelnews2015/20150922ctx.html
That's it for this week! If you have any comments or questions, send me an email.
Last updated: December 25, 2019 1:55 PM