Pivot Table : Planned and Actual Count Over Same Time Period

Hi,

ive been stuck on this pivot table from last 2 days...would appreciate any help...
I've a list of boxes with planned and actual dates..( There are 8 sectors and 300 boxes each..)
What I want to show on the pivot table is two rows for each sector: one row for total no of planned boxes and other for total no of actual boxes against Months in the column

In the attached file,I have managed to show planned boxes against time but cant find a away to show actuals over the same time period.

Please help...

Wow ChemistB,

That was a new level of insight to the data...
never thought of it that way....
could you explain how you did it??



Actually what I wanted to show was something like this...

Mar Apr May Jun Jul Aug

Planned 50 40 30 20 10 15
Actual 45 30 25 10 5 10
Cumulative Planned 50 90 120 140 150 165
Cumulative Actual 45 75 100 110 115 125
Variance 5 15 20 30 35 40


I thought of obtaining the first two rows from pivot,copying them as values and do the last three rows manually,but I guess its possible in pivots as calculated fieds(not sure about it)...

I have one more requirement actually....
I have datas for 8 sectors ( 300 boxes in each;boxes are of 4 types ).
So using conditional formatting,I would like to show the sector/box type with the highest delay...

I don't think that you can format a pivot table the way you want it. I could do it with formulas if that's acceptable? How are the 4 box types differentiated?


Yes formulas are acceptable..
The 4 box types are dry,wet,non typical and typical

Originally Posted by ChemistB

I created this dynamic named range for the box data
Box =DATA!$A$2:INDEX(DATA!$A:$A, MATCH("zzz",DATA!$A:$A ))
Then for the others, I used OFFSET like so
Planned = OFFSET(Box, 0, 3)

This makes the formulas modify themselves as the data expands (or contracts) and makes the formulas more clear.
First draft.
In C2 dragged right (out to Z)
=IF(TODAY()< MAX($B$2:B$2),"", EDATE(B2,1))

In B3 dragged right
=IF(LEN(B$2)>0, COUNTIFS(Sector, $A$1,Planned, ">="&B$2, Planned, "<"&EDATE(B$2,1)),"")

In B4 dragged right
=IF(LEN(B$2)>0, COUNTIFS(Sector, $A$1,Actual, ">="&B$2, Actual, "<"&EDATE(B$2,1)),"")

Similar type of formulas in rows 5 to 11. For the box types, I counted "Actual"
Questions?

Thanks for the effort ChemistB!!!
The formulas seem to be too complex for my level of Excel expertise..
I decided to stick to your old pivot table with a small tweak ( % values for Planned).I thought this would give a better picture.
and moreover,the data in the way I requested ( variance) has already been presented before,so would be of a lesser value to the management.
Thanks once more and I will get back once I get stuck again!!!!!!
Cheers!!!

Originally Posted by ChemistB

Yes, I think that's important. For example, looking at the way you set up the data, you planned 50 for march and did 45. that doesn't sound so bad. That's a good way to see if you are working to capacity. However, looking at my pivot table, the ones that were planned weren't done until April and May. This means you are constantly working on backlog. Data like this supports hiring temporary help (or approving overtime) to remove backlog.

Yes,you are right.
Actually this is data from a completed project and I'm assigned the task of submitting a detailed report of the delays.This clearly points out the backlog

Originally Posted by ChemistB

Okay, so we'll put them in a column in Data (or are they linked to the box ID somehow (i.e. if it has "G" then it's "wet")?

No they are not linked to the id