Tomorrow is thanksgiving day here. It is a harvest festival on which people express thanks for what they have. Much like Sankranthi (pongal) in India.
At this moment I am very angry and frustrated about the terror strikes in Mumbai. I am not sure how a person can do something like this to others. I do not believe in any god in particular, but now I am praying to god asking for little sanity and humanity in us.
I would also like to take a moment and tell you how much I am thankful for having discovered a passion through this blog. I have learned so much in last few months just by sharing what I know and interacting with people.
So to express the gratitude, I am sharing a simple excel tip. Please feel free to share your tip to tell how thankful you are for everything you have.
- Ctrl + 1 – opens the dialog box for formatting a cell. I use this all the time
Now your turn!
PS: expect delay in posts due to holidays
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/calculating-consistency-in-excel/?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.