Formatting of decimal places driven by input cell.

Paul Coverley

New Member
Joined
Jun 23, 2022
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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
BCDEFGHIJKLMN
1increment 1
2range3000
3
4
5
6Linearity pointsApplied loadDisplayNo of tipping weightsTrue valueErrorZero errorTotal errorToleranceResult
7UP(L)(I) ( DL )( x 10)(E)(EO)(EC)e
820e2020420.00.00.00.00.5PASS
9500e5005007500.00.00.00.00.5PASS
10Max/215001500101500.00.00.00.00.5PASS
111000e10001000151000.00.00.00.00.5PASS
12Max30003000273000.00.00.00.00.5PASS
Sheet1
Cell Formulas
RangeFormula
F8:F12F8=(D8+(0.5*$G$25)-(E8*$G$25)*0.1)
G8:G12G8=F8-D8
H8:H12H8=$J$32
I8:I12I8=G8-H8
J8:J12J8=IF((A8<=500),0.5,IF(AND(A8>500,A8<=2000),1,IF((A8>2000),1.5)))
K8:K12K8=IF((ABS(I8))<=J8,"PASS","FAIL")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
K6:K7Cell Value="PASS"textNO
K6:K7Cell Value="FAIL"textNO
J13,K8:K12Cell Value="PASS"textNO
J13,K8:K12Cell Value="FAIL"textNO
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

JamesCanale

Well-known Member
Joined
Jan 13, 2021
Messages
789
Office Version
  1. 365
Platform
  1. Windows
MrExcelPlayground10.xlsx
FGHIJKLMN
10.1
2
3
4
5
6
7
820.000.000.000.000.50
9500.000.000.000.000.50
101500.000.000.000.000.50
111000.000.000.000.000.50
123000.000.000.000.000.50
Sheet5
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F8:J12Expression=-LOG10($N$1)=3textNO
F8:J12Expression=-LOG10($N$1)=2textNO
F8:J12Expression=-LOG10($N$1)=1textNO
F8:J12Expression=-LOG10($N$1)=0textNO
F8:J12Expression=-LOG10($N$1)=-1textNO

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

Forum statistics

Threads
1,181,426
Messages
5,929,822
Members
436,697
Latest member
sunnypl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top