Showing decimals in combination text cell

**Johnmus** · 03-03-2011, 04:59 PM

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

**ChemistB** · 03-03-2011, 05:02 PM

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

**Johnmus** · 03-03-2011, 06:26 PM

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

**daddylonglegs** · 03-03-2011, 06:31 PM

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