Results 1 to 8 of 8

Thread: % with &

  1. #1
    Join Date
    Feb 2005
    Location
    Northern Ireland
    Posts
    24

    Smile Unanswered: % with &

    I have a cell adding various cells and calculating an average. It is formatted as a percentage.

    =SUM(B38,B79,B118,B162,B201,B240,B279,B323,B362,B4 06)/10

    I want to add text after the result but when I add

    &" booked so far"

    to the end it removes the % sign and the number returned is formatted to many decimal places. Is there a way to do:

    40% booked so far

    instead of

    0.403333333333333 booked so far
    Thomas J Marshall

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    format the column as text/string as you value will be a text or string

    =(SUM(B38,B79,B118,B162,B201,B240,B279,B323,B362,B 4 06)/10) * 100 & "% booked so far"
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2005
    Location
    Northern Ireland
    Posts
    24
    Quote Originally Posted by healdem View Post
    format the column as text/string as you value will be a text or string

    =(SUM(B38,B79,B118,B162,B201,B240,B279,B323,B362,B 4 06)/10) * 100 & "% booked so far"
    It then returns TRUE
    Thomas J Marshall

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Assuming your value is in cell A1
    Code:
    =TEXT(A1 * 100, "##00.00")&"% booked so far"
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2005
    Location
    Northern Ireland
    Posts
    24
    Quote Originally Posted by gvee View Post
    Assuming your value is in cell A1
    Code:
    =TEXT(A1 * 100, "##00.00")&"% booked so far"
    Mmmh! Not getting this! Where does my original formula fit with this?
    Thomas J Marshall

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    or:-
    =(B38,B79,B118,B162,B201,B240,B279,B323,B362,B406)/10 & "% booked so far"
    but you will need to use a text or string format on the cell
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Feb 2005
    Location
    Northern Ireland
    Posts
    24
    Quote Originally Posted by healdem View Post
    or:-
    =(B38,B79,B118,B162,B201,B240,B279,B323,B362,B406)/10 & "% booked so far"
    but you will need to use a text or string format on the cell
    With the cell formatted as TEXT it returns the formula.
    Thomas J Marshall

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Ok so try general format
    The code above works fine im Excel2010 on a clear worksheet.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •