Marko, who is a long time PHD reader and an excel ninja sent this via email,
I work at an insurance company in Slovenia. At the beginning of each year we have a conversation with our superiors to review our work in the past year and to set new goals (main activities) that we’re gonna work on throughout the year. To keep track of these activities I took the liberty of modifying Jennie’s A New Year Resolutions Template that Kicks Ass and created a more “dynamic” template.
He sent me this:
I immediately liked this implementation, for various reasons. This example shows how we can combine various powerful features in MS Excel to create something truly unique and outstanding. It uses,
- Conditional formatting to highlight rows when the activity is 100% complete. [learn how to]
- Formulas to show check boxes when the activity is done. [learn how to]
- A thermometer chart to show the progress against the target. [learn how to]
- An elegant date formula to show how many days are remaining to finish the goals. [learn how to]
- And scrollbar form controls to adjust the % completion values. [learn how to]
Download this tracker Excel File
Click here to download the example file and play with it.
Thank you Marko
Very elegant, very slick. Good work Marko. Thanks for sharing this with us.
Share your Excel Success with PHD
I like to learn how you use excel creatively to solve problems at work. Send me examples, workbooks or ideas. I am always looking for new ways to use excel and your contributions will help us all. Email your stuff to me 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.