| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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(D10,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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formatting a cell to display varying decimal places.
Format column B as Custom, 0.########%
-- Vasant "JayE" wrote in message ... 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(D10,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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formatting a cell to display varying decimal places.
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" wrote in message ... 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(D10,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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formatting a cell to display varying decimal places.
Use a custom format like:
[<=0]"";[<0.0001]0.0000%;0.00%; -- Regards, Tom Ogilvy "JayE" wrote in message ... 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" wrote in message ... 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(D10,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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formatting a cell to display varying decimal places.
Sorry; didn't read carefully enough. Try:
=IF(D1<=0,"",IF(D1=0.0001,TEXT(D1,"0.00%"),TEXT(D 1,"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" wrote in message ... 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" wrote in message ... 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(D10,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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formatting a cell to display varying decimal places.
Nice one, Tom.
Regards, Vasant "Tom Ogilvy" wrote in message ... Use a custom format like: [<=0]"";[<0.0001]0.0000%;0.00%; -- Regards, Tom Ogilvy "JayE" wrote in message ... 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" wrote in message ... 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(D10,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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formatting a cell to display varying decimal places.
But needless to say, Tom's solution is much better :-).
-- Vasant "Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message ... Sorry; didn't read carefully enough. Try: =IF(D1<=0,"",IF(D1=0.0001,TEXT(D1,"0.00%"),TEXT(D 1,"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" wrote in message ... 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" wrote in message ... 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(D10,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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formatting a cell to display varying decimal places.
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 ... Sorry; didn't read carefully enough. Try: =IF(D1<=0,"",IF(D1=0.0001,TEXT(D1,"0.00%"),TEXT(D 1,"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" wrote in message ... 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" wrote in message ... 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(D10,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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formatting a cell to display varying decimal places.
Thanks for the feedback, Jay; always appreciated!
-- Vasant "JayE" wrote in message ... 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 ... Sorry; didn't read carefully enough. Try: =IF(D1<=0,"",IF(D1=0.0001,TEXT(D1,"0.00%"),TEXT(D 1,"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" wrote in message ... 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" wrote in message ... 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(D10,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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| conditonally formatting decimal places | Excel Discussion (Misc queries) | |||
| How many decimal places can a cell display? | Excel Worksheet Functions | |||
| Aligning data with varying decimal places | Excel Discussion (Misc queries) | |||
| Currency Formatting to 3 decimal Places | Excel Programming | |||
| Conditional Formatting - number of decimal places | Excel Programming | |||
Linear Mode
