Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Number & Text (2003)

    Loungers, further to an earlier post regarding this. I have developed a formula ="This represents a "&TEXT(L22,"#0%")&" from "&TEXT(J22,"#")&" in"&TEXT($J$6," mmmm")'. This returns a value of , "This represents a -1.33333333333333% increase from 75 in May".

    How can if change the formula to return "This represents a -1.3% increase from 75 in May"

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Number & Text (2003)

    I guess:

    ="This represents a "&TEXT(L22,"#0.0%")& IF(L22>0,"in","de") & "crease from "&TEXT(J22,"#")&" in"&TEXT($J$6," mmmm")'
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Number & Text (2003)

    The formula as posted would result in "This represents a -1% from 75 in May". To display the percentage with one decimal place, use <code>TEXT(L22, "#.0%")</code>, and you need to add the word increase.

    <code>="This represents a "&TEXT(L22,"#.0%")&" increase from "&TEXT(J22,"#")&" in"&TEXT($J$6," mmmm")</code>

  4. #4
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Number & Text (2003)

    Thanks Jan & Hans for the suggestions.

    I thought it better that I attached a cutdown version of the spreadsheet so you can see what I need to do

    Thanks for your help

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Number & Text (2003)

    Cells L13, L16, L19 and L22 contain a concatenation, therefore text. Applying the TEXT function to them has no effect. You must use the TEXT function in the formulas for L13 etc. Moreover, you should put the % sign in the TEXT function, not in the formula that determines increase/decrease.

  6. #6
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Number & Text (2003)

    Hans, you've do it again!!

    Thanks very much

Posting Permissions

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