Function average using VBA

[frade]

Hello

I would like to calculate automatically and using VBA the average
of my results. Please have a look at my file to see what I really
would like to create.


Thanks a lot!

Fran?ois

[Bob Phillips]

Here is a macro to do it.

Sub CalcAverage()
Dim sDays As String
Dim sDilutions As String
Dim sResults 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
For i = 2 To iLastRow
sFormula = "AVERAGE(IF((" & sDays & "=$B" & i & ")*(" & _
sDilutions & "=$C" & i & ")," & sResults & "))"
Cells(i, "G").Value = Evaluate(sFormula)
Next i
End Sub

[frade]

Ok, thanks a lot for your help!! The macro works without problem

But what can I do to create the macro for the other column (E)"?


Fran?ois

[frade]

column F by the way

[Bob Phillips]

But what can I do to create the macro for the other column (E)"?

Sorry Fran?ois,

Didn't realise you wanted both.

Here is an amended version.

Sub CalcAverage()
Dim sDays As String
Dim sDilutions As String
Dim sResults 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
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)
Next i
End Sub

[frade]

Ok, Thanks a lot. It works without problem.

But what can I do to replace Average by SUM/value of a number in a cell
or calculate SQRT of SUM/Value of a number in a cell.

Something like this

sFormula = "SQRT(SUM/number)(IF((" & sDays & "=$B" & i & ")*(" & _ 
        sDilutions & "=$C" & i & ")," & sResults & "))"

I would like to calcule the square root of the sum divide by a number..


Best regards,

Fran?ois

[Bob Phillips]

But what can I do to replace Average by SUM/value of a number in a cell or calculate SQRT of SUM/Value of a number in a cell.

I would like to calcule the square root of the sum divide by a number..

Fran?ois,

All I have done is to plug a worksheet formula into VBA, so just adjust that VBA as you would a worksheet formula.

I also created a formula string, which helps, so change

sFormula = "AVERAGE(IF((" & sDays & "=$B" & i & ")," & sResults & "))"

to say

sFormula = "SUM(IF((" & sDays & "=$B" & i & ")," & sResults & "))"

Just ensure that you maintain correct syntax.

[frade]

Ok, but I would like to obtain the SUM divided by a value


Something like this?

sFormula = "SUM/value(IF((" & sDays & "=$B" & i & ")," & sResults & "))" 

in place of just SUM

Thanks

[Regouin]

you can just write the formula as you would in excel only everything that is not a VBA variable just be between " ". You combine 2 parts with the & symbol (see the example you gave). Tricky part is when you are using an Excel that is in a different language then english. VBA only handles english formulas (so stick to these) and uses commas instead of ; . I am using a dutch Excel and took me a while to figure it all out.

HTH

Frank

[Bob Phillips]

Tricky part is when you are using an Excel that is in a different language then english. VBA only handles english formulas (so stick to these) and uses commas instead of ; . I am using a dutch Excel and took me a while to figure it all out.

Frank,

I believe that if you put the local language formula ina worksheet cell, and then use
?Activecell.Formula (or maybe (?Activecell.FormulaLocal)
in the immediate window in the VB IDE, then you will see the English formula.

Can't test it myself, I have other language versions of Excel, but never installed them.

[Bob Phillips]

Ok, but I would like to obtain the SUM divided by a value


Something like this?

sFormula = "SUM/value(IF((" & sDays & "=$B" & i & ")," & sResults & "))" 

in place of just SUM

No, but it depends upon where the value is. If it is a VBA variable, then use
something like

sFormula = "SUM(IF((" & sDays & "=$B" & i & ")," & sResults & "))" 
Cells(i, "F").Value = Evaluate(sFormula)/myVal

If you want to divide by another cell, then something like

sFormula = "SUM(IF((" & sDays & "=$B" & i & ")," & sResults & "))/$H$10" 
Cells(i, "F").Value = Evaluate(sFormula

[frade]

Thanks a lot xld!

If my value is located on the page "sheet1", what can I do?

sFormula = "SUM(IF((" & sDays & "=$B" & i & ")," & sResults & "))/Sheet1.$H$10" 
Cells(i, "F").Value = Evaluate(sFormula )

or something like that?

Fran?ois

[Bob Phillips]

If my value is located on the page "sheet1", what can I do?

 
sFormula = "SUM(IF((" & sDays & "=$B" & i & ")," & sResults & "))/Sheet1.$H$10" 
Cells(i, "F").Value = Evaluate(sFormula )

Almost.

sFormula = "SUM(IF((" & sDays & "=$B" & i & ")," & sResults & "))/Sheet1!$H$10"