Show Decimals until first non-zero decimal is shown

[quauq]

Hi all,

i have a sheet with various numbers of market shares in column A (e.g. 2.2%, 5%). Some of those shares can also be <1 (e.g. 0.0092%, 0.045, 0.033% etc.).

I need to copy those values into a Word-document. However, the values displayed in the Word-doc should only ever show decimals until the first non-zero decimal is displayed (with a minimum of 1 decimal being shown). Normally, I use "Increase Decimal" and "Decrease Decimal" under the Number options. That way, in the examples I listed, 0.0092% would show as 0.01%, 0.045 would show as 0.05 (as with the way I described, Excel rounds dynamically) and 0.033% would show as 0.03%.

Is there a formula (or some other way) to automate this task in column B, so I need only copy those results into my Word-doc?

The formula I could come up with, does not do the trick (the rounded values are incorrect), but the idea should be in there somewhere:

=IFERROR(IF(A4*100>1;ROUND(A4*100;1);IF(RIGHT(100*ROUND(A4;INT(1-LOG(A4)));1)>=5;100*ROUND(A4;INT(1-LOG(A4))-1);100*ROUND(A4;INT(1-LOG(A4;10))))&"%");"n.a.")

Thank you!

 

[Anthony47]

Don't miss the suggestion from @myall_blues , above

I eventually went for a User Function.
Its code:

Function ForQnQ(ByVal iVal As Double) As String
'
Dim strVal As String, I As Long
Dim APS As String
'
strVal = CStr(CDbl(1.2))
APS = Mid(strVal, 2, 1)
strVal = Format(iVal * 100, "0.000000000000000")
I0 = InStr(1, strVal & "0", APS, vbTextCompare)
For I = 1 To Len(strVal) - I0
    If Mid(strVal, I + I0, 1) <> "0" Then Exit For
Next I
If I > 15 Then I = 1
If Round(iVal * 100, I) > 0 Then
    strVal = CStr(Round(iVal * 100, I))
    strVal = Format(Round(iVal * 100, I), "0." & String(I, "0"))
Else
    strVal = "0.0"
End If
ForQnQ = strVal & "%"
Debug.Print iVal * 100, I, strVal
End Function

Copy the code into a standard module of your vba Project
Then, with respect to the attached XL2BB minisheet, I set in F2 the formula

=ForQnQ(A2)

and copied it down

In Column D are your expected results for the data in column A
In Col E, the results from my previous formula, laid down on the basis of your first sample of data
In Col F the results with the new ForQnQ function
In col G I used the formula provided by myall_blues (message #8, above)

I didn't find a rule that explains why inputs with different decimals should be output with the same decimals (yellow data), or viceversa data having the same decimals should be output with different decimals (green data)

The minisheet:

MULTI_C41009.xlsm
	A	B	C	D	E	F	G
1	% Values		Decimal	Your wish	Formula	ForQnF	myAB
2	0,00086650%		0,000008665	0,001%	0,001%	0,0009%	0,0008%
3	0,00106050%		0,000010605	0,001%	0,001%	0,001%	0%
4	0,00482110%		0,000048211	0,005%	0,005%	0,005%	0,004%
5	0,00569320%		0,000056932	0,01%	0,01%	0,006%	0,005%
6	0,00076270%		0,000007627	0,001%	0,001%	0,0008%	0,0007%
7	0,00215310%		0,000021531	0,002%	0,002%	0,002%	0,002%
8	0,00000050%		0,000000005		0,000001%	0,0000005%	0,0000005%
9	31,20000000%		0,312000000	31,2%	30%	31,2%	31,2%
10	12,00000000%		0,120000000		10%	12,0%	1%
11	23,14000000%		0,231400000		20%	23,1%	23,1%
12	4,00000000%		0,040000000		4%	4,0%	4%
13	4,56000000%		0,045600000	4,6%	5%	4,6%	4,6%
14			0,000000000	4,6%	#N/D	0.0%	0%
15			0,000000000	4,6%	#N/D	0.0%	0%
16			0,000000000	4,6%	#N/D	0.0%	0%
17
Foglio3

Cell Formulas
Range	Formula
E2:E16	E2	=IFERROR(LET(iVal,A2,iLog,ROUNDUP(LOG10(iVal),0),maybe,IF(ROUND(iVal*100,-iLog-3)=0,ROUND(iVal*100,-iLog-2),ROUND(iVal*100,-iLog-3)),maybe&"%"),NA())
F2:F16	F2	=ForQnQ(A2)
G2:G16	G2	=IFERROR(LEFT(A2*100,FIND(MID(SUBSTITUTE(A2,0,""),2,99),A2*100)), LEFT(ROUND(A2*100,1),99))&"%"
C2:C16	C2	=A2

 

[quauq]

Don't miss the suggestion from @myall_blues , above

I eventually went for a User Function.
Its code:

Function ForQnQ(ByVal iVal As Double) As String
'
Dim strVal As String, I As Long
Dim APS As String
'
strVal = CStr(CDbl(1.2))
APS = Mid(strVal, 2, 1)
strVal = Format(iVal * 100, "0.000000000000000")
I0 = InStr(1, strVal & "0", APS, vbTextCompare)
For I = 1 To Len(strVal) - I0
    If Mid(strVal, I + I0, 1) <> "0" Then Exit For
Next I
If I > 15 Then I = 1
If Round(iVal * 100, I) > 0 Then
    strVal = CStr(Round(iVal * 100, I))
    strVal = Format(Round(iVal * 100, I), "0." & String(I, "0"))
Else
    strVal = "0.0"
End If
ForQnQ = strVal & "%"
Debug.Print iVal * 100, I, strVal
End Function

Copy the code into a standard module of your vba Project
Then, with respect to the attached XL2BB minisheet, I set in F2 the formula

=ForQnQ(A2)

and copied it down

In Column D are your expected results for the data in column A
In Col E, the results from my previous formula, laid down on the basis of your first sample of data
In Col F the results with the new ForQnQ function
In col G I used the formula provided by myall_blues (message #8, above)

I didn't find a rule that explains why inputs with different decimals should be output with the same decimals (yellow data), or viceversa data having the same decimals should be output with different decimals (green data)

The minisheet:

MULTI_C41009.xlsm
	A	B	C	D	E	F	G
1	% Values		Decimal	Your wish	Formula	ForQnF	myAB
2	0,00086650%		0,000008665	0,001%	0,001%	0,0009%	0,0008%
3	0,00106050%		0,000010605	0,001%	0,001%	0,001%	0%
4	0,00482110%		0,000048211	0,005%	0,005%	0,005%	0,004%
5	0,00569320%		0,000056932	0,01%	0,01%	0,006%	0,005%
6	0,00076270%		0,000007627	0,001%	0,001%	0,0008%	0,0007%
7	0,00215310%		0,000021531	0,002%	0,002%	0,002%	0,002%
8	0,00000050%		0,000000005		0,000001%	0,0000005%	0,0000005%
9	31,20000000%		0,312000000	31,2%	30%	31,2%	31,2%
10	12,00000000%		0,120000000		10%	12,0%	1%
11	23,14000000%		0,231400000		20%	23,1%	23,1%
12	4,00000000%		0,040000000		4%	4,0%	4%
13	4,56000000%		0,045600000	4,6%	5%	4,6%	4,6%
14			0,000000000	4,6%	#N/D	0.0%	0%
15			0,000000000	4,6%	#N/D	0.0%	0%
16			0,000000000	4,6%	#N/D	0.0%	0%
17
Foglio3

Cell Formulas
Range	Formula
E2:E16	E2	=IFERROR(LET(iVal,A2,iLog,ROUNDUP(LOG10(iVal),0),maybe,IF(ROUND(iVal*100,-iLog-3)=0,ROUND(iVal*100,-iLog-2),ROUND(iVal*100,-iLog-3)),maybe&"%"),NA())
F2:F16	F2	=ForQnQ(A2)
G2:G16	G2	=IFERROR(LEFT(A2*100,FIND(MID(SUBSTITUTE(A2,0,""),2,99),A2*100)), LEFT(ROUND(A2*100,1),99))&"%"
C2:C16	C2	=A2

Thank you so much! This works almost perfectly!

So, as a solution I ended up with the following formula, which combines the two working approaches for numbers >1% and numbers <1% (the user function posted above).

=IFERROR(IF(H4>1%;ForQnQ(H4);LET(iVal;H4;iLog;ROUNDUP(LOG10(iVal);0);maybe;IF(ROUND(iVal*100;-iLog-3)=0;ROUND(iVal*100;-iLog-2);ROUND(iVal*100;-iLog-3));maybe&"%"));"n.a")

HOWEVER, for some reason the one thing it gets wrong is when it needs to round values like 1.068%. Instead of the expected 1.1% it does 1.07% (or 2.092% becomes 2.09% instead of the expected 2.1%, 1.074% becomes 1.07% instead of 1.1% - in short it gets Z.0X wrong, where X needs to be rounded up). The formula as is already helps me out a lot, but if one of you kind folks could look into that, I would be very very grateful.

Thank you!

 

[Anthony47]

However you ignored my question:

I didn't find a rule that explains why inputs with different decimals should be output with the same decimals (yellow data), or viceversa data having the same decimals should be output with different decimals (green data)

So I don't know rework it
Also I don't understand why you need different approaches for input lower or higher than 1%

 

[myall_blues]

As @Anthony47 points out, your rules aren’t consistent.
In your original post you wanted to show up to the first non-zero. 1.068 rounded is 1.07, and expressed to the first non-zero is also 1.07. Likewise 1.074 rounded to one significant figure is 1.07, which is also the value expressed to the first non-zero.