Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table - Multiple consolidation Range | Excel Worksheet Functions | |||
Pivot table, dynamic data formula | Excel Discussion (Misc queries) | |||
Pivot Table Problems | Excel Discussion (Misc queries) | |||
formulas referencing pivot table cells | Excel Worksheet Functions | |||
Pivot table, how do you exclude counting cells with formulas as a | Excel Worksheet Functions |