Look Who it Was Random video

Excel - Get Averages for a Set of Numbers - Excel - Episode 1657

1,216 views · Published 1 March 2013 · 6:37 · Indexed 20 October 2026

Channel: MrExcel.com · 2013 · Education

Watch on YouTube

Microsoft Excel Tutorial: Taylor has to find Averages within Ranges of the Data Set.

Welcome back to another exciting episode of Dueling Excel, where Mike Girvin from Excel is Fun and I, Bill Jelen from MrExcel, go head to head to find the best solution to your Excel problems. In this episode, we tackle a question sent in by Taylor about finding averages for a set of numbers in different categories. But to make it even more challenging, we also need to find the averages for specific ranges within each category.

I start off by using a pivot table to organize the data and group the numbers into the desired ranges. This allows us to easily calculate the averages for each category and range. However, Mike has a different approach using formulas that may surprise you. He creates a table with inputs for the criteria and uses the AVERAGEIF function to calculate the averages for each category and range. He also introduces us to the new and improved AVERAGEIFS function, which is much faster than using SUMPRODUCT.

We also discuss the importance of using Charles Williams' Add-In, which has over 90 new functions including advanced vlookups. We highly recommend checking it out and incorporating it into your Excel arsenal. And don't worry, we'll be sure to teach you how to use these new functions in future episodes.

Thank you for tuning in to another exciting episode of Dueling Excel. Be sure to join us next week for more Excel tips and tricks from MrExcel and Excel is Fun. And don't forget to leave a comment below with any suggestions or questions for future episodes. See you next time!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Table of Contents:
(00:00) Using a pivot table to find averages for a set of numbers
(01:30) Using conditional formatting to visualize the data
(01:54) Using formulas to achieve the same result
(04:00) Using IFERROR to simplify the formula
(05:00) Discussion on the benefits of using AVERAGEIFS, SUMIFS, COUNTIFS
(06:05) Mention of Charles Williams' Add-In for advanced vlookups
(06:27) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Advanced vlookup techniques
Average calculation
AVERAGEIF function
Charles Williams (Excel expert)
Conditional formatting
COUNTIFS function
Excel tutorial
Grouping data
IFERROR function
MVP summit
Pivot table
SUMIFS function

 Bill 'MrExcel' Jelen immediately sets up a Pivot Table - Mike 'ExcelisFun' Girvin chooses to go with a different method using =AVERAGEIF and more.
Mike and Bill also encourage us to check out SpeedTools Add-in from http://fastexcel.wordpress.com featuring the work of Charles Williams -'The Master of Fast'- with dozens of functions including Fast, Efficient VLOOKUPs. Follow along with Episode #1657 to solve the Average question and to learn more about the New Add-in from Charles Williams.

Join the MrExcel Message Board discussion about this video at  https://www.mrexcel.com/board/threads/1153479/

More from this channel