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

Imagine you have the following sales data:

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).