Keywords: cell formatting, two values in one cell, average value, confidence interval
Version: Should work in any Excel version from Excel 97 and forward.
Sometimes it is useful to have the output from your excel sheets arranged in such a way that you can cut and paste directly into your Word file (report, paper och whatever it might be) getting a nice, readable result and minimizing the manual editing.
In this example I will show how to summarize a population average and its confidence interval (C.I) in one cell.
1. First of all, you need some data to play around with. Arrange your data as seen in picture 1 below. I have called my data “Sample” and “Value”, but this is of course arbitrary, and you can select any data you wish for this example.
2. In cell C13 we calculate the population average (formula “=average(C3:C12)“), and in cell C15 we calculate our confidence interval (formula “=1.96*C14/SQRT(4)/2“).
Picture 1.
3. Get get excel to arrange the output as we want it we will ender a special string in cell C16 looking like this: =ROUND(C13,1)&” ± “&(ROUND(C$15,1)). Does it look strange? Don’t worry, I will explain.
The ROUND(a,b) formula simply rounds the value in cell a to the nearest b decimals. The “&“ sign tells Excel that a text string follows that will be displayed in the cell, so ‘ &” ± “& ‘ tells Excel to display a plus/minus sign, flanked by two spaces. And the closing (ROUND(C$15,1) tells Excel to display our C.I rounded to the nearest two decimals. The resulting output looks like this: 280.2 ± 5.6.
4. If you select cell C16 and press ctrl+C (copy) and then press ctrl+V (paste) in your word document you will get a nice looking formatted output looking like in picture 2 below, without any extra manual editing. This can be time saving expecially if you have a lot of data that need to be included in your report or paper.
Picture 2.
Popularity: 6% [?]
