+ Reply to Thread
Results 1 to 5 of 5

Showing decimals in combination text cell

  1. #1
    Registered User
    Join Date
    02-10-2011
    Location
    California, USA
    MS-Off Ver
    Excel 2010
    Posts
    91

    Showing decimals in combination text cell

    Hi,

    I am using this formula to list score and name in a single cell on a separate sheet.

    =IF(AE4="","",AE4&" "&INDEX($B$4:$B$26,MATCH(COUNT(AE$4:AE4),$AC$4:$AC$26,0)))

    This references in cell AE4 a score with two decimals. It will show the decimals except when the number has no decimals. I would like to show a number with a decimal, even if the number has no decimals. For example I want to show 12.0 (instead of 12) and then the name. I tried formatting the cell but since it is a mixed text/number cell that is not possible.

    Any suggestions?

    Thanks
    Last edited by Johnmus; 03-04-2011 at 08:17 PM.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,191

    Re: Showing decimals in combination text cell

    TEXT(AE4,"0.0")
    Does that work for you?
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    02-10-2011
    Location
    California, USA
    MS-Off Ver
    Excel 2010
    Posts
    91

    Re: Showing decimals in combination text cell

    Yes, that works!

    Thanks!

    =TEXT(AE4="0.00")&" "&INDEX($B$4:$B$26,MATCH(COUNT(AE$4:AE4),$AC$4:$AC$26,0)))

    How would I keep the IF(AE4="","",AE4 component in the entire formual?

    Thanks

  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Showing decimals in combination text cell

    Try

    =IF(AE4="","",TEXT(AE4,"0.00 ")&INDEX($B$4:$B$26,MATCH(COUNT(AE$4:AE4),$AC$4:$AC$26,0)))

    or you could also use FIXED function to specifiy the number of decimals, it defaults to 2 if you don't explicitly state, i.e.

    =IF(AE4="","",FIXED(AE4)&" "&INDEX($B$4:$B$26,MATCH(COUNT(AE$4:AE4),$AC$4:$AC$26,0)))
    Audere est facere

  5. #5
    Registered User
    Join Date
    02-10-2011
    Location
    California, USA
    MS-Off Ver
    Excel 2010
    Posts
    91

    Re: Showing decimals in combination text cell

    Thanks a million. That works great!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1