I want to format a cell to return a number value with a text, depending on
what the value of the number is.
For example if A1 contains the value 4.1%, then I want to use a formula in
B1 to read "4.1% more work days", if the number in A1 is negative, I need B1
to read "4.1 fewer work days". The catch is that I also want to add
additional text, based on an if then statement which I can add with no
problem. Therefore, I cannot simply use a custom number format in the cell
format, and need to use a formula. My challenge is formatting using the
following as a starting point:

=TEXT(A1,"##.##%_more;##.##%_more")&" workdays".

Unfortunately, this formula does not work, and I am not sure what I am
missing. Any help would be much appreciated.

Thanks much

One way:

=TEXT(A1,"##.##% ")&IF(A1<0,"fewer","more") & " workdays"


In article ,
mzehr wrote:

I want to format a cell to return a number value with a text, depending on
what the value of the number is.
For example if A1 contains the value 4.1%, then I want to use a formula in
B1 to read "4.1% more work days", if the number in A1 is negative, I need B1
to read "4.1 fewer work days". The catch is that I also want to add
additional text, based on an if then statement which I can add with no
problem. Therefore, I cannot simply use a custom number format in the cell
format, and need to use a formula. My challenge is formatting using the
following as a starting point:

=TEXT(A1,"##.##%_more;##.##%_more")&" workdays".

Unfortunately, this formula does not work, and I am not sure what I am
missing. Any help would be much appreciated.

Thanks much

On Mon, 14 May 2007 11:04:01 -0700, mzehr
wrote:

I want to format a cell to return a number value with a text, depending on
what the value of the number is.
For example if A1 contains the value 4.1%, then I want to use a formula in
B1 to read "4.1% more work days", if the number in A1 is negative, I need B1
to read "4.1 fewer work days". The catch is that I also want to add
additional text, based on an if then statement which I can add with no
problem. Therefore, I cannot simply use a custom number format in the cell
format, and need to use a formula. My challenge is formatting using the
following as a starting point:

=TEXT(A1,"##.##%_more;##.##%_more")&" workdays".

Unfortunately, this formula does not work, and I am not sure what I am
missing. Any help would be much appreciated.

Thanks much

You're actually pretty close. You need to set up your format as a conditional
format string. So something like:

=TEXT(A1,"[0]##.##%"" more"";[<0]##.##%"" fewer""")&" workdays"

might give you the more/fewer depending on the value in A1.

You'll need to decide what you want to do if the contents of A1 is zero or
blank, and make the appropriate changes in the TEXT function format statement
--ron

Ron,
Fantastic! That was exactly what I was looking for! Thanks for your help.
Mike

"Ron Rosenfeld" wrote:

On Mon, 14 May 2007 11:04:01 -0700, mzehr
wrote:

I want to format a cell to return a number value with a text, depending on
what the value of the number is.
For example if A1 contains the value 4.1%, then I want to use a formula in
B1 to read "4.1% more work days", if the number in A1 is negative, I need B1
to read "4.1 fewer work days". The catch is that I also want to add
additional text, based on an if then statement which I can add with no
problem. Therefore, I cannot simply use a custom number format in the cell
format, and need to use a formula. My challenge is formatting using the
following as a starting point:

=TEXT(A1,"##.##%_more;##.##%_more")&" workdays".

Unfortunately, this formula does not work, and I am not sure what I am
missing. Any help would be much appreciated.

Thanks much

You're actually pretty close. You need to set up your format as a conditional
format string. So something like:

=TEXT(A1,"[0]##.##%"" more"";[<0]##.##%"" fewer""")&" workdays"

might give you the more/fewer depending on the value in A1.

You'll need to decide what you want to do if the contents of A1 is zero or
blank, and make the appropriate changes in the TEXT function format statement
--ron

Thanks JE. While your response will also work, and is very helpful, Ron's
response was exactly what I was looking for. I was having trouble getting
the text qualifier formated properly. I appreciate your time in responding!

"JE McGimpsey" wrote:

One way:

=TEXT(A1,"##.##% ")&IF(A1<0,"fewer","more") & " workdays"


In article ,
mzehr wrote:

I want to format a cell to return a number value with a text, depending on
what the value of the number is.
For example if A1 contains the value 4.1%, then I want to use a formula in
B1 to read "4.1% more work days", if the number in A1 is negative, I need B1
to read "4.1 fewer work days". The catch is that I also want to add
additional text, based on an if then statement which I can add with no
problem. Therefore, I cannot simply use a custom number format in the cell
format, and need to use a formula. My challenge is formatting using the
following as a starting point:

=TEXT(A1,"##.##%_more;##.##%_more")&" workdays".

Unfortunately, this formula does not work, and I am not sure what I am
missing. Any help would be much appreciated.

Thanks much

On Mon, 14 May 2007 12:27:02 -0700, mzehr
wrote:

Ron,
Fantastic! That was exactly what I was looking for! Thanks for your help.
Mike

You're welcome. Glad to help. Thanks for the feedback.
--ron