Results 1 to 2 of 2
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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>
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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