How to Calculate Average Number of Items Sold and Average Revenue Generated by a Salesperson in a Transaction by Using MS Excel’s AVERAGEIF Function

How to Calculate Average Number of Items Sold is an important task to perform in many MS Excel projects.

Imagine you have the following sales data:

How to Calculate Average Number of Items Sold and Average Revenue Generated by a Salesperson in a Transaction by Using MS Excel’s AVERAGEIF Function
How can we find the average number of items sold by a salesperson in an average sales transaction?
The formula is:

AVERAGEIF = ([RANGE_NAME],”Name”,[AVERAGE_RANGE])

This formula will find the average of the second range for every “name” mentioned in the first range.
More specifically:

=AVERAGEIF(B2:B20,”Mary”,E2:E20) = 14.67

Result is: 14.67.
Mary has sold 14.67 items every time she completed a sales transaction.
Let’s see what the figure is for Weng:

=AVERAGEIF(B2:B20,”Weng”,E2:E20) = 19.5

“How can I calculate average sales revenue generated by a salesperson in a typical transaction?”

You can again use the same function, with different AVERAGE_RANGE value:

=AVERAGEIF(B2:B20,”Mary”,F2:F20) = $342.33

For Weng:

=AVERAGEIF(B2:B20,”Weng”,F2:F20) = $285

TAKEAWAY POINT: Mary is selling less number of items per transaction than Weng (14.67 vs. 19.5) but she is generating more revenue for her company ($342 vs. $285).