Formatting a cell to display varying decimal places.

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

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

--

Vasant

"JayE" <JayE@discussions.microsoft.com> wrote in message
news:418921A4-4AA3-4CE2-B63D-A839206524BF@microsoft.com...
> 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, 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

"Vasant Nanavati" wrote:

> Format column B as Custom, 0.########%
>
> --
>
> Vasant
>
> "JayE" <JayE@discussions.microsoft.com> wrote in message
> news:418921A4-4AA3-4CE2-B63D-A839206524BF@microsoft.com...
> > 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
> >
> >
> >
>
>
>

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


--
Regards,
Tom Ogilvy




"JayE" <JayE@discussions.microsoft.com> wrote in message
news:82628122-3529-4476-B7C2-1A93C21B6B63@microsoft.com...
> 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
>
> "Vasant Nanavati" wrote:
>
> > Format column B as Custom, 0.########%
> >
> > --
> >
> > Vasant
> >
> > "JayE" <JayE@discussions.microsoft.com> wrote in message
> > news:418921A4-4AA3-4CE2-B63D-A839206524BF@microsoft.com...
> > > 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
> > >
> > >
> > >
> >
> >
> >

Nice one, Tom.

Regards,

Vasant

"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:ODDQdSjXFHA.584@TK2MSFTNGP15.phx.gbl...
> Use a custom format like:
> [<=0]"";[<0.0001]0.0000%;0.00%;
>
>
> --
> Regards,
> Tom Ogilvy
>
>
>
>
> "JayE" <JayE@discussions.microsoft.com> wrote in message
> news:82628122-3529-4476-B7C2-1A93C21B6B63@microsoft.com...
> > 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
> >
> > "Vasant Nanavati" wrote:
> >
> > > Format column B as Custom, 0.########%
> > >
> > > --
> > >
> > > Vasant
> > >
> > > "JayE" <JayE@discussions.microsoft.com> wrote in message
> > > news:418921A4-4AA3-4CE2-B63D-A839206524BF@microsoft.com...
> > > > 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
> > > >
> > > >
> > > >
> > >
> > >
> > >
>
>

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




"JayE" <JayE@discussions.microsoft.com> wrote in message
news:82628122-3529-4476-B7C2-1A93C21B6B63@microsoft.com...
> 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
>
> "Vasant Nanavati" wrote:
>
> > Format column B as Custom, 0.########%
> >
> > --
> >
> > Vasant
> >
> > "JayE" <JayE@discussions.microsoft.com> wrote in message
> > news:418921A4-4AA3-4CE2-B63D-A839206524BF@microsoft.com...
> > > 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
> > >
> > >
> > >
> >
> >
> >

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

--

Vasant

"Vasant Nanavati" <vasantn *AT* aol *DOT* com> wrote in message
news:OmIciVjXFHA.3920@TK2MSFTNGP10.phx.gbl...
> 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
>
>
>
>
> "JayE" <JayE@discussions.microsoft.com> wrote in message
> news:82628122-3529-4476-B7C2-1A93C21B6B63@microsoft.com...
> > 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
> >
> > "Vasant Nanavati" wrote:
> >
> > > Format column B as Custom, 0.########%
> > >
> > > --
> > >
> > > Vasant
> > >
> > > "JayE" <JayE@discussions.microsoft.com> wrote in message
> > > news:418921A4-4AA3-4CE2-B63D-A839206524BF@microsoft.com...
> > > > 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
> > > >
> > > >
> > > >
> > >
> > >
> > >
>
>

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" wrote:

> But needless to say, Tom's solution is much better :-).
>
> --
>
> Vasant
>
> "Vasant Nanavati" <vasantn *AT* aol *DOT* com> wrote in message
> news:OmIciVjXFHA.3920@TK2MSFTNGP10.phx.gbl...
> > 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
> >
> >
> >
> >
> > "JayE" <JayE@discussions.microsoft.com> wrote in message
> > news:82628122-3529-4476-B7C2-1A93C21B6B63@microsoft.com...
> > > 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
> > >
> > > "Vasant Nanavati" wrote:
> > >
> > > > Format column B as Custom, 0.########%
> > > >
> > > > --
> > > >
> > > > Vasant
> > > >
> > > > "JayE" <JayE@discussions.microsoft.com> wrote in message
> > > > news:418921A4-4AA3-4CE2-B63D-A839206524BF@microsoft.com...
> > > > > 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
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
>
>
>