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

    Array Formula Assistance

    I've got a worksheet that i'm trying to calculate values on. Column D contains either the text "Incoming" or "Outgoing" Representing the type of call, (made or recieved.) Then i've got Column AJ, which contains the call duration.

    Column D will only be populated if a call is taken. Each row designates one call. What I need is the average call time for Incoming, and Outgoing calls. (The result of adding all call times if the call was incoming, and dividing that by the total number of incoming calls.) How can I do this in an array?

    Thanks for any help you can provide!
    <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
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array Formula Assistance

    Something like this:

    <pre>=SUM((D1100="Incomming")*AJ1:A100)/SUM((D1100="Incomming")*1)
    </pre>


    Replace "Incomming" with "Outgoing" for that average.
    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array Formula Assistance

    =SUMIF(D1100,"=Incoming",AJ1:AJ100)/COUNTIF(D1100,"=Incoming")

    If you want an array formula:

    {=SUM(IF(D1100="Incoming",1,0)*(AJ1:AJ100))/SUM(IF(D1100="Incoming",1,0))}

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

    Re: Array Formula Assistance

    Brilliant!

    Thanks!
    <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

Posting Permissions

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