Stdev

[UpTheArgyle]

Hi i have 49 row of data (7 weeks) I want to switch on filter to just select all mondays, and then do a STDEV calculation on just those remaining 7 bits of data.
Trouble is it still looks at all 49 =STDEV(D2:D93)

If i manually type this it works : =STDEV(D93,D86,D79,D72,D65,D58,D51,D44,D37,D30,D23,D16,D9,D2)

Is there a quicker to to tell excel to only look at those cells that are visible?

 

[Sektor]

Excel 2010:

=AGGREGATE(8;5;D2:D93)

The "5" argument tells function to ignore hidden rows.

 

[UpTheArgyle]

But i was after STDEV, which cant be the same as AGGREGATE?

Also what is the "8" for in =AGGREGATE(8;5;D2:D93)


Thanks

 

[Sektor]

"7" is STDEV.S function.
"8" is STDEV.P function.

STDEV.S and STDEV.P replace STDEV in Excel 2010.
What Office do you have? Watch in Excel help for AGGREGATE.

 

[UpTheArgyle]

2007 here

 

[Sektor]

Bad luck. Resort to VBA. Here's UDF. Use it like Excel's function:

Function StandardDev(rng As Range) As Double
    StandardDev = WorksheetFunction.StDev(rng.SpecialCells(xlCellTypeVisible))
End Function

Type in E1:

=StandardDev(D2:D93)

 

[pgc01]

Hi

For old versions of excel you can

. use Subtotal()

. or you can test if the value refers to a Monday (in this case you don't even have to filter). This is usually the case when you have a date in a column and the corresponding value in another

If you want to implement any of this solutions and have problems with the formulas post back with details