Latest posts by techwriter (see all)
- How to Count the Number of Days with an Incident and Chart with Running Averages in MS Excel - October 19, 2016
- FREE Online Video Course – MS Word Power Shortcuts - October 14, 2016
- INFOGRAPHICS – Which Business Entity is Right for You? - September 28, 2016
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).