Excel Standart deviation - Formula optimization

[ItIsHardToProgram]

Hello every one,

I have the following formula in a cell:

=SQRROOT(1/L4*(if(L16>0,(L16-L5)^2,1))*(if(L17>0,(L17-L5)^2,1))*(if(L18>0,(L18-L5)^2,1))*(if(L19>0,(L19-L5)^2,1))*(if(L20>0,(L20-L5)^2,1)))

Is there a way that I could do this without havnig to copy every single darn cell, because that would be around 300 cells...

Thank you very much.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

Hi,

Copy 'n' paste.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

that is...

copy the cell with the formula.

select the destination range and paste.

Or even FASTER, select the cell with the formula.

double click the little box in the lower right-hand corner of the selection, and VOLA; assuming that your have data in adjacent cells to the LEFT.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

-> Is there a way that I could do this without havnig to copy every single darn cell

Do what?

Do you mean you want to populate that formula down a column (or across a row)?

If so, then check out Excel's help file for Fill data in worksheet cells.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[ItIsHardToProgram]

I guess you misunderstood my initial question.

I need to populate one single cell with that formula (for 300 lines) as it is a standart deviation for a population in wich I need to ignore the 0.

Either way I will manage this through VBA as I can't seem to find an easy solution with excel formula.

Thanks all

Julien ~

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[GlennUK]

So why not an array ( entered using Ctrl-Shift-Enter ) of :

=STDEV(IF(L16:L300>0,L16:L300))

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[combo]

You could combine SUBTOTAL with argument you need and autofilter.

combo

 

[ItIsHardToProgram]

I guess I did not know you could increment formula's like that, either way I have found a workaround, everything is implemented in my VBA:

Sub DynamicSdev()

Dim dSdev As Double

For Each pi In Worksheets("Total").PivotTables("TableauTotal").PivotFields("Surname             ").PivotItems
    pi.Visible = True
Next


For iColumn = 13 To 14
iOffset = 0
For iCell = 16 To iMaxCell
    If Worksheets("Total").Cells(iCell, iColumn).Value > 0 Then
           iOffset = iOffset + 1
           dSdev = dSdev + (Worksheets("Total").Cells(iCell, iColumn).Value - Worksheets("Total").Cells(5, iColumn + 6)) ^ 2
           dMoyenne = dMoyenne + Worksheets("Total").Cells(iCell, iColumn).Value
    End If
Next iCell
    
    Worksheets("Total").Cells(4, iColumn).Value = iOffset
    
    dMoyenne = dMoyenne / iOffset
    dSdev = Sqr(1 / iOffset * dSdev)
    
    Worksheets("Total").Cells(5, iColumn).Value = Worksheets("Total").Cells(5, iColumn + 6).Value
    Worksheets("Total").Cells(6, iColumn).Value = dSdev
    
    Worksheets("Total").Cells(8, iColumn).Value = dMoyenne + 2 * dSdev

Next iColumn
End Sub

For any one interested...

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.