Sleeper : Function SQRT, SUM...

[frade]

Hi,

You can see a new file with this procedure on sheet2


Private Sub calculations()
Dim sDays As String
Dim sDilutions As String
Dim sResults As String
Dim sCountSum As String
Dim sID As String
Dim STANDARD_DEVIATION_BY_DAY As String
Dim STANDARD_DEVIATION_BETWEEN_DAY As String
Dim iLastRow As Long
Dim iLastRow2 As Long
Dim iLastRowOK As Long
Dim sR As String
Dim i As Long
Dim valB As Long
Dim val2B As Long
Dim sFormula As String
Dim NumDays As Long
iLastRow = Range("A65536").End(xlUp).Row
iLastRow2 = Range("H65536").End(xlUp).Row
iLastRowOK = Worksheets("Sheet2").Range("H" & iLastRow)
valB = Worksheets("Sheet1").Range("B1")
NumDays = Worksheets("Sheet1").Range("B3")
val2B = valB * 10
sID = "A2:B" & iLastRow
sDays = "B2:B" & iLastRow
sDilutions = "C2:C" & iLastRow
sResults = "E2:E" & iLastRow
sCountSum = "P2:P" & iLastRow
sR = "R2:R" & iLastRow
STANDARD_DEVIATION_BY_DAY = "I2:I" & iLastRow
STANDARD_DEVIATION_BETWEEN_DAY = "K2:K" & iLastRow
For i = 2 To iLastRow
sFormula = "AVERAGE(IF((" & sDays & "=$B" & i & ")," & sResults & "))"
Cells(i, "F").Value = Evaluate(sFormula)
sFormula = "AVERAGE(IF((" & sDays & "=$B" & i & ")*(" & _
sDilutions & "=$C" & i & ")," & sResults & "))"
Cells(i, "G").Value = Evaluate(sFormula)
sFormula = "((E" & i & "-G" & i & ")^2)"
Cells(i, "H").Value = Evaluate(sFormula)
Next i
End Sub


First, results (sheet2, column E) are entered manually by an user
I have only calculated 2 averages of my results and the square
of a difference
-> average by day : column F
-> average by num of repeat : column G
-> square of difference : column H

After having clicked on the button "copier_valeurs"
I would like also know 5 other values automatically

->STANDARD DEVIATION BY DAY (ALWAYS 10 DAYS)
->STANDARD DEVIATION BETWEEN DAY
->AVERAGE BETWEEN DAY
->CV BY DAY
->CV BETWEEN DAY

You can see an example of each formula (sheet2)

Thanks a lot!

Fran?ois