Pivot Table vs. Formulas
 

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

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" wrote:

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

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

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.

"Eric" wrote:

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