PDA

View Full Version : [SOLVED] Function average using VBA



frade
05-08-2005, 08:23 AM
Hello :hi:

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
05-08-2005, 08:47 AM
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
05-08-2005, 09:10 AM
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)"? :help


Fran?ois

frade
05-08-2005, 09:12 AM
column F by the way

Bob Phillips
05-08-2005, 09:23 AM
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
05-09-2005, 06:35 AM
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
05-09-2005, 06:58 AM
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
05-09-2005, 07:05 AM
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
05-09-2005, 07:19 AM
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
05-09-2005, 08:13 AM
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
05-09-2005, 08:16 AM
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
05-09-2005, 08:34 AM
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
05-09-2005, 09:09 AM
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"