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