Array formula - st deviation

**laurafv** · 03-02-2009, 05:16 PM

Hey guys,

I would need your help, I have a list of 187 countries and I would like to find a way to do this with an array and not by hand.

Attached here is an excel with a sample; I would like to compute the standard deviation of the variable for the period 1980-2008 for each country separately. Is there a way to do this without me having to select by hand the range corresponding to each country in the st dev formula?

Thanks a lot,

Laura.

**DonkeyOte** · 03-02-2009, 05:20 PM

You could use an Array such as:

=STDEV(IF($A$2:$A$117=$A2,$C$2:C$117))
Entered with CTRL + SHIFT + ENTER

However, it would be easier (& better) to use a Pivot Table with COUNTRY as ROW Field and VARIABLE as DATA Field set to STDEV (rather than SUM).

**laurafv** · 03-02-2009, 06:12 PM

hi,

thanks a lot but the array doesn't seem to work.. I need the st deviation for every single country in the period 1980-2008, not the overall st deviation for all the countries... do you think this can be done with a pivot table or with an array?

**DonkeyOte** · 03-02-2009, 06:14 PM

You will need to dispense with your Merged cells - using Merged Cells is a no-no on so many levels I woudnt know where to begin.

The formula provided will give you the STDEV for Argentina if entered correctly... ie the country as specified in A2...

To reiterate, use a Pivot Table.