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!

 

[Phuoc]

Try this

Book1
	A	B
1
2
3
4	0.60%	0.6%
5	0.045%	0.05%
6	0.033%	0.03%
7	55.60%	55.6%
8	1.12%	1.12%
9	0.0092%	0.01%
Sheet2

Cell Formulas
Range	Formula
B4:B9	B4	=TEXT(A4,INDEX({"0%","0.0%","0.00%"},MATCH(TRUE,MOD(A4*1000000000,{10000000,1000000,1})=0,0)))

 

[quauq]

Try this

Book1
	A	B
1
2
3
4	0.60%	0.6%
5	0.045%	0.05%
6	0.033%	0.03%
7	55.60%	55.6%
8	1.12%	1.12%
9	0.0092%	0.01%
Sheet2

Cell Formulas
Range	Formula
B4:B9	B4	=TEXT(A4,INDEX({"0%","0.0%","0.00%"},MATCH(TRUE,MOD(A4*1000000000,{10000000,1000000,1})=0,0)))

Thank you!

this formula does indeed work fine for the values provided. However, I tried adapting it to very small values and for some reason it does not round correctly anymore.

=TEXT(B12;INDEX({"0%";"0,0%";"0,00%";"0,000%";"0,0000%";"0,00000%";"0,000000%";"0,0000000%";"0,00000000%";"0,000000000%"};MATCH(TRUE;MOD(B12*1000000000;{10000000;1000000;100000;10000;1000;100;10;1})=0;0)))

With this formula 0,000000005 becomes 0,0000005%, which is correct as far as decimal to % goes, but it should be 0,00000001% if rounded correctly.

 

[Anthony47]

Could you provide a small sample of conversion you need to obtain? An image would be enough, an XL2BB table would be better

 

[quauq]

Thank you!

this formula does indeed work fine for the values provided. However, I tried adapting it to very small values and for some reason it does not round correctly anymore.

=TEXT(B12;INDEX({"0%";"0,0%";"0,00%";"0,000%";"0,0000%";"0,00000%";"0,000000%";"0,0000000%";"0,00000000%";"0,000000000%"};MATCH(TRUE;MOD(B12*1000000000;{10000000;1000000;100000;10000;1000;100;10;1})=0;0)))

With this formula 0,000000005 becomes 0,0000005%, which is correct as far as decimal to % goes, but it should be 0,00000001% if rounded correctly.

Could you provide a small sample of conversion you need to obtain? An image would be enough, an XL2BB table would be better

Sure can (unfortunately, XL2BB is not possible, as I am not allowed to install anything on my company PC):

I hope this clears up any confusion I may have produced.

This is only a small sample, and values can be very small, so the formula should allow for at least 10 decimals being 0 before the first non-zero one.

 

[Anthony47]

Maybe, in D4 and then copied down:

=IFERROR(LET(iVal,A4,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())

This runs on Office 365
Results are in the image (column C is not necessary for the calculation)

 

[quauq]

Maybe, in D4 and then copied down:

=IFERROR(LET(iVal,A4,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())

This runs on Office 365
Results are in the image (column C is not necessary for the calculation)

This works perfectly well for any % smaller than 1; but for % values higher than that the formula rounds more than would be appropriate (such numbers should be displayed as they are with one decimal):

In the examples, 31,2000% should be displayed as 31,2%, 4,56% as 4,6% and so on.

Is there a way to iron this out?

Thank you!

 

[myall_blues]

Are any of your numbers greater than or equal to 100%?

 

[quauq]

Are any of your numbers greater than or equal to 100%?

No, they can only ever be smaller than 100% and larger than or equal to 0% (0% should be displayed as n.a. or 0.0%)

 

[myall_blues]

What about:

=IFERROR(LEFT(A1*100,FIND(MID(SUBSTITUTE(A1,0,""),2,99),A1*100)), LEFT(ROUND(A1*100,1),99))&"%"