Formatting a cell to display varying decimal places.

[JayE]

I have a worksheet that in one column (B) I have a formula that pulls results
from another column (D). The results in D range from 0 to 3.00. If the
results in D are greater then 0 they are displayed in B. However if the
reslut is 0, then nothing is to appear in B. Here is my formula in Column B
Row 1 : =IF(D1>0,D1," ").

Here is the problem, if the results in D1 are .0001 it needs to apear in B1
as " .01% ( two decimal places). However, if the result in D1 is .000001,
then the result should appear as .0001%. In other words, if the results in
column D are >= to .0001, then they should appear as x.xx% in column B. If
the rsults in column D are <= to .00009, then thyey should appear as x.xxxx%
in column D.

Can anyone give me a solution? Thanks.
Jay

 

[Vasant Nanavati]

Format column B as Custom, 0.########%

 

[JayE]

Vasant, thanks. However, unless I did something incorrectly my result in
column B ranges from 0. to 0.0000. What I need is the following:

If the result in column D is > or = to .0001 then the answer in Column B
must be stated as 0.00%.

If the result in column D is < the .0001 then the answer in column B must be
stated as 0.0000%.

Jay

 

[Tom Ogilvy]

Use a custom format like:
[<=0]"";[<0.0001]0.0000%;0.00%;

 

[Vasant Nanavati]

Sorry; didn't read carefully enough. Try:

=IF(D1<=0,"",IF(D1>=0.0001,TEXT(D1,"0.00%"),TEXT(D1,"0.0000%")))

Keep in mind that these will be text strings and not numbers, so that they
cannot be used directly in calculations. However, you can use VALUE(D1) in
place of D1 for calculations.

 

[Vasant Nanavati]

Nice one, Tom.

Regards,

Vasant

Use a custom format like:
[<=0]"";[<0.0001]0.0000%;0.00%;


--
Regards,
Tom Ogilvy

Vasant, thanks. However, unless I did something incorrectly my result in
column B ranges from 0. to 0.0000. What I need is the following:

If the result in column D is > or = to .0001 then the answer in Column B
must be stated as 0.00%.

If the result in column D is < the .0001 then the answer in column B

must

be
stated as 0.0000%.

Jay

apear

in

 

[Vasant Nanavati]

But needless to say, Tom's solution is much better .

 

[JayE]

Tom, thanks. It is working perfectly. Vasant,thanks as well, the result in
B1 is for a label and no futher calculations would be necessary. Your
solution would have worked fine.

Jay

 

[Vasant Nanavati]

Thanks for the feedback, Jay; always appreciated!