Chart For Showing Planned Vs Actual

[chullan88]

Hi,

Last week I received a pivot table prepared by forum member ChemistB in reply to my query which shows the planned vs actual boxes over time period.
I have attached the sample file where the rows shows the planned nos and the columns actual.
For Eg. 24 boxes were planned in february of which 8 were done in March and 16 in April.
This table is not so easy to understand for 1st timers,and I would like to know if anyone have in mind a suitable chart by which I can show this data pictorially.
Any help would be appreciated...


Thanks

[Beamernsw]

Hi, I must admit that as a "1st timer" myself,I can't really understand it either

I don't suppose something as simple as this addition to your sheet would work?




Edit: If that simple list of jobs/month works, I also added a couple of sheets to show how you could list all sections and a total on the same sheet....never know, may be helpful...or not

[chullan88]

Hi, I must admit that as a "1st timer" myself,I can't really understand it either

I don't suppose something as simple as this addition to your sheet would work?




Edit: If that simple list of jobs/month works, I also added a couple of sheets to show how you could list all sections and a total on the same sheet....never know, may be helpful...or not

Yes of course it helps....!!
I am planning to integrate all these to a dashboard.
However could you explain the formulas used?

Thanks!!

[Beamernsw]

Sure, well looking at Cell D6 of the "Horizontal Dates" worksheet the formula is:-
=COUNTIFS(DATA!$C:$C,">="&D$3,DATA!$C:$C,"<="&EOMONTH(D$3,0),DATA!$B:$B,$B6)

That formula counts the number of rows where the data matches all three IF's from the COUNTIFS function.
The syntax for countifs is:- COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], [criteria_range3, criteria3]…)

The three criteria that is being sorted is as follows:-
Criteria 1: DATA!$C:$C,">="&D$3 … This will include all rows where the date in column C is > or = D3 (01 Feb 2015)

Criteria 2: DATA!$C:$C,"<="&EOMONTH(D$3,0) … This will narrow those chosen rows to only include rows where the date in column C is < or = End Of Month of D3 (28 Feb 2015)

Criteria 3: DATA!$B:$B,$B6 … This further narrows the results again by only including rows that have B6 (Sector 01) in column B


I hope that explains it fairly well for you