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.
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)
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.
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.
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.
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!
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 @ contextures.com
Note: I am an affiliate for some of the products mentioned in this email, and earn a commission on the sales.
Last updated: March 15, 2017 11:52 AM