1. ## Variables in Formulas

I know the following formula can be used to calculate the average call duration for all outgoing calls in my Excel Database. My question is, how can I base this formula on what the user selects?

=SUM(IF(B1:B501="Outgoing",1,0)*(AL1:AL501))/SUM(IF(B1:B501="Outgoing",1,0))

I will have a combo or list box with possible criteria, so I can make the formula meet the request:

=SUM(IF(B1:B501="**VARIABLE**",1,0)*(AL1:AL501))/SUM(IF(B1:B501="**VARIABLE**",1,0))

Doing this is pretty simple really:
Dim s as Variant
<font color=448800>'s = selected criteria</font color=448800>
Range("A" & i).value = "=SUM(IF(B1:B501=" & s & ",1,0)*(AL1:AL501))/SUM(IF(B1:B501=" & s & "Outgoing",1,0))"
<font color=448800>How can I add the Quotes to a VBA Reference when defining Cell Contents?? How can I make this an Array Formula?</font color=448800>

A few questions are above... If I create an Array Formula in Excel, however, I must press CTRL + SHIFT + ENTER to 'activate' it. How can I replicate this behavior in VBA?

Any help would be Greatly Appreciated! , TYIA!! <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

2. ## Re: Variables in Formulas

Try this:

Sub test()
Dim i As Long
Dim s As Variant
i = 5
s = 10
ActiveSheet.Range("A" & i).FormulaArray = "=SUM(IF(B1:B501=""" & s & """,1,0)*(AL1:AL501))/SUM(IF(B1:B501=""" & s & "Outgoing"",1,0))"

End Sub

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•