Cell format to report StDev range

[OverKnight]

Hello - I have a sheet with means and standard deviations calculated, but I would like to report the +/- 1, +/- 2 and +/- 3 standard deviations as a range, in this format: x.xxxx - x.xxxx, i.e., if the mean is 0.341 and the SD is 0.0029, I would like to report +/- 1 SD as "0.3381 - 0.3439". The "space hyphen space" between the two numbers would be text, and I think this could be done with cell formatting, but I haven't been able to figure this out.

Thank you.

[MrShorty]

I cannot see getting cell formatting to do this.

I will usually use the CONCATENATE() and TEXT() functions to do this: =CONCATENATE(TEXT(mean,"0.0000")," - ",TEXT(1*sdev,"0.0000"). Of course, this results in a text string with two pieces of information, so I usually treat this as a "dead end" calculation. I usually make sure I leave the mean and standard deviation (to their full precision) separately in their own cells for use in downstream calculations.

[OverKnight]

Thank you, MrShorty. I don't know why I thought cell formatting could do this... that's why I come here!

Sorry for the late response. I tried to attach a sheet yesterday, but was not able to. A1:E4 on the attached sheet contains how I would like the data to be presented. A8:E11 contains your formulae, which I believe I've entered correctly. Hopefully this sheet better explains what I'm trying to do.

Thank you.

[berlan]

Hi, another way (see attached),

C9: =TEXT($A9-$B9*COLUMNS($C9:C9),"0.0000")&" - "&TEXT($A9+$B9*COLUMNS($C9:C9),"0.0000")

and copy across.

Hope this helps,
berlan

[OverKnight]

Thank you, Berlan, and very sorry about the late response; I was away from the office.

This works exactly as needed, but I see now that I'll need to include a text label at the beginning of each, so it would display like this: +/- 1 SD: 0.3288 - 0.3345. How would I do this?

Again, thank you for your help.