Hello Folks.
I have a rather bad news for you. My internet service provider (TATA Photon) has mysteriously blocked chandoo.org. My site hasn’t been loading since Sunday. While I know it is up and running, I cannot see it unless I go thru a proxy server like kproxy.
It may take a couple of days for this to be resolved. I am using this time to take a break from blogging and read some books that have been waiting. I am almost done with Edward Tufte’s The Visual Display of Quantitative Information
and Walkenbach’s Excel 2010 Bible
. I will be reading Stephen Few’s Information Dashboard Design
and then Walkenbach’s Excel 2010 Power Programming
.
In the meanwhile I have a fun VLOOKUP contest for you.
All you need to do is, share a VLOOKUP trick or tip thru comments.
And the winner gets an iPod nano
. 8gb latest version.
Some rules to keep in mind:
- You have to post the entire tip in the comments. No links please.
- You can post as many tips as you want.
- You can post tips on VLOOKUP, INDEX – MATCH, OFFSET – MATCH, HLOOKUP, LOOKUP or lookups done using SUMPRODUCT
- Bonus karma & points for people making an example excel file & linking it along with the tip.
- Contest closes on October 12, End of day (Pacific time, why? because I love the big, blue ocean)
- Winner will be selected randomly.
Go!
Read these excellent articles explaining – VLOOKUP, OFFSET & MATCH, SUMPRODUCT.
PS: If you would like to sponsor this contest, just drop me an email at chandoo.d @ gmail.com
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.