PDA

View Full Version : Sleeper: SQUARE of the difference from two columns



frade
05-10-2005, 08:29 AM
Hello,

Please have a look at my file. Click directly on the button "Copier_valeurs"
(sheet2) to calculate the average by REP (column G). Then I would like
to fill up the column P by calculating the square of this difference:
between the result and the average

Value of P2 = (E2-G2)*(E2-G2)
Value of P3 = (E3-G3)*(E3-G3)
Value of P4 = (E4-G4)*(E4-G4)
and so on..

Thanks a lot

Fran?ois

Bob Phillips
05-10-2005, 09:48 AM
Fran?ois,



Private Sub calculations()
Dim sDays As String
Dim sDilutions As String
Dim sResults As String
Dim STANDARD_DEVIATION_BY_DAY As String
Dim STANDARD_DEVIATION_BETWEEN_DAY As String
Dim iLastRow As Long
Dim i As Long
Dim sFormula As String
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
sDays = "B2:B" & iLastRow
sDilutions = "C2:C" & iLastRow
sResults = "E2:E" & 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 = "SQRT(SUM(IF((" & sDays & "=$B" & i & ")," & STANDARD_DEVIATION_BY_DAY & ")))"
Cells(i, "J").Value = Evaluate(sFormula)
sFormula = "SQRT(SUM(IF((" & sDays & "=$B" & i & ")," & STANDARD_DEVIATION_BETWEEN_DAY & ")))"
Cells(i, "L").Value = Evaluate(sFormula)
sFormula = "((E" & i & "-G" & i & ")^2)"
Cells(i, "P").Value = Evaluate(sFormula)
Next i
End Sub