Contextures News

Skip the Excel IF Formula

September 27, 2016

No need for an IF formula, and more, in this week's Excel news. Visit my Excel website for many more tips, tutorials and videos -- let me know if you're looking for something and can't find it there.

Count Filled Cells

My daughter called me with an Excel question on Friday -- she needed to calculate how many people were coming to an event. Her worksheet has last names in one column, and guest names in another column.

She thought an IF formula might be needed, but the COUNTA function is the right tool for this job. It will count the cells that have anything in them. So, we can check the Last Name column and the Guest column, to get a count of 1 or 2. I used a named Excel table in this example, so the column names are in the formula. You could use cell references instead.

=COUNTA([@NameL],[@Guest])   or   =COUNTA(C2,E2)

open excel files

How Far Is It?

Recently, someone asked for help with Jerry Latham's distance calculator workbook on my website. You enter a latitude and longitude, and the workbook shows the distance between those points. The web page has details on how the formulas work and links to reference sites. Or, just skip all that, and download the workbook. You don't have to understand all the calculations in order to use the file.

For testing, I entered Toronto's CN Tower as the origin, and Toronto Zoo as the destination -- they're about 26 km apart. You can get decimal address coordinates from the NASA website, and convert them to degrees/minutes seconds on the FCC website.

longitude and latitude workbook

Data Cleansing Course

Time is running out, if you want to watch the FREE 3-part Data Cleansing video series from John and Oz at MyExcelOnline. The final video topic is: Consolidate Excel Workbooks & Worksheets using Power Query.

Also, John and Oz have opened registration to their new course: The Ultimate Excel Power Query & Data Cleansing Online Course. It has great content and bonus material, and you'll get 20% off with the coupon code CONTEXTURES -- registration closes this Friday, Sept 30th.

Excel Articles

Here are a couple of Excel articles I read recently, that you might find useful.

No Tableau - Gus Cavanaugh explains why Tableau is good for exploring data, but not useful for data analysis. It doesn't help clean your data either! The article is from last year, but still relevant. (Level - Intermediate/Advanced)

Avoid Errors - After recent reports of Excel causing errors in scientific studies, Doug Jenkins shows how you can easily prevent those errors from occuring. It's good advice for all kinds of data, not just science! (Level - All)

Also, see all my Excel products on my Contextures website.

Fitness Centre

Finally, after a couple of years of construction, the local Community Centre has re-opened. There is a time lapse video that shows all the stages, and you can see the seasons change too. The building has a shiny new fitness centre, and we've signed up for an annual membership.

So far, I've only used the walking track, but next week we have a starter session, to learn how to use the fancy fitness equipment. I hope it works better than my iPhone, which only recorded 665 steps on Friday, after 30 minutes of brisk walking. Maybe I was going backwards part of the time!

weekly photo

That's it for this week! If there are topics that you'd like to see covered in future emails, please let me know.

ddalgleish @

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

Note: I am an affiliate for some of the products mentioned in this email, and earn a commission on the sales.


Search Contextures Sites

Excel Data Entry Popup List





Last updated: March 15, 2017 11:52 AM