Multiple Formula Custom Formatting
 

I was recently challenged to create a formula that displays two values
separated by a slash in the same cell. For example A1=40 and B2= 10. C3
displays [30 / .75]. The difference between the two, and what should be the
percentage. The formula I put together is =(A1-B2)&" / "&((A1-B2)/A1).

The formula works but I can not get the second value to format as a %, it
only displays as a decimal. Does anyone know how I can make the second value
display as a percentage without messing up the formatting of the first value?

Any advice would be much appreciated.
Thanks, Carter

Multiple Formula Custom Formatting
 

You want the Text function, as in:
=(A1-B2)&" / "&TEXT((A1-B2)/A1,"0.00%")

You can also format the first number, as in:
=TEXT(A1-B2,"0")&" / "&TEXT((A1-B2)/A1,"0.00%")

If you don't want the "%" showing, you will need to divide by 100, as in:
=(A1-B2)&" / "&TEXT((A1-B2)/A1/100,".00")

Regards,
Fred


"Carter" wrote in message
...
I was recently challenged to create a formula that displays two values
separated by a slash in the same cell. For example A1=40 and B2= 10. C3
displays [30 / .75]. The difference between the two, and what should be
the
percentage. The formula I put together is =(A1-B2)&" / "&((A1-B2)/A1).

The formula works but I can not get the second value to format as a %, it
only displays as a decimal. Does anyone know how I can make the second
value
display as a percentage without messing up the formatting of the first
value?

Any advice would be much appreciated.
Thanks, Carter

Multiple Formula Custom Formatting
 

Try it like this...

=A1-B2&" / "&TEXT((A1-B2)/A1,"0%")

--
Biff
Microsoft Excel MVP


"Carter" wrote in message
...
I was recently challenged to create a formula that displays two values
separated by a slash in the same cell. For example A1=40 and B2= 10. C3
displays [30 / .75]. The difference between the two, and what should be
the
percentage. The formula I put together is =(A1-B2)&" / "&((A1-B2)/A1).

The formula works but I can not get the second value to format as a %, it
only displays as a decimal. Does anyone know how I can make the second
value
display as a percentage without messing up the formatting of the first
value?

Any advice would be much appreciated.
Thanks, Carter

Multiple Formula Custom Formatting
 

Hi try

=(A1-B2)&" / "&((A1-B2)/A1)*100&"%"

"Carter" wrote:

I was recently challenged to create a formula that displays two values
separated by a slash in the same cell. For example A1=40 and B2= 10. C3
displays [30 / .75]. The difference between the two, and what should be the
percentage. The formula I put together is =(A1-B2)&" / "&((A1-B2)/A1).

The formula works but I can not get the second value to format as a %, it
only displays as a decimal. Does anyone know how I can make the second value
display as a percentage without messing up the formatting of the first value?

Any advice would be much appreciated.
Thanks, Carter

Multiple Formula Custom Formatting
 

=(A1-B2) & " / " & TEXT((A1-B2)/A1,"0%")

OR

=(A1-B2) & " / " & TEXT((A1-B2)/A1,"0.00%")


Gord Dibben MS Excel MVP

On Tue, 9 Feb 2010 10:19:01 -0800, Carter
wrote:

I was recently challenged to create a formula that displays two values
separated by a slash in the same cell. For example A1=40 and B2= 10. C3
displays [30 / .75]. The difference between the two, and what should be the
percentage. The formula I put together is =(A1-B2)&" / "&((A1-B2)/A1).

The formula works but I can not get the second value to format as a %, it
only displays as a decimal. Does anyone know how I can make the second value
display as a percentage without messing up the formatting of the first value?

Any advice would be much appreciated.
Thanks, Carter