Custom Number Format with Text as formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
Click to expand...

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 said:
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
Click to expand...

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
Click to expand...
 
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!
 
Back
Top