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

    Understanding Arrays

    Greetings!

    I know I need to create an array formula, but i'm having some problems understanding the syntax.

    I have a sheet, we'll call it sheet one. Sheet 1 has a series of ranges which represent call-center statistics. Sheet 2 is a culmination of the data on sheet one.

    Sheet 1 has a column (A) which represents Firm/Client. As we only deal with either a firm or a client respectively, the properties of this column have been set to be either True, or False.

    I have setup Column as the date and time of the call, which is a mandatory field. So what I need, is a formula which will count any instance of "FALSE" or "TRUE" In column A on Sheet1, only if there is a value for date/time in Column C.

    Any ideas?! )

    Thanks!

    Drk.
    <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
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Understanding Arrays

    Hi Drk,
    Not sure if you wanted to count those with false, those with true or those with either so:
    To count those with false enter:
    =SUM((A1:A6=FALSE)*(C1:C6<>"")) as an array by typing it in then pressing Control-Shift-Enter. To count true, just replace FALSE with TRUE! To count those with either (I'm assuming the other possibility is nothing in there) you'd use:
    =SUM((A1:A6<>"")*(C1:C6<>"")) with Ctrl-Shift-Enter.
    You can change the ranges as necessary.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Understanding Arrays

    Looks like that'll work.. Thanks again Rory! [img]/w3timages/icons/grin.gif[/img]
    <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

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Understanding Arrays

    My pleasure [img]/w3timages/icons/grin.gif[/img]. Did you sort out that Range.Find problem? It occurs to me you could replace it with a For Each ...Next loop checking the Fomula of each cell as you go if the find didn't work.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Understanding Arrays

    Yeah, I sorted it out, it is still intermittent, so i'm not sure what's causing the problem, but i've added a 'goto' feature to get around that for now.. btw Rory, Thanks millions for all your help!

    I've got another one which I think *may* apply,

    I have 68 rows in a sheet, one column in these rows is 'time'. I want to return the number of times which occur in the 8 o'clock hour, 9 o'clock hour, 10 o'clock.... etc. any ideas?
    <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

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Understanding Arrays

    Hi Drk,
    You could use:
    =SUM(IF(HOUR(A1:A68)=8,1,0)) entered as an array (i.e. Ctrl-Shift-Enter) changing the hour bit from 8 to whatever you need (and the range as necessary)
    Always glad to help! [img]/w3timages/icons/grin.gif[/img]
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Understanding Arrays

    Groovey.. How does it know the difference between 1am and 1pm? Thanks again!

    Drk.
    <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

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Understanding Arrays

    I'm assuming that the am/pm bit was specified on entry - Excel will translate 8pm as 20:00 for the purposes of its HOUR() calculation.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Understanding Arrays

    Ahh, so military time should be used to define the 'hours'.. Thanks Rory! [img]/w3timages/icons/smile.gif[/img]
    <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
  •