fbpx
Search
Close this search box.

Who is the most consistent seller? [BYOD]

Share

Facebook
Twitter
LinkedIn

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 e-commerce 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.

who-is-the-most-consistent-seller-data

Looking at this list, you have a few questions.

  1. Who is the best seller?
  2. Who is the most active seller?
  3. Who is the most consistent seller?
  4. 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 data-set.

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 e-commerce 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:

  1. Insert a pivot table from the invoice data.
  2. Then add “Date” to row label area
  3. Add “Seller” to column label area
  4. Add “value” to values area
  5. Just looking at the below report, we can answer questions 1 & 4

calculating-most-consistent-seller-using-pivot-table

  1. 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)
  2. 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 ad-hoc. 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:

calculations-for-most-consistent-seller-explained

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 re-ordering 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.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

11 Responses to “Who is the most consistent seller? [BYOD]”

  1. Hui... says:

    The Date column in the sample file is Text not Dates

  2. Great Chandoo. Keep it up, Looking forward more from BYOD..

  3. gayani says:

    Thanks

  4. Frank Tonsen says:

    With Excel 2013 the pivot table could be connected to the data model which provides a distinct count.

  5. Mak says:

    This will do for invoice count
    =COUNTIF(F:F,H12)
    Instead of
    =COUNTIFS(sales[SELLER],$H12)

  6. Alejandro says:

    Excellent document. How did you make the last graphic? Witch app. Thanks for answer.

  7. Chris says:

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

    • Vândalo says:

      Hi Chris,

      =countif(sales [date],sales[date]) function is counting the unique dates in the table.

      Vândalo

  8. Nguyen says:

    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?

  9. TS says:

    Hi,

    Not understood this formula: {=SUMPRODUCT(IF(sales[SELLER]=H12,1/COUNTIFS(sales[SELLER],H12,sales[date],sales[date]),0))}

    Please explain.

    Thanks.

Leave a Reply