Multiple Formula Custom Formatting

C

Carter

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
 
F

Fred Smith

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
 
G

Gord Dibben

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

OR

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


Gord Dibben MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top