Pivot Table vs. Formulas

[Eric]

I have a database list that looks something like the following:

Outlet Defect Work Type Complete
------ ------ --------- -------
BOH ... New Project N
Bus Svc ... FF&E N
Front ... Punch Y
Site ... Punch Y
Bus Svc ... Punch Y

There's less than 400 items in the list currently, and likely will not
grow to more than 2,000 or so defects.

For one analysis, I want to summarize data as follows:

Outlet #Items #Complete %Complete
------ ------ --------- ---------
x 12 6 50%
y 30 6 20%
------ ------ --------- ---------
Totals 42 12 29%

I am doing this by using:
1) the advanced filter to extract a unique list of Outlets
2) =COUNTIF(valColOutlet,K13) to get the #Items by Outlet
3) =SUMPRODUCT(--(valColOutlet=K13),--(valColComplete="Y")) for
#Complete
4) easy formulas to get the rest

I'd like to use a pivot table so I could page by work type, drill into
the data, etc. But it generates something like:

Complete
N Y Grand Total
------ ------ --------- ---------
x 6 6 12
y 24 6 30
------ ------ --------- ---------
Totals 30 12 42

So I guess I'm really asking if there is a way to show Complete = "Y"
after the Total, and then add a calulation for the completion
percentage the way I want to.

Thanks, Eric

 

[Guest]

I hope I understand your question.

If Outlet is in Column A, add a column heading for E (e.g., "% Complete").

Try this formula =

=SUM(($A$1:$A2=A2)*($D$1:$D2="Y"))/COUNTIF(A:A,A2)
NOTE: This is an array formula, so you probably need to hit CTRL+SHIFT+ENTER
when entering the formula. You will also have to copy down the formula as
you add new records.

When I replicated (and expanded) your data, I got the following output:

Outlet Defect Work Type Complete % Complete
BOH New Project N 0.00%
Bus Svc FF&E N 0.00%
Front Punch Y 33.33%
Site Punch Y 33.33%
Bus Svc Punch Y 16.67%
BOH New Project Y 33.33%
Bus Svc FF&E N 16.67%
Front Punch N 33.33%
Site Punch Y 66.67%
Bus Svc Punch Y 33.33%
BOH New Project Y 66.67%
Bus Svc FF&E N 33.33%
Front Punch N 33.33%
Site Punch Y 100.00%
Bus Svc Punch Y 50.00%

I don't know if this helps and/or is what you're looking for or not.

Adam.

 

[Eric]

No; what I'm looking for is a way to get a oivot table to display the
output I can already get from using formulas.

Thanks, Eric

 

[Guest]

Oh. Sorry. Check out the options for your Data when in the Layout section
of creating the Pivot table. Under "Count" you can choose "% of Total."

I hope that helps. Sorry once again if it doesn't.

Adam.