Excel IF Function Horror Stories
March 9, 2021
Thank you for reading the news, and you'll get the next email on March 23rd.
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
The AVERAGEIF function is similar to SUMIF and COUNTIF -- you can calculate a result based on criteria.
If you need to get an exact match for the criteria, just put the word in the formula. For example, this formula calculates the average quantity per order, if the item is "Pen". (Note: upper and lower case are treated equally)
To make the formula more flexible, add one or more wildcards. For example, the * wildcard represents any number of characters (or no characters). You can put it before and after the word, to include all items that have "pen", anywhere in their name:
Do you have any IF formula horror storiess? Here are two examples that I saw last week:
1) In a time sheet workbook, an IF formula had 17 employee names hard coded in it, and their hourly pay rates.
2) A ranking workbook had this monster of a formula, with 8 nested IF functions:
Most formulas like that should be lookups instead of IFs. For example, here's the formula I'd use for the ranking workbook, with a small lookup table on another sheet:
I also posted a short video, showing how to build a simple IF formula. You can show it to your co-workers, if they like to build IF function horror stories!
Here are a few Excel-related articles that you might find useful or interesting.
Power BI: This is a fascinating way to browse a collection of Excel articles! Simon Hurst explains how the IEACW's new Spreadsheet Knowledge Base was built, using Excel and Power BI. And if you'd like to try Microsoft's free Power BI Desktop tool, Matt Allington shows how to get started. (Level - All)
Microsoft: Join Microsoft's Excel team for an Excel Reddit AMA (Ask Me Anything) on March 10 at 10 AM (Pacific Time). The whole Excel team will be there, answering your questions, so don't miss it! You can download the ics file on this Microsoft page. (Level - All)
Also see: Previous Newsletter Issues
If you have a basement, I hope it isn't as cluttered as ours! Last week, I found this treasure -- Lotus 1-2-3 for DOS, Release 2.3. Wikipedia says that version was released 30 years ago, in 1991. The big excitement was its WYSIWYG interface, and all the fancy graphs you could make! No wonder it was "The world's most popular spreadsheet".
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2021/20210309ctx.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: April 4, 2021 10:02 PM