Combining 2 cells - Lose number format when combing

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

bobm - 25 Apr 2004 03:38 GMT

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

Reply to this Message

bobm - 25 Apr 2004 03:41 GMT

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

Thanks again!!

> 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
[quoted text clipped - 15 lines]
>
>Thanks!!

Reply to this Message

JE McGimpsey - 25 Apr 2004 04:07 GMT

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

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

Reply to this Message

bobm - 25 Apr 2004 04:24 GMT

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.

>That would require a VBA Event Macro. Is that something you're
>interested in?
>
>> Also, does anyone know of a way to format the mean in the new cell as
>> black and the sd as red?

Reply to this Message

JE McGimpsey - 25 Apr 2004 05:25 GMT

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

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

Reply to this Message

rbanks - 26 Apr 2004 22:39 GMT

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

--
Message posted from http://www.ExcelForum.com

Reply to this Message

JE McGimpsey - 25 Apr 2004 03:46 GMT

One way:

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

> 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
[quoted text clipped - 15 lines]
>
> Thanks!!

Reply to this Message