Custom Number Format with Text as formula

[Guest]

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

 

[JE McGimpsey]

One way:

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

 

[Ron Rosenfeld]

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

 

[Guest]

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

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

 

[Guest]

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!

 

[Ron Rosenfeld]

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