Have you ever wondered how you can hide that 0 (zero) at axis bottom? Like this…,
Here is a handy little trick to do just that:
- Select the axis and press CTRL+1 (or right click and select “Format axis”)
- Go to “Number” tab. Select “Custom”
- Specify the custom formatting code as
#,##0;-#,##0;; - Press “Add” if you are using Excel 2007, otherwise press just OK.
- That is all.
The trick uses custom number formatting codes in excel to show blanks whenever axis value is Zero, thus hiding the zeros at the bottom of axis. [even more juicy awesome kickass stuff on custom cell formatting]
Learn more quick excel tips and charting tricks.
PS: I am in Stockholm this week, to meet some customers. Internet access is limited plus I am too busy exploring this beautiful city. So we will have only quick tips this week.
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.