Arrays? Multiple Criteria in Formulas (2003 sp2)

Topic

New Reply

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?

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

If you move the data field of the pivot table into the column area it looks exactly like the table.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote