Who is the most consistent seller? [BYOD]
Last week, I asked my email newsletter readers to submit “one data analysis problem you are struggling with”. We called it BYOD – Bring your own data. More than 100 people have emailed various interesting (and often very difficult) problems. This week (between 16th of February to 20th of February), let’s take a look at some of these problems and solve them.
This problem is sent by Robot Mak.
Who is the most consistent of all?
Imagine you are a category manager at a large ecommerce company. Your site offers various products, but you don’t really make these products. You list products made by other vendors on your site. Every day, these vendors would send you invoices for the amount of product they have sold. Here is a snapshot of such invoices.
Looking at this list, you have a few questions.
 Who is the best seller?
 Who is the most active seller?
 Who is the most consistent seller?
 Which seller has fewest invoices?
Let’s go ahead and answer these using Excel. Shall we?
But first, what is consistency?
Consistency is the kind of word that means different things to different people. So when we analyze a set of data to find most consistent item, the first thing we need is a consistent definition of consistency.
Let’s look it up in the dictionary.
con·sis·ten·cy: Reliability or uniformity of successive results or events
The keyword is uniformity. So in our case, we can say a seller is consistent, if they are sending invoices fairly regularly.
We have 11 days of data in the dataset.
We can calculate consistency % for a seller using this:
=number of days on which invoice is sent / 11
So who would be most consistent?
Let’s say 2 sellers have sent invoices on each of those 11 days. Then who is most consistent? Both of them have consistency = 100%.
May be we can calculate weighted consistency %?
Weighted consistency?!?
Since for this ecommerce business, the most important factor is revenues (well, it should be profits, but we don’t have that data here), we can calculate weighted consistency by adding a fraction that depends on the value of total invoice amount.
Something like this:
=consistency % + (0.000001)*rank of seller based on invoice amount in ascending order
note: if your data has more than 1000 sellers, multiply with a smaller number like 0.00000001
Alternative ways to figure out weights:
Instead of revenue rank, we can use below alternatives too:
 Standard deviation of invoice amounts per day
 Standard deviation of number of invoices per day
 Rank of total invoice count in ascending order
Option 1. Using Pivot Tables
The easiest way to answer all the questions is to use Pivot Tables. Just follow below steps:
 Insert a pivot table from the invoice data.
 Then add “Date” to row label area
 Add “Seller” to column label area
 Add “value” to values area
 Just looking at the below report, we can answer questions 1 & 4
 To answer question 3 (most consistent seller), we have to see which sellers have invoices against maximum number of dates. Both SELLER2 & SELLER6 qualify. Since SELLER6 has higher amount, we can say she is most consistent (based on our definition of most consistent above)
 To answer question 2 (most active seller), replace “value” with “reference” in pivot table and find out the seller with maximum count
Option 2. Using formulas
While the pivot table approach works, it is adhoc. That means, we can’t extract the names of sellers automatically. We can use Excel formulas to answer all these questions elegantly.
Let’s assume all the data is in a table named sales
In your workbook, calculate all of these:
Now we just need a few doses of INDEX+MATCH formulas to answer the questions.
[Related: How to count unique values in a range?  Using SUMPRODUCT formula]
Who is the best seller?
=INDEX($H$12:$H$20,MATCH(MAX($I$12:$I$20),$I$12:$I$20,0))
Note: Column H has the seller names & I has the seller amounts
Who is the most active seller?
=INDEX($H$12:$H$20,MATCH(MAX(J12:J20),J12:J20,0))
Note: Column J has invoice count
Who is the most consistent seller?
=INDEX($H$12:$H$20,MATCH(MAX(M12:M20),M12:M20,0))
Note: Column M has weighted consistency %
Who is the seller with fewest invoices?
=INDEX($H$12:$H$20,MATCH(MIN(J12:J20),J12:J20,0))
Download Example Workbook
Click here to download example workbook with all these calculations. Examine the formulas & pivot table to learn more.
How do you measure consistency?
I will be honest. This is the first time I calculated consistency. But I find it interesting. Consistency can be used to understand your data better & make informed decisions. Few common situations where it can really help,
 Identifying consistent customers to reward them
 Finding consistent assembly line in a set of them
 Optimizing reordering pattern of inventories based on how consistently orders are placed
What about you? Do you measure consistency of your data? What techniques do you use? Please share your techniques & tips in the comments section.
Hello Awesome...
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.
Thank you and see you around.
Related articles:

Leave a Reply
« Revenue vs. Commission growth – Getting the message across [BYOD]  CP030: Detecting fraud in data using Excel – 5 techniques for you » 
11 Responses to “Who is the most consistent seller? [BYOD]”
The Date column in the sample file is Text not Dates
[…] http://chandoo.org/wp/2015/02/18/calculatingconsistencyinexcel/?utm_source=feedburner&utm_med… […]
Great Chandoo. Keep it up, Looking forward more from BYOD..
Thanks
With Excel 2013 the pivot table could be connected to the data model which provides a distinct count.
This will do for invoice count
=COUNTIF(F:F,H12)
Instead of
=COUNTIFS(sales[SELLER],$H12)
Excellent document. How did you make the last graphic? Witch app. Thanks for answer.
Can someone tell me what =countif(sales[date],sales[date]) is counting? The value is 19. Its found in the =SUMPRODUCT(IF(sales[SELLER]=H12,1/COUNTIFS(sales[SELLER],H12,sales[date],sales[date]),0))
Hi Chris,
=countif(sales [date],sales[date]) function is counting the unique dates in the table.
Vândalo
Excellent document!
Can you explain more about the calculation on Weighted consistency? More specific the small number is 0,00001 ?
How come the number should be smaller if there is more sellers?
Hi,
Not understood this formula: {=SUMPRODUCT(IF(sales[SELLER]=H12,1/COUNTIFS(sales[SELLER],H12,sales[date],sales[date]),0))}
Please explain.
Thanks.