How to Average Values Satisfying Specified Conditions with AVERAGEIF and AVERAGEIFS Functions

Excel possesses many different functions that can help you achieve varies purposes. What’s more, different functions have different criteria settings. Therefore, now we will use the AVERAGEIF and AVERAGEIFS functions to average values satisfying specified conditions.

In your daily work, the average value is one of the performance measures. Besides, according to the average value, you can also analyze the tendency of your products or services in the market. And in the image below, this is a sales records worksheet of some sellers.A Worksheet of Sellers

And you will find that it is very annoying to calculate the average of people or month or the products. Thus, you can use the function of the AVERAGEIF and AVERAGEIFS functions to get your target.

The AVERAGEIF Function

If you want to know the average sales of one product, such as apple, you can use this function.

  1. Select a blank cell.
  2. Input the formula into the cell:

=AVERAGEIF(C2:C24,”apple”,D2:D24)

  1. Then press the key “Enter” on the keyboard. And then you will see that the average of apple sales volume is in the cell.

And this result can help you with your next work.Average of Apple

The AVERAGEIFS Function

And now if you want to know a person’s sales volume of certain product, the AVERAGEIFs function can come to help you. For instance, if you want to know the average apple sales volume of William, you can use this function.

  1. Click any of a blank cell where you want to put the result.
  2. Input the formula into the cell:

=AVERAGEIFS(D2:D24,A2:A24,”William”,C2:C24,”apple”)

Because in this sample, the target numbers are in the D column, the range should be in this column. And the conditions are in the A and C column. Besides, in your actual worksheet, you should adjust the range and criteria to get the result.

  1. Press the key “Enter”. And then the number will in the cell.William's Average Apple Sales Volume

Compared with the AVERAGEIF function, the AVERAGEIFS function can set more conditions. And you need choose one of the functions that can help you in your work.

Measures to Excel File Corruption

Most of the time, your computer needs to connect the internet to finish some tasks. During the process, you will sometime get attacked by virus or unknown malwares. And the malicious software will cause damage to your files. In addition, Excel files are also not immune to it. So we recommend that you prepare the repair software at hand in case those accidents happen. This tool is very powerful in handling with damaged files and can recover Excel files to the maximum extant.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including word recovery and outlook repair software products. For more information visit www.datanumen.com

Comments are closed.