Hi,
I have two worksheets and I am trying to populate an 'Overview Summary' table from information gained in the first worksheet. The info is there, I'm just having trouble with the right formula's etc.
Any information will be gratefully received.
Regards,
Chris
Last edited by crafty carper; 02-22-2011 at 05:34 AM. Reason: Problem Solved
Hi crafty carper,
See if the attached with a Pivot Table isn't close to what you want.
I'd hide the columns that aren't needed to make it look better.
One test is worth a thousand opinions.
see attachment, hope it helps
Hi Marvin,
Yes, that's close to what I am after.
Would it be possible to work out how compliant each Team is, considering that the maximum number of points possible is 882 per inspection.
Also, would it be possible to identify how many inspections each Team have had, given that there could be more than one entry per inspection date.
Many thanks for your help.
Regards,
Chris
That's just great, I'm really impressed.
Very many thanks.
Chris
Hi,
Isn't the number of inspections the Total Count of Pts2?
Isn't the Total Sum of Pts2 the number of points / 881 (I don't get 882 in your total).
Pivot tables allow lots of ways to look at numbers, Counts, Sums, % of Column Total which is how I got my answer.
Hi Marvin,
882 is the total number of points you could score, if you were non-compliant on every question on the inspection form (not included). Therefore, if a Team had one Q1 (10 points), two Q2's (10 points) and three Q3's (6 points), that would add up to 26 points.
882 - 26 = 856
856 / 882 x 100 = 97.05%
Have I explained that correctly?
The only other problem I can see, is obtaining the number of inspections carried out, because there is more than one entry per inspection date in most cases.
Any further help is greatly appreciated.
Regards,
Chris
Hi Chris,
In pivot tables there are Calculated Fields that will do what you want (I believe). If not you could do a column just to the right of the pivot table and set it equal to 882 - Total / 882.
In your profile, you don't list the verson of Excel you are using 2010, 2007, 2003, Mac or before so I don't know if you have the feature in your version.
See http://www.contextures.com/excel-piv...ted-field.html or
http://www.databison.com/index.php/c...a-pivot-table/ or
http://www.ozgrid.com/Excel/pivot-calculated-fields.htm
Hi,
Currently it is 2003.
Regards,
Chris
check attachment, now inspections are counted by unique date and team
Many thanks, you have been a great help.
Regards,
Chris
If you are satisfied with the outcome and your problem is solved please mark the thread as solved (see Forum rules on "How to?" guestions)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks