Standard Deviation forumula: More than 30 values?

**Goalie35** · 12-15-2009, 05:26 PM

I'm trying to create a standard deviation formula based on 52 values. Unfortunately though, excel only accepts 30. Here's my formula:

STDEV(S7,U7,W7,Y7,AA7,AC7,AE7,AG7,AI7,AK7,AM7,AO7,AQ7,AS7,AU7,AW7,AY7,BA7,BC7,BE7,BG7,BI7,BK7,BM7,BO7,BQ7,BS7,BU7,BW7,BY7, CA7, CC7, CF7, CH7)

Excel then throws the "You've entered too many arguments for this function" error.

Does anyone know how I could set this up so I can use more than 30 values for this standard deviation?

Thanks.

**DonkeyOte** · 12-15-2009, 05:33 PM

You don't specify the complete range you require but in basic terms you can use an Array

=STDEVA(IF(ISNUMBER(S7:DY7)*MOD(COLUMNS(S7:DY7),2),S7:DY7))
confirmed with CTRL + SHIFT + ENTER

**ChemistB** · 12-15-2009, 05:37 PM

Another possibility (requires more manual work); select your cells by holding down the control key and clicking on them. Then in the name box (where the cell id is located), type in a name (e.g. test) and enter.
Then the formula is STDEV(test)

**Palmetto** · 12-15-2009, 05:34 PM

Create named ranges for sets of thirty cells and used the named range as the argument instead of the cell. You can then have 900 samples.

=STDEV(named_rng1,named_rng2,named_rng3, etc.)