Combining 2 cells - Lose number format when combing

[bobm]

I have a range of scores for a test in cells A10 thru A100.
In Cell A1 I have a the formula =COUNTA(A10:A100) that returns the
number or n of scores. In cell A2 I have the formula
=SUM(A10:A100)/A1 which returns the mean for the range. In cell A3 I
have the formula =STDEV(A10:A100) which returns the standard deviation
for the range. Cells A2 and A3 are formatted as numbers with 2
decimal places (ex., 1.55).

I've combined cells A2 and A3 into another cell with the formula =A2&
" / " & A3 to be able to view the mean/standard deviation in the same
cell (ex., 9.5 / 1.5).

My problem is that when I combine cells A2 and A3 I loose my number
formatting of 2 decimal places and end up with 6 or more decimals to
the right of the decimal in both the mean and sd. Does anyone know of
a way to retain the 2 decimal place formatting when combining the
numbers in a new cell?

Thanks!!

 

[bobm]

Also, does anyone know of a way to format the mean in the new cell as
black and the sd as red?

Thanks again!!

 

[JE McGimpsey]

One way:

=TEXT(A2,"0.00 \/ ") & TEXT(A3,"0.00")

 

[JE McGimpsey]

That would require a VBA Event Macro. Is that something you're
interested in?

 

[bobm]

I suppose but not if it requires a lot of time on your part. It's
probably not that necessary. Thanks for the help up to this point.

 

[JE McGimpsey]

It doesn't take much time, but sometimes OPs can't use a macro solution.

Put this in the worksheet code module (right-click on the worksheet tab
and choose View Code):

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Application.EnableEvents = False
With Range("A4")
.Value = Format(Range("A2").Value, "0.00") & " / " & _
Format(Range("A3").Value, "0.00")
.Characters(8).Font.Color = RGB(255, 0, 0)
End With
End Sub

 

[rbanks]

Try this:

=CONCATENATE(TEXT(A2,"00.00")," / ",TEXT(A3,"00.00"))


This essentially converts both the SD and the mean to text with
decimal places within the formula.

Let me know if you need other help