Formatting of decimal places driven by input cell.

[Paul Coverley]

Hi all - I'm new to the forum.

I have attached a worksheet to show an example of scenario I am trying to resolve.

the attached is the start of a record sheet for instrument tests.

What I am looking to achieve is to format the number of decimal places in readings in columns F to J based upon the number of decimal places expressed in Increment cell n1.

n1's value could be 10,1,0.1,0.01 etc

I would cols F to J to be formatted to 1 decimal place more than the number of dp's expressed in n1

i.e if n1 = 1 (0dps) cols f to j to display to 1dps
if n1 = 0.1 (1dps) cols f to j to display to 2dps

I hope I have explained this ok.

Any help gratefully received

Many thanks

Paul

example.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N
1												increment	1
2												range	3000
3
4
5
6	Linearity points	Applied load	Display	No of tipping weights	True value	Error	Zero error	Total error	Tolerance	Result
7	UP	(L)	(I)	( DL )	( x 10)	(E)	(EO)	(EC)	e
8	20e	20	20	4	20.0	0.0	0.0	0.0	0.5	PASS
9	500e	500	500	7	500.0	0.0	0.0	0.0	0.5	PASS
10	Max/2	1500	1500	10	1500.0	0.0	0.0	0.0	0.5	PASS
11	1000e	1000	1000	15	1000.0	0.0	0.0	0.0	0.5	PASS
12	Max	3000	3000	27	3000.0	0.0	0.0	0.0	0.5	PASS
Sheet1

Cell Formulas
Range	Formula
F8:F12	F8	=(D8+(0.5*$G$25)-(E8*$G$25)*0.1)
G8:G12	G8	=F8-D8
H8:H12	H8	=$J$32
I8:I12	I8	=G8-H8
J8:J12	J8	=IF((A8<=500),0.5,IF(AND(A8>500,A8<=2000),1,IF((A8>2000),1.5)))
K8:K12	K8	=IF((ABS(I8))<=J8,"PASS","FAIL")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K6:K7	Cell Value	="PASS"	text	NO
K6:K7	Cell Value	="FAIL"	text	NO
J13,K8:K12	Cell Value	="PASS"	text	NO
J13,K8:K12	Cell Value	="FAIL"	text	NO

 

[JamesCanale]

MrExcelPlayground10.xlsx
	F	G	H	I	J	K	L	M	N
1									0.1
2
3
4
5
6
7
8	20.00	0.00	0.00	0.00	0.50
9	500.00	0.00	0.00	0.00	0.50
10	1500.00	0.00	0.00	0.00	0.50
11	1000.00	0.00	0.00	0.00	0.50
12	3000.00	0.00	0.00	0.00	0.50
Sheet5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F8:J12	Expression	=-LOG10($N$1)=3	text	NO
F8:J12	Expression	=-LOG10($N$1)=2	text	NO
F8:J12	Expression	=-LOG10($N$1)=1	text	NO
F8:J12	Expression	=-LOG10($N$1)=0	text	NO
F8:J12	Expression	=-LOG10($N$1)=-1	text	NO

The actual formatting doesn't show here, but you'd pick "NUMBER" and select the number of significant digits.