Reader Nikhil Shah asks in an email:
Dear Chandoo,
I have some Error In Excel sheet.
Problem :-
I have a locker number 01234 567890, in Excel it will be displayed as 01234 567890 with the space. However if it was entered as 01234567890, Excel will display it as 1234567890, without the leading zero. If the data has been read in from a text fill in could be formatted as text and still show 01234567890.
Now I want my locker number see with zero in excel sheet,How it possible,please help me with example.
You can force excel to format numbers the way you want using “format cell” feature. Just select the cell with number you want to format and hit ctrl+1 (or right mouse click > format cells) and go to Number tab in the dialog box. Select “custom” category and enter format as 000000 00000 (6 zeros followed by a space and then five more zeros) as shown below. Hit ok to set the format to display locker number as you desire.
Also, try these things:
- To set social security number (SSN) format, enter 000-00-0000
- To set phone number format, enter 000-000-0000
- To set phone number with 1 digit country code format, enter (+0) 000 000 0000, for 2 digit country codes you can try (+00) 000 000 0000.
- To set social security number (SSN) format, enter 000-00-0000
- To set ZIP code format, enter 00000, for 9 digit ZIP codes you can try 00000-0000
Do read Using Custom Cell Formats in Excel – Tips & Tricks to findout how to format dates, currencies, special formats etc.
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.