Simple Issue: Standard Deviation by Date range...

**niceguy21** · 06-18-2012, 01:19 PM

HelloPeople

I have a problem that would appear to be simple, but I am missing something:

A table listing of various websites (called siteIDs) has a range of dates and each date has associated figures (clicks and impressions).

What I would like to do is give a standard deviation calculation of impressions for each site ID, but each site ID has a random number of dates (rows) attributed to it- so no set formula.

I have a attached a sample set of data of 2 Site IDs, but bare in mind the full set is around 1000 rows so the manual SDEV formula I have used here would be too time consuming a process.

How do I set the row range for the STDEV always equal to the number of rows of the given SiteID.

Your thoughts,

Thanks,

**NBVC** · 06-18-2012, 01:30 PM

Add a helper column first...

say between current columns C and D..

So in new C2 enter formula:

=LOOKUP(REPT("z",255),B$3:B3)

copied down. You can hide this column.

Unmerge the results column (don't use merged cells for formulas that you want to copy down/across).. and enter formula:

=IF(B3<>"",STDEV(IF($C$3:$C$15=C3,$F$3:$F$15)),"")

adjust ranges to suit, and confirm with CTRL+SHIFT+ENTER not just ENTER and copied down.

**niceguy21** · 06-19-2012, 09:55 AM

OK, some of the uses of the uses functions here are quite advanced.. but I'm guessing they are standard templates than can be
tweaked for examples such as this??

I am not getting the right numbers out of this.. do you have a possible suggestions?

My working is attached.

Thanks,

**NBVC** · 06-19-2012, 01:19 PM

You are refererencing column C when you should be referencing column D...

=IF(B3<>"",STDEV(IF($D$3:$D$15=D3,$F$3:$F$15)),"")

confirmed with CTRL+SHIFT+ENTER not just ENTER.

and D3 should be:

=LOOKUP(REPT("z",255),B$3:B3)

copied down.