Excel Number Formatting - Auto Decimal Place by What You Type

D

dickgent

#11
I don't recommend that you convert to text. That can come back to haunt you when you have forgotten how you accomplished this. You can use conditional formatting to set the decimal places base on a cell evaluation formula. See the Cond Format Example.jpg for better explanation. Good Luck! :bigwave:
 

Attachments

Elsmar Forum Sponsor
P

Philium

#12
I don't recommend that you convert to text. That can come back to haunt you when you have forgotten how you accomplished this. You can use conditional formatting to set the decimal places base on a cell evaluation formula. See the Cond Format Example.jpg for better explanation. Good Luck! :bigwave:
It seems conditional number formating is only in newer version of Excel. I have 2000 and it only allows font, border & patern formating.
 
A

adamsjm

#13
If you use the custom format of ???.?????, the cell will be formatted 8 numbers and a decimal point. The decimal will align after the third character and leave "space" for five additional characters. This will work quite well for what you are doing with one exception: trailing zeros will not appear and you will lose the "fell" of the number's precision.

If that need is necessary, you can format some blank cells at the top of your entry column with the necessary precision that you are seeking with the custom format of #.###?? for three decimals and #.##### for five. This will give you decimal point alignment and precision. Once your data is entered you can use the format painter to copy the formats to the appropriate cells.

If you use the courier new font the spaces for the numbers will also be displayed in fixed width spacing.

Also, you may wish to study the "round" function. In performing calculations, you should always round the result to ensure the correct precision of the calculation. Remember the significant number theory that you were taught in school and the philosophy behind MSA. Just because Excel can calculate 13 (or more digits) does not mean that you should use that capability.
 
A

adamsjm

#14
This would be better:
for one decimal place ??0.0????
for two ??0.00???
for three ??0/000??
for four ??0.0000?
and for five decimal places ??0.00000

If you want some space between the number and the right edge of the cell you can add additional "?" to the end of the format to hold a space(s).
 
D

Darius

#16
IMHO:

# of Decimals in Excel

BEING
UCL = Upper Control Limit
LCL = Lower Control Limit
MESH =the minimum number of classes you want (data granulometry)

=-ROUNDUP(IF(UCL=LCL,0,LOG((UCL-LCL)/MESH,10)),0)*(UCL-LCL<(MESH-1))

NOTE: you can use Q1 and Q3 quartile insteed of LCL and UCL, recommended MESH being at least 10 classes.
 
D

Darius

#17
Update, I changed so it will work OK for a single data point

being

DATA= value cell or range

=ROUNDUP(IF(QUARTILE(DATA,3)=QUARTILE(DATA,1),INT((LOG10(100/MEDIAN(DATA))+1))*(MEDIAN(DATA)<100),-LOG((QUARTILE(DATA,3)-QUARTILE(DATA,1))/10,10)),0)

Simple, night wahr?:notangel:, the single data point part, INT((LOG10(100/MEDIAN(DATA))+1))*(MEDIAN(DATA)<100), take for granted that the order of magnitude of the data point set it all, but it's a faulty way to see, because you can have data points that changes between 1000 and 1000.05, the order of magnitude of 1000 may tell you not to use decimals but because you are using a range, the order must comply to show the variation.
 
Thread starter Similar threads Forum Replies Date
M Can the same form number be in Word and Excel for ISIR's and such? Document Control Systems, Procedures, Forms and Templates 9
Q Excel worksheet - Help required (viewing specific number of rows/columns) Excel .xls Spreadsheet Templates and Tools 4
J Incoming Inspection Records using Excel File ISO 13485:2016 - Medical Device Quality Management Systems 2
S 95% UCL - Attaching an excel for recovery study Statistical Analysis Tools, Techniques and SPC 0
T EXCEL - How do I make the cell stay blank until a date is entered in N7? Excel .xls Spreadsheet Templates and Tools 6
Ed Panek Validating Excel (XLS) formulas 21 CFR Part 820 - US FDA Quality System Regulations (QSR) 7
C Template for Excel Validation Reliability Analysis - Predictions, Testing and Standards 6
R Gage R&R Excel templates Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 3
S Issuing of CoC to Customer in a Word or Excel format ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 7
Ajit Basrur Track audit findings on Excel tracker Excel .xls Spreadsheet Templates and Tools 9
J Does anyone have an excel IATF 16949 Internal Audit checklist I could use? IATF 16949 - Automotive Quality Systems Standard 7
S Anyone Familiar with "SPC for Excel" add-in? Lean in Manufacturing and Service Industries 4
P Commercial excel templates that calculate overhead, hourly rates, fee proposal Excel .xls Spreadsheet Templates and Tools 0
I Cannot Export multiple sections from Excel to PDF without losing pagination Excel .xls Spreadsheet Templates and Tools 23
M Matching two matrices in Excel Excel .xls Spreadsheet Templates and Tools 5
J Level 3 KPI Excel Template Manufacturing and Related Processes 1
bobdoering Informational New AIAG PFMEA Process - Excel Template Attached FMEA and Control Plans 23
GreatNate Excel PPAP Tracking templates or tool wanted APQP and PPAP 1
E Part 11 Compliance, Excel living documents (i.e. document master list, equipment list, approved supplier list) Pharmaceuticals (21 CFR Part 210, 21 CFR Part 211 and related Regulations) 3
J ISO 9001:2015 Excel Gap Analysis wanted ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 7
B AS9110 Audit Check Sheet wanted Excel or Word Document AS9100, IAQG, NADCAP and Aerospace related Standards and Requirements 2
G Excel report with pictures Excel .xls Spreadsheet Templates and Tools 5
A Query regarding MS Excel usage Excel .xls Spreadsheet Templates and Tools 2
A Excel OOT (Out of Tolerance) Formula Excel .xls Spreadsheet Templates and Tools 3
D Asterix when pasting a column of values from Excel into Minitab (V17) Using Minitab Software 5
Y Excel Automation - Printing Word Documents from within Excel Excel .xls Spreadsheet Templates and Tools 0
S Help me build an Excel Spreadsheet for tracking training Training - Internal, External, Online and Distance Learning 14
P Template or example Excel list of ISO certificates issued to clients ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 5
M Medical Device Process validation, Validation of excel spreadsheets used for process Other Medical Device and Orthopedic Related Topics 4
C Excel date format from Denmark to USA Excel .xls Spreadsheet Templates and Tools 6
G ISO 9001:2015 - Is control possible with Excel adaptable measurement sheets? ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 24
N Where I can find an Excel sample/template for recording Delivery Time and Cost Excel .xls Spreadsheet Templates and Tools 2
D How do I go about creating document logs and registers with the MS Excel Excel .xls Spreadsheet Templates and Tools 2
D Microsoft Excel database to Stand-alone software Calibration and Metrology Software and Hardware 3
Q Controlling Multi-Tab Microsoft Excel Forms Excel .xls Spreadsheet Templates and Tools 5
B Zeiss CMM Calypso output to Excel or CSV file Help Inspection, Prints (Drawings), Testing, Sampling and Related Topics 17
S used excel formula calculated the ARL's with Rule1&Rule2 for Shewhart control chart Statistical Analysis Tools, Techniques and SPC 0
N Gantt Chart / Time Tracker in Excel - Combining Tasks on the Same Line Excel .xls Spreadsheet Templates and Tools 2
M Preventive Maintenance Spreadsheet in Excel Quality Assurance and Compliance Software Tools and Solutions 2
1 KPI Dashboard Template in Excel Format wanted Excel .xls Spreadsheet Templates and Tools 9
Ajit Basrur MS Excel to study Correlation between Humidity & Differential Pressure Excel .xls Spreadsheet Templates and Tools 7
D Gage Calibration Tracking in MS Excel Calibration and Metrology Software and Hardware 9
T Where can I get a PPAP workbook in Excel .xls with links? APQP and PPAP 1
L Excel Template for Cgk Study wanted (Capability Gauges Study) Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 12
V Looking for advice on Pooled Standard Deviation between Minitab and MS Excel Statistical Analysis Tools, Techniques and SPC 1
P Recommendations on SPC Software or Excel SPC add-in(s) Quality Assurance and Compliance Software Tools and Solutions 6
T Calculating LCL, UCL, Cp, and Cpk in an Excel Spreadsheet Statistical Analysis Tools, Techniques and SPC 3
R Is there any Excel .xls Spreadsheet available for DOE with 3 factors? Six Sigma 1
kedarg6500 Excel spreadsheet for Unilateral Tolerance Study wanted Excel .xls Spreadsheet Templates and Tools 2
kedarg6500 Excel Spreadsheet for AIAG Example wanted Excel .xls Spreadsheet Templates and Tools 3

Similar threads

Top Bottom