Excel 2007 : Two formula in Pivot Table column

[Jumper1]

I hope I can sufficiently describe my problem so someone can actually help. On the Data worksheet for each ticket in column B there is a Length of Outage in column E. For each T# there is only one value in column E but there could be multiple Units, column F, or Sites, column D. On the Summary worksheet all works well in my default view of the pivot table. The Length of Outage is correct for each row and Sums correctly for the Grand Total. The problem arises when you look at the data in another way. For example if you select the Unit drop down and select just Assembly the Length of Outage is missing for some of the Tickets. If I go back to the Data worksheet and add a value in column E for each row, the Pivot Table will add the values which is incorrect. Obviously the Grand Total will be incorrect also. So my dilemma is how to have the Pivot Table display a noncumulative value in the Length of Outage column, while still having the Grand Total be the Sum of all the Length of Outage displayed.

I'm open to any suggestions and would be happy to provide additional details if needed.

[MarvinP]

Hi Jumper1 and welcome to the forum,
The quick answer, now that see I don't understand the problem, is to set the "Length of Outage" to Max instead of Sum.

It would help in your discription if you could tell us what the correct answer is.

[Jumper1]

[

It would help in your discription if you could tell us what the correct answer is.

Good point, the correct answer in the pivot table would be for the Length of Outage column to have a value, no matter what set of drop down filters you have selected, AND for the Grand Total of the Length of Outage column to sum those values. I have reposted the example workbook to better explain the problem. In the Gran Total the correct answer should be the sum of the above values, 4:30, not the Max. You can now select any combination of filters and the Length of Outage for each ticket is correct, but the Grand Total is incorrect, it needs to be the sum not the max.

[MarvinP]

Does this do it for you?

[Jumper1]

Unfortunately no, the problem is the Length of Outage column is adding the values from the Data worksheet and that metric is not cumulative. So, for example, T# 19291386 the Length of Outage is 0:40, or from 1:00pm to 1:40pm. It does not matter how many units or sites were affected by the outage, it is still only 0:40 minutes in duration. So in the pivot table no matter how you filter the data, T# 19261386 should always display 0:40. And the Grand Total should always sum the Length of Outage column.

What else can I provide to help in this process?

[MarvinP]

Ok - I'm blocking some light shining through my ears.

See if Min or Running Totals in the Pivot are closer. I'm thinking Min is it, only if you have it on all records.

[Jumper1]

As in life I feel we have come full circle. The Min solution produces the same results as the Max solution we started with. While either will provide the correct answer for each Ticket #/record/row, (0:40, 1:10, 0:30…), neither has the correct result in the Grand Total cell. In this case the Grand Total cell is displaying the Min of the Length of Outage column when it should be the sum of the column.
Perhaps I’m focusing on too narrow of a potential solution. Is there a better way to setup the Data worksheet so as to provide desired results on the Pivot Table? I am not a Pivot Table expert and am wide open to suggestions.

[MarvinP]

I think you are correct that a reevaluation of the data is in order.

I'm remembering my Relational Database Design classes and trying to apply them to your data. I think you breaking one of those fundamental rules. More specifically the Length of Outage (LOO) is listed in each row next to the Unit. This connect is what is wrong. I'd create two different tables.

1. Date, Ticket#, Issue, State, LOO.
2. Ticket#, Unit, Agents, Hours

Your current method is like a Invoice where you have the total of the Invoice next to each item on the invoice. You need to separate the Units (items) on the invoice from the LOO (total).

I hope this helps.

[Jumper1]

OK, I understand the basic premise. Using your suggestion, let me work on a redesign. Ultimately I need a single pivot table for the users of this table, but the two table idea may be one of the steps to get there. If you have any other ideas, please share. I have other commitments I need to tend to so it may be a few days before I make any progress. Thank you for your tenacity, I will keep you informed.

[Jumper1]

I couldn’t put it down. I’m exploring your idea of two tables and have put together a crude demo. Ideally I would want to combine the data from these two tables into one table for my users. Can a pivot table use another pivot table as its data source?
As an alternative, if two tables are the only way to go, then the LOO table on the right should be tied to the bigger table on the left in such a way as to display the same Ticket# in each automatically. For example, if you select filters in the bigger table on the left so that only Ticket # 19291386 and 19365928 were displayed, then those same tickets should be the only ones displayed on the LOO table on the right.
I’m hoping we’re on the right track.

[MarvinP]

Yep - Now you have separated your Pivot tables.
I like it better but need to know what your common question is to make is slick.
Do you punch in a Tx number and want to see it or Date or ??

[Jumper1]

There is no common question. I setup this data in a Pivot Table because each user has their unique data needs. For example, a manager may want to know how many issues occurred in New York, or just in the month of January. Other users may want to see different subsets of the data. Regardless of how the data is filtered, all users will need to see Total Agents Impacted, Total Prod Hours and Length of Outage. I hope this answers your question.

[Jumper1]

I am unable to combine the two pivot tables into anything useful. The resultant table just has columns with no data, Count of Page 2, Count of Page 3, Total Count of Page 2, Total Count of Page 3...Unfortunately using an Access database is not a viable option. There are end users that are doing a copy/paste into their own spreadsheet. I need to find an Excel solution. Anyone have any ideas?

[MarvinP]

What would be useful? What should your data show you, exactly? Without an endpoint you will never get there!

[Jumper1]

The endpoint is:
1. An Excel solution
2. If using a pivot table
A. The Length of Outage column will always have a value
B. Any combination of Site or Unit will have a value in the Length of Outage column
C. The correct values for the LOO column are:
a. T#19291386 = 0:40
b. T# 19354991 = 1:10
c. T# 19365847 = 0:30
d. T#19365928 = 2:10
D. The Grand Total of the LOO column will be the Sum of the column, based on any filters selected (“B” above)

So as an example, if nothing is filtered the correct Grand Total in the LOO column is 4:30. If the Site filter is used and New Mexico is selected, three tickets will be visible and the LOO values are:
T# LOO
19354991 1:10
19365847 0:30
19365928 2:10
The correct Grand Total should be 3:50

I have a very clear endpoint in mind. I apologize if I am not explaining that goal in a clear manner.
Thank you for your patience and help.

[MarvinP]

In using the pivot, we see that filtering by unit while all other filters are ALL, gives a LOO of blanks. This doesn't fit 2A from above.

This means that the Data table doesn't give what you want. You need to structure the data to give back what you require. There is this conflict of having blanks (you don't want these) and if you have repeated data your total is too much.

Pivot tables are cool but sometimes the data structure doesn't work well with what you want. I've been in these places before and I find it best to do a single problem, get an answer and check it. Then see if a second problem fits just like the first. I think you are keeping your fingers crossed that all 4 filters can appear on the top of a single PT. I believe you will need either 2 PTs or a restructure of your combined data.

[Jumper1]

Taking your advice, MarvinP, I’ve abandoned the single PT approach and have a new column (the “D column) just for the LOO data. As you can see with a few IF statements and a VLOOKUP I can present the correct data no matter what filter is selected. And the SUM cell will grow or shrink along with the PT. Add some cell formatting and it resembles the PT. The challenge now is getting the correct SUM. I need a method that will avoid the circular reference. So if the Grand Total row is row 13, I need to SUM rows 9-12. If a different set of filters is selected and the Grand Total is now row 11, I need to SUM rows 9-10. Basically I need a statement that says SUM(D9: (Grand Total row-1)).

I’m hoping since this is more of a formula question as opposed to a design question it will be easier to solve. Also, not sure if this warrants a new thread, please advise.

[MarvinP]

Hi Jumper,

I've been feeling bad about your problem and thinking we could do better. Find the attached with what I think will make you happy. I think I found the answer for the total on the LOO. You just needed to do an Relative Ref one cell above the one you are on... At least I think this is what you want.

Hope this helps.

[Jumper1]

You nailed it, exactly what I need. Absolutely no reason to feel bad, I presented a difficult problem and presented it in a manner that took us down a dead end road, the single PT that would do it all. I’m grateful you were able to step back and see the big picture and force me to think of alternate solutions.

As it turns out I was about to post my own solution. It works fine but compared to yours, it is kludgy and needlessly complex. The SUMIF function is overkill in this situation. I like your solution much better. Thank you again for your help. I've attached my solution for your entertainment.