|
NOTE: This week's tip applies to many versions of Excel
Do you want to add some power to averaging numbers?
Formulas are very powerful and save us incredible time performing our respective jobs. This week's e-MOSTip is on how to use the AVERAGEIF statistical function. It's going to be easy and worth knowing so read on for the details.
The power behind AVERAGEIF versus it's family member AVERAGE is the ability to include a criteria by which a specified range of values should be averaged.
One of the first parts in using any function is to know the syntax or order that the formula accepts arguments. The syntax for the AVERAGEIF function is given below and the explanation for how to use this time-saving function follows.
Syntax
=AVERAGEIF(range,criteria,average_range)
NOTE: function names are not case sensitive so you may type them in lower or uppercase.
Scenario: You have a list of real estate agents reporting sales from various office locations. You want to find out the average sales of all agents reporting sales from the Birmingham office. The AVERAGEIF function will provide you with the answer quickly.
The example formula below is the formula used in the sample spreadsheet shown in Figure 1 to find out the average sales of all agents reporting sales from the Birmingham office.
Example =AVERAGEIF(D5:D18,"Birmingham",C5:C18)
See Figure 2 for the answer the above formula returns and read below for an explanation of the parts of the formula. Explanation of the three parts of the formula example:
First part of the formula (D5:D18)
Explanation: The range where the criteria Birmingham can be found.
Second part of the formula ("Birmingham")
Explanation: The criteria being searched for within range D5:D18
Third part of the formula (C5:C18)
Explanation: The range to be averaged
End Result:
All values in range C5:C18 are averaged based upon the criteria of Birmingham locations which are entries located in range D5:D18. There are five Birmingham entries in range D5:D18, so five values from range C5:C18 are averaged.
Try out your new knowledge...
Remembering is doing so why don't you try out your new knowledge by downloading the project file and create an AVERAGEIF formula that averages all sales from the Anniston location.
Do you need a little incentive to reinforce your new knowledge? If so, complete and submit your project file to me this week and I will place you in a drawing for a free MOSTraining Excel Webinar.
ANNOUNCEMENT:
Upcoming Training on Excel Formulas
MOSTraining will be releasing a series of economical Webinars on "Mastering Excel Formulas". These Webinars will be hands-on and instructor-led. You can login from the convenience of your desk. If you are interested in being notified when this series launches please contact me at Train@MOSTraining.com.
Formulas are one of the biggest struggles for people using Excel and it is among one of the most important things to learn in order to be more productive when using Excel.
Have a great week!
Member of:
American Society for Training and Development
National Speaker's Association
| 
