Keep significant trailing zeros

[Asbestos_Jen]

I have data as simplified below. The final result needs to be 2 significant digits, but can't be in scientific notation because the majority of our clients are not scientists. The formula in column E works well when the last significant digit is not zero, but always truncates trailing zeros.

Is there way to keep the trailing zero using formulas? The raw values are calculated from many parameters, I just used values here.

Book2
	B	C	D	E
1	Raw		FINAL
2	Raw Limit	Raw Result	Limit	RESULT
3	0.001	0.00005	0.001	<0.001
4	0.03556	3.521149763	0.036	3.5
5	0.00100003	0.001207	0.001	0.0012
6	0.001	0.000372	0.001	<0.001
7	0.0358	0.46428	0.036	0.46
8	0.346530314	0.043	0.35	<0.35
9	0.001201	0.005360064	0.0012	0.0054
10	0.0011	0.005990788	0.0011	0.006
11	0.001003	0.006535544	0.001	0.0065
12	0.00096	0.007008873	0.00096	0.007
13	0.00091	0.007422251	0.00091	0.0074
14
15	Correct # digits
Sheet1

Cell Formulas
Range	Formula
D3:D13	D3	=ROUND($B3,2-(1+INT(LOG10($B3))))
E3:E13	E3	=IF($C3>$B3,ROUND($C3,2-(1+INT(LOG10($C3)))),"<"&D3)

 

[6StringJazzer]

DISREGARD THIS SOLUTION: It works fine when only one digit is showing but it undesirably adds a zero even if there are already two significant digits showing. Back to the drawing board.

You can't do it with formulas, this is a display format issue. I was able to this with conditional formatting, but you have to have a rule for every expected level of precision. And the order matters.

$scratch.xlsm
	A
2	0.60
3	0.060
4	0.0060
5	0.00060
Sig Dig

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:A	Expression	=INT(A1*10)=A1*10	text	NO
A:A	Expression	=INT(A1*100)=A1*100	text	NO
A:A	Expression	=INT(A1*1000)=A1*1000	text	NO
A:A	Expression	=INT(A1*10000)=A1*10000	text	NO

 

[Asbestos_Jen]

DISREGARD THIS SOLUTION: It works fine when only one digit is showing but it undesirably adds a zero even if there are already two significant digits showing. Back to the drawing board.

You can't do it with formulas, this is a display format issue. I was able to this with conditional formatting, but you have to have a rule for every expected level of precision. And the order matters.

$scratch.xlsm
	A
2	0.60
3	0.060
4	0.0060
5	0.00060
Sig Dig

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:A	Expression	=INT(A1*10)=A1*10	text	NO
A:A	Expression	=INT(A1*100)=A1*100	text	NO
A:A	Expression	=INT(A1*1000)=A1*1000	text	NO
A:A	Expression	=INT(A1*10000)=A1*10000	text	NO

View attachment 119769

Thanks for the input.

I would love MS to add a function for this, but it wouldn't help me anyway, since my company still runs Office 2007 or 2010 on all machines.

I remember seeing a VBA solution somewhere once upon a time; I'll see if I can find it again.

 

[6StringJazzer]

Premature, not solved

 

[6StringJazzer]

OK I think I got it.

$scratch.xlsm
	B	C	D	E
1	Raw		FINAL
2	Raw Limit	Raw Result	Limit	RESULT
3	0.001	0.00005	0.001	<0.001
4	0.03556	3.52115	0.036	3.5
5	0.001	0.001207	0.001	0.0012
6	0.001	0.000372	0.001	<0.001
7	0.0358	0.46428	0.036	0.46
8	0.34653	0.043	0.35	<0.35
9	0.001201	0.00536	0.0012	0.0054
10	0.0011	0.005991	0.0011	0.0060
11	0.001003	0.006536	0.001	0.0065
12	0.00096	0.007009	0.00096	0.0070
13	0.00091	0.007422	0.00091	0.0074
14
15	Correct # digits
Sheet3

Cell Formulas
Range	Formula
D3:D13	D3	=ROUND($B3,2-(1+INT(LOG10($B3))))
E3:E13	E3	=IF($C3>$B3,ROUND($C3,2-(1+INT(LOG10($C3)))),"<"&D3)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E:E	Expression	=AND(E1*10<10,INT(E1*10)=E1*10)	text	NO
E:E	Expression	=AND(E1*100<10,INT(E1*100)=E1*100)	text	NO
E:E	Expression	=AND(E1*1000<10,INT(E1*1000)=E1*1000)	text	NO
E:E	Expression	=AND(E1*10000<10,INT(E1*10000)=E1*10000)	text	NO

 

[Asbestos_Jen]

What are the actual formats you're using in the conditions? XL2BB doesn't capture conditional formatting for me because my Excel is too old (XL2BBv2.0 because XL2BBv2.1 crashes excel on this computer.)

2024-11-27_Trailing0sd.xlsx
	B	C	D	E
1	Raw		FINAL
2	Raw Limit	Raw Result	Limit	RESULT
3	0.00100	0.00005	0.001	<0.001
4	0.03556	3.52115	0.036	3.5
5	0.00100	0.00121	0.001	0.0012
6	0.00100	0.00037	0.001	<0.001
7	0.03580	0.46428	0.036	0.46
8	0.34653	0.04300	0.35	<0.35
9	0.00120	0.00536	0.0012	0.0054
10	0.00110	0.00599	0.0011	0.006
11	0.00100	0.00654	0.001	0.0065
12	0.00096	0.00701	0.00096	0.007
13	0.00091	0.00742	0.00091	0.0074
Sheet2

Cell Formulas
Range	Formula
D3:D13	D3	=ROUND($B3,2-(1+INT(LOG10($B3))))
E3:E13	E3	=IF($C3>$B3,ROUND($C3,2-(1+INT(LOG10($C3)))),"<"&$D3)

 

[6StringJazzer]

Here are the custom formats for each rule

=AND(E1*10<10,INT(E1*10)=E1*10)	0.00
=AND(E1*100<100,INT(E1*100)=E1*100)	0.000
=AND(E1*1000<1000,INT(E1*1000)=E1*1000)	0.0000
=AND(E1*10000<10000,INT(E1*10000)=E1*10000)	0.00000

 

[Asbestos_Jen]

That seems to do the job as long as the final result is a number. I'll have to see if I can modify it for the "less than" (text) results.

 

[6StringJazzer]

You can't modify text using that method. You may have to bake it into the formula that produces the text.

 

[Asbestos_Jen]

I found this UDF at Rounding to Significant Figures in Excel searching for a VBA solution. I think I'll go this way instead of conditional formatting or really ugly nested IFs, which was what I was going to try adapting the cf rules to do.

2024-11-27_Trailing0sd.xlsm
	K	L	M	N
1	Raw		FINAL
2	Raw Limit	Raw Result	Limit	RESULT
3	0.001	0.00005	0.0010	<0.0010
4	0.03556	3.521149763	0.036	3.5
5	0.00100003	0.001207	0.0010	0.0012
6	0.001	0.000372	0.0010	<0.0010
7	0.0358	0.46428	0.036	0.46
8	0.346530314	0.043	0.35	<0.35
9	0.001201	0.005360064	0.0012	0.0054
10	0.0011000	0.005990788	0.0011	0.0060
11	0.001003	0.006535544	0.0010	0.0065
12	0.0009600	0.007008873	0.00096	0.0070
13	0.0009000	0.007422251	0.00090	0.0074
14	0.0000900	0.004222511	0.000090	0.0042
Vertex

Cell Formulas
Range	Formula
M3:M14	M3	=roundsf(K3,2)
N3:N14	N3	=IF(L3>K3,roundsf(L3,2),"<"&roundsf(K3,2))

Function ROUNDSF(num As Variant, sigs As Variant) As String
Dim exponent As Integer
Dim decplace As Integer
Dim fmt_left As String
Dim fmt_right As String
Dim numround As Double
If IsNumeric(num) And IsNumeric(sigs) Then
If sigs < 1 Then
' Return the   "  #NUM  "   error 
            ROUNDSF = CVErr(xlErrNum) 
 Else
            numround = WorksheetFunction.text(num, "." & _ 
 String(sigs, "0") & "E+000") 
If num = 0 Then
                exponent = 0 
 Else
'Round is needed to fix a ?truncation? 
'problem when num = 10, 100, 1000, etc. 
                exponent = Round(Int(Log(Abs(numround)) / Log(10)), 1) 
End If
            decplace = (sigs - (1 + exponent)) 
If decplace > 0 Then
fmt_right = String(decplace, "0") 
                fmt_left = "0." 
 Else
                fmt_right = "" 
                fmt_left = "0" 
End If
            ROUNDSF = WorksheetFunction.text(numround, _ 
                      fmt_left & fmt_right) 
End If
 Else
' Return the   "  #N/A  "   error 
        ROUNDSF = CVErr(xlErrNA) 
End If
End Function