You've never used this handy Excel function
March 10, 2015
I'll bet that you've never used Excel's TRIMMEAN function in one of your workbooks. It's a handy way to calculate an average on a set of numbers, and exclude some of the highest and lowest numbers. See how it works, 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.
Have you ever used Excel's TRIMMEAN function? It can calculate an average that excludes the outliers -- a specific percentage of the highest and lowest numbers in the dataset. If you have some data that is throwing off the average, try TRIMMEAN, as an alternative way of looking at the results.
Read the details on my blog, and watch the video, to see how it works. I don't use those conditional formatting color scales too often, but thought it would help in this example: Ignore Outliers with Excel TRIMMEAN
Here are a few of the Excel articles that I read recently, that you might find useful:
Here are some upcoming events, courses, recently published books, and other new items, related to Excel:
It's a little trickier to use an Advanced Filter, instead of an AutoFilter, but it can be much more powerful and flexible. In this example, I typed a short list of products on a worksheet. Next, I typed a COUNTIF formula at the top of the worksheet, in the criteria area for the filter. When I apply the filter, it only shows the rows that contain one of the products from my short list.
Click here to watch the short video that shows how the advanced filter works: Filter For Items in a List
A few years ago, I got this Galileo thermometer as a gift, and I love the way that it looks. It doesn't give the temperature with digital accuracy, but the balls with coloured liquid are much nicer to look at than a digital display.
The lowest floating ball shows 72F as the indoor temperture, which is perfect. Too bad it isn't the same temperature outside!
This week, I'm pleased to recommend Excel University -- an online Excel training course designed specifically for accountants. I went through several of the modules, and was very impressed by the content, delivery, and course structure.
The course focuses specifically on the Excel features, functions, and techniques that are most useful to accounting professionals. The training is practical and the concepts are easily applied to your workbooks. You'll learn how to get your Excel tasks done more quickly, how to save time, and how to automate your recurring-use workbooks.
Click the link to learn more about Excel University.
That's it for this week! If you have any comments or questions, send me an email.
Last updated: January 26, 2020 4:15 PM