Showing 2 decimals in mixed number/text cell

**Johnmus** · 05-11-2011, 04:00 PM

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:

=MAX(IF(BH4:BH81<>"Exh",BE4:BE81))&" "&INDEX(B4:B81,MATCH(1,((BH4:BH81<>"Exh")*(BE4:BE81=MAX(IF(BH4:BH81<>"Exh",BE4:BE81)))),0))

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!

**romperstomper** · 05-11-2011, 04:48 PM

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))

**Johnmus** · 05-11-2011, 05:30 PM

Thanks Romperstomper!

I suspected it was easy. Thanks for the help!

John

**Johnmus** · 05-15-2011, 03:11 AM

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.

**romperstomper** · 05-15-2011, 01:57 PM

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?

**Johnmus** · 05-16-2011, 10:08 PM

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!