Contextures News 2021-03-09

Excel IF Function Horror Stories

March 9, 2021

Formula wildcards, IF horror stories, 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.

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.

Formula Wildcards

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)

  • =AVERAGEIF(A1:A10,"Pen",B1:B10)

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:

  • =AVERAGEIF(A1:A10,"*Pen*",B1:B10)

There are more Average function examples on my Contextures site, and my new AVERAGEIF function video is on that page.

AVERAGEIF wildcard options

IF Function

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:

  • =IF(OR(ISBLANK(A2),ISBLANK(B2),ISBLANK(D2)),, IF(D2=1,B2, IF(D2=2,B2*1.5, IF(D2=3,B2*2.1, IF(D2=4,B2*3.3, IF(D2=5,B2*5.1, IF(D2=6,B2*6.2, IF(D2=7,B2*8,"Unknown"))))))))

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:

  • =IFERROR(IF(COUNTA(A2,B2,D2)<3,"", B2*VLOOKUP(D2,tblRank,2)), "Unknown")

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!

if function checks for empty cell

Excel Articles

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)

Challenge: Robert Gascon has posted the solution to his latest Excel formula challenge, which I linked to in the last newsletter(Level - Int/Adv)

Also see: Previous Newsletter Issues

Lotus 1-2-3

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".

weekly photo

NOTE: To read this newsletter online, paste this URL into your web browser:
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.

Debra Dalgleish

Debra Dalgleish

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.

contextures newsletter info


Last updated: April 4, 2021 10:02 PM