How do you properly align average and standard deviation outputs in a spreadsheet?

Question

I am not sure of the best way of formulating this question right now so I will use an example using random numbers. I start with values assigned to IDs, so that each ID can be n=1, n=2, ... etc

ID  Value   
1   1235        
1   326     
1   567     
2   768     
2   646     
3   4367        
3   346     
3   35      
4   436     
5   3467        
5   46      
6   3467        
6   3532        
6   457     
7   3463        
7   3463
7   9328
7   2498

etc

I want to calculate in Excel/Calc the Average and SD so that the values are properly aligned (ideally there would be merged cells), given its one, two, three... etc cells of input, one cell of output.

Example Screenshot:

What I want to obtain. AVG and SD mean proper values for the (random) given data; thus AVG and SD are properly aligned]1

I want, that is, an automated way of calculating the Average and SD having into account different n, so that it is properly aligned/formatted.

There must be an easy way of doing it but right now I am clueless. -_-

I'd appreciate any suggestion.

Answer 1

This is not hard if you assume that Column A is sorted, so we are dealing with contiguous ranges in Column B (which is what the visual aspects of your question suggest).  Set up Column E as a helper column with this formula:

• E2 → =IF(A2=A3, E3, ROW())

For each row, this identifies the last row of the range that the current row is in.  Then you can get the results you want with

• B2 → =IF(A1=A2, "", AVERAGE(B2:INDIRECT("B" & E2)))
• C2 → =IF(A1=A2, "", STDEV(B2:INDIRECT("B" & E2)))

(Or use whatever method you want for calculating standard deviation.)   This checks whether it is the first row of a range. If it is, it uses the INDIRECT() function to construct a range between the current cell and the last cell with the same ID value.

       

And, of course, you can hide Column E, or use some out-of-sight column (e.g., Z) as the helper column.  Note that this solution does not use array formulas.

Answer 2

This isn't exactly what you asked for, but I'd use a pivot table:

(I changed the first column header to ID and formatted the second and third columns to only show two decimal places, otherwise it's just what's shown in the PivotTable Builder.)

You can get what you asked for by placing:

=IF($A2=$A1,"",AVERAGEIF($A2:$A19,$A2,$B2:$B19))

in C2, and:

=IF($A2=$A1,"",STDEV.P(INDIRECT("R"&MATCH($A2,$A:$A,0)&"C2:R"&MATCH($A2,$A:$A,1)&"C2",0)))

in D2 and filling both columns down. The outer IF in each formula is to place the value only in the first row containing a particular ID. The rest of the C2 formula should be straight-forward, AVERAGEIF averages numbers for which a specific criteria is true. In this case, it looks in the first column, selects numbers with the same value as the value in the current row in the first column, then averages the corresponding numbers in the second column.

Unfortunately there's no "STDEVIF" (at least in Excel 2011 on the Mac, maybe there is in whatever spreadsheet program you're using. If so, just use it in place of AVERAGE in the C2 formula), so you have to be tricky :-). The approach is to find the range of cells you want the standard deviation of, construct a reference to those cells, then pass that reference to STDEV.P. The range is constructed by finding the first row in column 1 with the same value as the value in the current row in column 1, then finding the last row in column 1 with the same value as the value in the current row in column 1. Those two values delineate the top and bottom of the subrange of column 1 you want to use, so construct an R1C1 style reference in a string, use INDIRECT to turn it into an actual reference, then pass that to STDEV.P. Simple! :-) Ok, it's a little hideous, but it works.

Answer 3

Excel doesn't have functionality like this built in. You would need to use subtotals or pivot tables which don't do what your after.

To build the table with formulas use the following two functions.

In C2 put

=IF(A2<>A1,AVERAGEIF($A$2:$A$13,A2,$B$2:$B$13),"")

In D2 put and enter by pressing ctrl+shift+enter

=IF(A2<>A1,STDEV(IF($A$2:$A$13=A2,$B$2:$B$13)),"")

Then copy these formulas down

The IF(A2<>A1... at the start basically says only show something if Column A differs between this row and the one above.

Averageif works exactly like you would think it should.

Column D is an array formula so first it goes in and does the if statement on each cell in the range and it would return an array something like (1,14,13,3,FALSE,FALSE...) for each cells and then calculates the standard deviation on this which should basically ignore the FALSE values.

This method assumes the data is sorted on ID. The average and Std Dev calcs would be right if it wasn't sorted but they would show up everytime ID changes not just the first.