Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Arrays? Multiple Criteria in Formulas (2003 sp2)

    I know this has to be easier than I am trying to make this. I have several formulas that I need to build that need to be based off a month field. The source data comes from a query to an Access database in which the criteria is subjective. The user, when opening the file is prompted to identify the beginning date and the ending date. This allows them to run this 3-month report on a rolling-basis. I also want the graphs (to be built) and the pivots to update automatically. With all that being said, I dumping times of which certain things are not meeting the deadline established. So for instance, I am pulling data that shows me that between 9/1/07 and 11/30/07, I have all of these items that did not meet the deadline. Of those that did not meet the deadline, by month, I want to count the number, sum the time, average the time, and find the max time. My pivot table doesn't look clean. It does what I want it to do, but I was hoping to do it all with formulas. Can I use arrays here? Am I way off base?
    Attached Files Attached Files
    thanks
    christine

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Arrays? Multiple Criteria in Formulas (2003 sp2)

    It is not clear to me what you want those formulas to do. Could you explain what you are after? What numbers do you want, which of the current numbers are incorrect and what numbers do you expect and why

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Arrays? Multiple Criteria in Formulas (2003 sp2)

    Sorry, I thought I might have been a little jumbled. That's what happens when you try to work on something the day before you go on vacation. On sheet 1, note the pivot table that begins in cell A52. I want to get this same information for all 8 extracts shown in columns C through J. When I try to pull it all together in a pivot, it doesn't come up in a nice clean display. I was thinking that I could move over to using array formulas in a table, but that wasn't going so well either. Can you look at the pivot and tell me if I'm missing something. I kind of want it to look like the table I mocked up beginning in cell C35.


    MO Data Total
    9 Count of SEC> SLA 1
    Sum of SEC> SLA 0:02
    Average of SEC> SLA 0:02
    Max of SEC> SLA 0:02
    10 Count of SEC> SLA 2
    Sum of SEC> SLA 1:39
    Average of SEC> SLA 0:49
    Max of SEC> SLA 1:26
    Total Count of SEC> SLA 3
    Total Sum of SEC> SLA 1:42
    Total Average of SEC> SLA 0:34
    Total Max of SEC> SLA 1:26
    thanks
    christine

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Arrays? Multiple Criteria in Formulas (2003 sp2)

    If you move the data field of the pivot table into the column area it looks exactly like the table.
    Attached Images Attached Images
    Regards,
    Rudi

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Arrays? Multiple Criteria in Formulas (2003 sp2)

    If you are just trying to get the pivot to display those items in columns instead of rows:
    Hover over the gray box with "Data" (cursor turns from a plus to 4 expanding arrows)
    Press and hold the left mouse button
    Drag the box to the right (there is a little graphic with a blue highlight in the column) over the "total" until the graphic changes to a blue highlight in the row
    Let go of the button and it will be put into columns.

    [If you are after something else, again could you elaborate]
    Steve

  6. #6
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Arrays? Multiple Criteria in Formulas (2003 sp2)

    Aaawwwww, for the love of Christmas! I knew that. Sorry to be a bother and thanks.
    thanks
    christine

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Arrays? Multiple Criteria in Formulas (2003 sp2)

    If its any consolation, I know how you feel Christine...I have been there too - overlooking the simple things. The lounge tends to do that to one. <img src=/S/yep.gif border=0 alt=yep width=15 height=15>
    Regards,
    Rudi

Posting Permissions

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