+ Reply to Thread
Results 1 to 8 of 8

Showing 2 decimals in mixed number/text cell

  1. 05-11-2011, 04:00 PM #1
    Johnmus
    Johnmus is offline
    Registered User
    Join Date
    02-10-2011
    Location
    California, USA
    MS-Off Ver
    Excel 2010
    Posts
    92

    Showing 2 decimals in mixed number/text cell

    Hi.

    I have created a workbook for band competitions and one of the sheets is for the the awards announcer. This sheet lists the scores and schools. The score and schools are combined in one cell. I have formatted all cells from where the scores are copied to the Awards sheet to show 2 decimals but the formula resulting in a score and school shows 5 decimals. I know that I can use "set precision as displayed", but I am hesitant to do so. Is there a way to show two decimals in a mixed number/text cell?

    Below is the formula to arrive at the score/school:

    Please Login or Register  to view this content.
    This is an array formlula.

    This results in 91.11875 School 8 - I would like it to be 91.12 school 8

    Would it be possible to use a macro to specifically change this to two decimals in these cells? There are four cells where this needs to occur T82, AL82, AZ82 and BE82

    Thanks for any help!
    Reply With QuoteRegister To Reply
  2. 05-11-2011, 04:48 PM #2
    romperstomper
    romperstomper is offline
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,258

    Re: Showing 2 decimals in mixed number/text cell

    Use the TEXT function:
    =TEXT(MAX(IF(BH4:BH81<>"Exh",BE4:BE81)),"0.00")&" "&INDEX(B4:B81,MATCH(1,((BH4:BH81<>"Exh")*(BE4:BE81=MAX(IF(BH4:BH81<>"Exh",BE4:BE81)))),0))
    Remember what the dormouse said
    Feed your head
    Reply With QuoteRegister To Reply
  3. 05-11-2011, 05:30 PM #3
    Johnmus
    Johnmus is offline
    Registered User
    Join Date
    02-10-2011
    Location
    California, USA
    MS-Off Ver
    Excel 2010
    Posts
    92

    Re: Showing 2 decimals in mixed number/text cell

    Thanks Romperstomper!

    I suspected it was easy. Thanks for the help!

    John
    Reply With QuoteRegister To Reply
  4. 05-15-2011, 03:11 AM #4
    Johnmus
    Johnmus is offline
    Registered User
    Join Date
    02-10-2011
    Location
    California, USA
    MS-Off Ver
    Excel 2010
    Posts
    92

    Re: Showing 2 decimals in mixed number/text cell

    Hi,

    Unfortunately, I found out I cannot use the Text option as the formula above with MAX is an integral part of ranking schools. Placing text in front of the formula negates that.

    Any other ideas? Set as displayed solves the problem, but I am hesitant to use it.

    Thanks for the help.
    Reply With QuoteRegister To Reply
  5. 05-15-2011, 01:57 PM #5
    romperstomper
    romperstomper is offline
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,258

    Re: Showing 2 decimals in mixed number/text cell

    I don't follow - your formula was already returning text since you added the "School" part after it, so what difference does the TEXT function make?
    Reply With QuoteRegister To Reply
  6. 05-16-2011, 10:08 PM #6
    Johnmus
    Johnmus is offline
    Registered User
    Join Date
    02-10-2011
    Location
    California, USA
    MS-Off Ver
    Excel 2010
    Posts
    92

    Re: Showing 2 decimals in mixed number/text cell

    When I inserted the TEXT in the formula (AO11) the rankings changed from begining at 0 to beginning at 1. When I deleted the TEXT function it was restored. I have not been able to replicate the error since, so it was probably something I did wrong. I am venturing into VBA, with some great help from this forum. I am attaching a minimal sample. Perhaps you see something I am missing?

    Appreciate the help!
    Last edited by Johnmus; 05-17-2011 at 12:12 PM.
    Reply With QuoteRegister To Reply
  7. 05-17-2011, 02:56 AM #7
    romperstomper
    romperstomper is offline
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,258

    Re: Showing 2 decimals in mixed number/text cell

    I'm not really sure what you want me to look at here since it seems to be working?
    Reply With QuoteRegister To Reply
  8. 05-17-2011, 12:11 PM #8
    Johnmus
    Johnmus is offline
    Registered User
    Join Date
    02-10-2011
    Location
    California, USA
    MS-Off Ver
    Excel 2010
    Posts
    92

    Re: Showing 2 decimals in mixed number/text cell

    Yes , it seems to be working, Thanks for the help! It is appreciated.

    John
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1