Sort Excel Pivot Table by Percentage of Count
up vote
1
down vote
favorite
I have source data showing timesheet approvals in the following format (for about 850 employees and 200 managers):
Employee Name Manager Name TS Approved?
Employee 1 Manager 1 No
Employee 2 Manager 2 Yes
Employee 3 Manager 3 Yes
Employee 4 Manager 1 No
Employee 5 Manager 3 No
I've made a pivot table as follows (The % unapproved is just a formula I have next to the pivot table):
Count TS Approved?
Manager Name No Yes Total % Unapproved
Manager 1 11 11 100%
Manager 2 6 10 16 38%
Manager 3 7 18 25 28%
Manager 4 5 8 13 38%
Manager 5 5 4 9 56%
Manager 6 3 3 0%
Manager 7 5 5 100%
I need to sort to get the top 5 worst approvers by count - but only 5. My issues are:
- If I use the pivot table 'Top 10' on the 'No' column, it'll show 6 values as it doesn't differentiate between the three 5s
- I tried adding the percentage so I could sort Largest-Smallest on %, then Largest-Smallest on count, then just take the top 5 manully - since 5/5 (100%) unapproved is worse than 5/8 (38%) - but don't know how to sort on %.
- If I add it as a formula outwith the pivot table (like above), Excel won't let me sort the pivot table based on those data. 'You cannot move part of a Pivot Table Report....'
- If I add the data to show as "% of Parent Row Total" in the table, it still only sorts on the count
Can anyone think how I can get it to do what I want, i.e.?
Count TS Approved?
Manager Name No Yes Total % Unapproved
Manager 1 11 11 100%
Manager 3 7 18 25 28%
Manager 2 6 10 16 38%
Manager 7 5 5 100%
Manager 5 5 4 9 56%
Manager 4 5 8 13 38%
Manager 6 3 3 0%
Note: I can do it easily enough using countifs rather that a pivot table, but ideally want the pivot table format if possible.
Thank you!
Louise
microsoft-excel microsoft-excel-2010 pivot-table sorting
add a comment |
up vote
1
down vote
favorite
I have source data showing timesheet approvals in the following format (for about 850 employees and 200 managers):
Employee Name Manager Name TS Approved?
Employee 1 Manager 1 No
Employee 2 Manager 2 Yes
Employee 3 Manager 3 Yes
Employee 4 Manager 1 No
Employee 5 Manager 3 No
I've made a pivot table as follows (The % unapproved is just a formula I have next to the pivot table):
Count TS Approved?
Manager Name No Yes Total % Unapproved
Manager 1 11 11 100%
Manager 2 6 10 16 38%
Manager 3 7 18 25 28%
Manager 4 5 8 13 38%
Manager 5 5 4 9 56%
Manager 6 3 3 0%
Manager 7 5 5 100%
I need to sort to get the top 5 worst approvers by count - but only 5. My issues are:
- If I use the pivot table 'Top 10' on the 'No' column, it'll show 6 values as it doesn't differentiate between the three 5s
- I tried adding the percentage so I could sort Largest-Smallest on %, then Largest-Smallest on count, then just take the top 5 manully - since 5/5 (100%) unapproved is worse than 5/8 (38%) - but don't know how to sort on %.
- If I add it as a formula outwith the pivot table (like above), Excel won't let me sort the pivot table based on those data. 'You cannot move part of a Pivot Table Report....'
- If I add the data to show as "% of Parent Row Total" in the table, it still only sorts on the count
Can anyone think how I can get it to do what I want, i.e.?
Count TS Approved?
Manager Name No Yes Total % Unapproved
Manager 1 11 11 100%
Manager 3 7 18 25 28%
Manager 2 6 10 16 38%
Manager 7 5 5 100%
Manager 5 5 4 9 56%
Manager 4 5 8 13 38%
Manager 6 3 3 0%
Note: I can do it easily enough using countifs rather that a pivot table, but ideally want the pivot table format if possible.
Thank you!
Louise
microsoft-excel microsoft-excel-2010 pivot-table sorting
I'm not following you exactly on what you are doing, so maybe this article can help you achieve what you want - Excel Pivot Table Filters - Top 10.
– CharlieRB
Apr 15 '16 at 13:19
Thanks Charlie - but I don't think Top 10 works. I want the 5 worst managers - only 5. Top 10 will return more than 5 values as it doesn't differentiate between the duplicated numbers (e.g. in the above I'd get 11, 7, 6, 5, 5, 5 rather than just 11, 7, 6, 5, 5). I basically want to sort highest to lowest on % Unapproved, then highest to lowest on "No" and chase the 5 worst people.
– Louise
Apr 18 '16 at 10:51
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
I have source data showing timesheet approvals in the following format (for about 850 employees and 200 managers):
Employee Name Manager Name TS Approved?
Employee 1 Manager 1 No
Employee 2 Manager 2 Yes
Employee 3 Manager 3 Yes
Employee 4 Manager 1 No
Employee 5 Manager 3 No
I've made a pivot table as follows (The % unapproved is just a formula I have next to the pivot table):
Count TS Approved?
Manager Name No Yes Total % Unapproved
Manager 1 11 11 100%
Manager 2 6 10 16 38%
Manager 3 7 18 25 28%
Manager 4 5 8 13 38%
Manager 5 5 4 9 56%
Manager 6 3 3 0%
Manager 7 5 5 100%
I need to sort to get the top 5 worst approvers by count - but only 5. My issues are:
- If I use the pivot table 'Top 10' on the 'No' column, it'll show 6 values as it doesn't differentiate between the three 5s
- I tried adding the percentage so I could sort Largest-Smallest on %, then Largest-Smallest on count, then just take the top 5 manully - since 5/5 (100%) unapproved is worse than 5/8 (38%) - but don't know how to sort on %.
- If I add it as a formula outwith the pivot table (like above), Excel won't let me sort the pivot table based on those data. 'You cannot move part of a Pivot Table Report....'
- If I add the data to show as "% of Parent Row Total" in the table, it still only sorts on the count
Can anyone think how I can get it to do what I want, i.e.?
Count TS Approved?
Manager Name No Yes Total % Unapproved
Manager 1 11 11 100%
Manager 3 7 18 25 28%
Manager 2 6 10 16 38%
Manager 7 5 5 100%
Manager 5 5 4 9 56%
Manager 4 5 8 13 38%
Manager 6 3 3 0%
Note: I can do it easily enough using countifs rather that a pivot table, but ideally want the pivot table format if possible.
Thank you!
Louise
microsoft-excel microsoft-excel-2010 pivot-table sorting
I have source data showing timesheet approvals in the following format (for about 850 employees and 200 managers):
Employee Name Manager Name TS Approved?
Employee 1 Manager 1 No
Employee 2 Manager 2 Yes
Employee 3 Manager 3 Yes
Employee 4 Manager 1 No
Employee 5 Manager 3 No
I've made a pivot table as follows (The % unapproved is just a formula I have next to the pivot table):
Count TS Approved?
Manager Name No Yes Total % Unapproved
Manager 1 11 11 100%
Manager 2 6 10 16 38%
Manager 3 7 18 25 28%
Manager 4 5 8 13 38%
Manager 5 5 4 9 56%
Manager 6 3 3 0%
Manager 7 5 5 100%
I need to sort to get the top 5 worst approvers by count - but only 5. My issues are:
- If I use the pivot table 'Top 10' on the 'No' column, it'll show 6 values as it doesn't differentiate between the three 5s
- I tried adding the percentage so I could sort Largest-Smallest on %, then Largest-Smallest on count, then just take the top 5 manully - since 5/5 (100%) unapproved is worse than 5/8 (38%) - but don't know how to sort on %.
- If I add it as a formula outwith the pivot table (like above), Excel won't let me sort the pivot table based on those data. 'You cannot move part of a Pivot Table Report....'
- If I add the data to show as "% of Parent Row Total" in the table, it still only sorts on the count
Can anyone think how I can get it to do what I want, i.e.?
Count TS Approved?
Manager Name No Yes Total % Unapproved
Manager 1 11 11 100%
Manager 3 7 18 25 28%
Manager 2 6 10 16 38%
Manager 7 5 5 100%
Manager 5 5 4 9 56%
Manager 4 5 8 13 38%
Manager 6 3 3 0%
Note: I can do it easily enough using countifs rather that a pivot table, but ideally want the pivot table format if possible.
Thank you!
Louise
microsoft-excel microsoft-excel-2010 pivot-table sorting
microsoft-excel microsoft-excel-2010 pivot-table sorting
asked Apr 15 '16 at 10:24
Louise
612
612
I'm not following you exactly on what you are doing, so maybe this article can help you achieve what you want - Excel Pivot Table Filters - Top 10.
– CharlieRB
Apr 15 '16 at 13:19
Thanks Charlie - but I don't think Top 10 works. I want the 5 worst managers - only 5. Top 10 will return more than 5 values as it doesn't differentiate between the duplicated numbers (e.g. in the above I'd get 11, 7, 6, 5, 5, 5 rather than just 11, 7, 6, 5, 5). I basically want to sort highest to lowest on % Unapproved, then highest to lowest on "No" and chase the 5 worst people.
– Louise
Apr 18 '16 at 10:51
add a comment |
I'm not following you exactly on what you are doing, so maybe this article can help you achieve what you want - Excel Pivot Table Filters - Top 10.
– CharlieRB
Apr 15 '16 at 13:19
Thanks Charlie - but I don't think Top 10 works. I want the 5 worst managers - only 5. Top 10 will return more than 5 values as it doesn't differentiate between the duplicated numbers (e.g. in the above I'd get 11, 7, 6, 5, 5, 5 rather than just 11, 7, 6, 5, 5). I basically want to sort highest to lowest on % Unapproved, then highest to lowest on "No" and chase the 5 worst people.
– Louise
Apr 18 '16 at 10:51
I'm not following you exactly on what you are doing, so maybe this article can help you achieve what you want - Excel Pivot Table Filters - Top 10.
– CharlieRB
Apr 15 '16 at 13:19
I'm not following you exactly on what you are doing, so maybe this article can help you achieve what you want - Excel Pivot Table Filters - Top 10.
– CharlieRB
Apr 15 '16 at 13:19
Thanks Charlie - but I don't think Top 10 works. I want the 5 worst managers - only 5. Top 10 will return more than 5 values as it doesn't differentiate between the duplicated numbers (e.g. in the above I'd get 11, 7, 6, 5, 5, 5 rather than just 11, 7, 6, 5, 5). I basically want to sort highest to lowest on % Unapproved, then highest to lowest on "No" and chase the 5 worst people.
– Louise
Apr 18 '16 at 10:51
Thanks Charlie - but I don't think Top 10 works. I want the 5 worst managers - only 5. Top 10 will return more than 5 values as it doesn't differentiate between the duplicated numbers (e.g. in the above I'd get 11, 7, 6, 5, 5, 5 rather than just 11, 7, 6, 5, 5). I basically want to sort highest to lowest on % Unapproved, then highest to lowest on "No" and chase the 5 worst people.
– Louise
Apr 18 '16 at 10:51
add a comment |
2 Answers
2
active
oldest
votes
up vote
0
down vote
Interesting challenge. Some of the issues include:
- Field calculations do not have enough flexibility to get what you need
- Although you can display numbers as % of total, and it appears you can sort on it - it really sorts on the underlying numbers.
I have a solution that makes use of Tables and Pivot Table. There may be a simpler solution available. The steps are (done in Excel 2016):
- Select inside your raw data. Select the "Insert" Ribbon and click on "Table"
- In your new table, insert a calculation for %NotApproved
- Select the "Table Tools" "Design" Ribbon and click "Summarize with Pivot Table"
- Construct a simple Pivot table with Manager Name as the rows and %NotApproved as the Values.
- Sort the Manager Names in Descending order by %NotApproved
Here is an example. The following is a snippet of 30 rows of "raw data" similar to described in your question ...
Select the "Insert" Ribbon and click on "Table" ...
You get better formatted data. Select D1, next the last column heading and type in "%No" - this creates a new column in the table with a new heading. In Cell D2, type in the following formula ...
=IF([@[TS Approved?]]="No",1,0)/COUNTIF([Manager Name],"="&[@[Manager Name]])*100
When you hit enter, it is automatically filled down in the table. This formula does:
IF([@[TS Approved?]]="No",1,0)
If the timesheet approved is "No", get a value of 1.
COUNTIF([Manager Name],"="&[@[Manager Name]])
Determines how many times the manager in this row appears in the table.- Result from 1 divided by the result from 2 times 100
The table now looks like this ...
Select "Table Tools" "Design" Ribbon, and click on Summarize with Pivot Table. Build the Pivot table to look like this ...
... and sort it ...
... to get this ...
Although it seems like a lot of steps to get set up, it's pretty easy to maintain the Table and this automatically keeps the Pivot Table maintained.
add a comment |
up vote
0
down vote
Not sure why, but I realized two things over breakfast this morning ...
- Using a Table is good, but perhaps just adds complication to the problem.
- Although you calculate your %Unapproved as the % of timesheets the manager is responsible for, you may want to calculate it as the % of all unapproved timesheets.
So I thought I'd post an alternative answer.
Beside your raw data, put a header %No
and this calculation below (and fill down).
=IF(C2="No",1,0)/COUNTIF($C$2:$C$31,"="&C2)*100
The formula calculates, if this timesheet is unapproved, the percentage of all unapproved timesheets it is.
Your raw data now looks like this ...
Build your pivot table and sort on %No.
If you still want %Unapproved to be % of timesheets the manager is responsible for, use this equation in Column D.
=IF(C2="No",1,0)/COUNTIF($B$2:$B$31,"="&B2)*100
Thanks for your feedback - I changed jobs not long after asking the question, and had to leave the spreadsheet behind! :( I'm not sure the above would have done it though...I needed to get the highest number of unapproved timesheets - then in the case of a tie, rank by percentage. If I understand your solution correctly, it'll put someone with 1 unapproved timesheets out of 1 (i.e. 100%) higher than someone with, 2 out of 5 (i.e. 20%). I would want the 2/5 to rank first - more physical non-approvals - then to rank ties on % (i.e. 2/5 vs 2/2 - 2/2 is higher due to the higher %). Thank you!
– Louise
Aug 25 '16 at 12:32
add a comment |
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
Interesting challenge. Some of the issues include:
- Field calculations do not have enough flexibility to get what you need
- Although you can display numbers as % of total, and it appears you can sort on it - it really sorts on the underlying numbers.
I have a solution that makes use of Tables and Pivot Table. There may be a simpler solution available. The steps are (done in Excel 2016):
- Select inside your raw data. Select the "Insert" Ribbon and click on "Table"
- In your new table, insert a calculation for %NotApproved
- Select the "Table Tools" "Design" Ribbon and click "Summarize with Pivot Table"
- Construct a simple Pivot table with Manager Name as the rows and %NotApproved as the Values.
- Sort the Manager Names in Descending order by %NotApproved
Here is an example. The following is a snippet of 30 rows of "raw data" similar to described in your question ...
Select the "Insert" Ribbon and click on "Table" ...
You get better formatted data. Select D1, next the last column heading and type in "%No" - this creates a new column in the table with a new heading. In Cell D2, type in the following formula ...
=IF([@[TS Approved?]]="No",1,0)/COUNTIF([Manager Name],"="&[@[Manager Name]])*100
When you hit enter, it is automatically filled down in the table. This formula does:
IF([@[TS Approved?]]="No",1,0)
If the timesheet approved is "No", get a value of 1.
COUNTIF([Manager Name],"="&[@[Manager Name]])
Determines how many times the manager in this row appears in the table.- Result from 1 divided by the result from 2 times 100
The table now looks like this ...
Select "Table Tools" "Design" Ribbon, and click on Summarize with Pivot Table. Build the Pivot table to look like this ...
... and sort it ...
... to get this ...
Although it seems like a lot of steps to get set up, it's pretty easy to maintain the Table and this automatically keeps the Pivot Table maintained.
add a comment |
up vote
0
down vote
Interesting challenge. Some of the issues include:
- Field calculations do not have enough flexibility to get what you need
- Although you can display numbers as % of total, and it appears you can sort on it - it really sorts on the underlying numbers.
I have a solution that makes use of Tables and Pivot Table. There may be a simpler solution available. The steps are (done in Excel 2016):
- Select inside your raw data. Select the "Insert" Ribbon and click on "Table"
- In your new table, insert a calculation for %NotApproved
- Select the "Table Tools" "Design" Ribbon and click "Summarize with Pivot Table"
- Construct a simple Pivot table with Manager Name as the rows and %NotApproved as the Values.
- Sort the Manager Names in Descending order by %NotApproved
Here is an example. The following is a snippet of 30 rows of "raw data" similar to described in your question ...
Select the "Insert" Ribbon and click on "Table" ...
You get better formatted data. Select D1, next the last column heading and type in "%No" - this creates a new column in the table with a new heading. In Cell D2, type in the following formula ...
=IF([@[TS Approved?]]="No",1,0)/COUNTIF([Manager Name],"="&[@[Manager Name]])*100
When you hit enter, it is automatically filled down in the table. This formula does:
IF([@[TS Approved?]]="No",1,0)
If the timesheet approved is "No", get a value of 1.
COUNTIF([Manager Name],"="&[@[Manager Name]])
Determines how many times the manager in this row appears in the table.- Result from 1 divided by the result from 2 times 100
The table now looks like this ...
Select "Table Tools" "Design" Ribbon, and click on Summarize with Pivot Table. Build the Pivot table to look like this ...
... and sort it ...
... to get this ...
Although it seems like a lot of steps to get set up, it's pretty easy to maintain the Table and this automatically keeps the Pivot Table maintained.
add a comment |
up vote
0
down vote
up vote
0
down vote
Interesting challenge. Some of the issues include:
- Field calculations do not have enough flexibility to get what you need
- Although you can display numbers as % of total, and it appears you can sort on it - it really sorts on the underlying numbers.
I have a solution that makes use of Tables and Pivot Table. There may be a simpler solution available. The steps are (done in Excel 2016):
- Select inside your raw data. Select the "Insert" Ribbon and click on "Table"
- In your new table, insert a calculation for %NotApproved
- Select the "Table Tools" "Design" Ribbon and click "Summarize with Pivot Table"
- Construct a simple Pivot table with Manager Name as the rows and %NotApproved as the Values.
- Sort the Manager Names in Descending order by %NotApproved
Here is an example. The following is a snippet of 30 rows of "raw data" similar to described in your question ...
Select the "Insert" Ribbon and click on "Table" ...
You get better formatted data. Select D1, next the last column heading and type in "%No" - this creates a new column in the table with a new heading. In Cell D2, type in the following formula ...
=IF([@[TS Approved?]]="No",1,0)/COUNTIF([Manager Name],"="&[@[Manager Name]])*100
When you hit enter, it is automatically filled down in the table. This formula does:
IF([@[TS Approved?]]="No",1,0)
If the timesheet approved is "No", get a value of 1.
COUNTIF([Manager Name],"="&[@[Manager Name]])
Determines how many times the manager in this row appears in the table.- Result from 1 divided by the result from 2 times 100
The table now looks like this ...
Select "Table Tools" "Design" Ribbon, and click on Summarize with Pivot Table. Build the Pivot table to look like this ...
... and sort it ...
... to get this ...
Although it seems like a lot of steps to get set up, it's pretty easy to maintain the Table and this automatically keeps the Pivot Table maintained.
Interesting challenge. Some of the issues include:
- Field calculations do not have enough flexibility to get what you need
- Although you can display numbers as % of total, and it appears you can sort on it - it really sorts on the underlying numbers.
I have a solution that makes use of Tables and Pivot Table. There may be a simpler solution available. The steps are (done in Excel 2016):
- Select inside your raw data. Select the "Insert" Ribbon and click on "Table"
- In your new table, insert a calculation for %NotApproved
- Select the "Table Tools" "Design" Ribbon and click "Summarize with Pivot Table"
- Construct a simple Pivot table with Manager Name as the rows and %NotApproved as the Values.
- Sort the Manager Names in Descending order by %NotApproved
Here is an example. The following is a snippet of 30 rows of "raw data" similar to described in your question ...
Select the "Insert" Ribbon and click on "Table" ...
You get better formatted data. Select D1, next the last column heading and type in "%No" - this creates a new column in the table with a new heading. In Cell D2, type in the following formula ...
=IF([@[TS Approved?]]="No",1,0)/COUNTIF([Manager Name],"="&[@[Manager Name]])*100
When you hit enter, it is automatically filled down in the table. This formula does:
IF([@[TS Approved?]]="No",1,0)
If the timesheet approved is "No", get a value of 1.
COUNTIF([Manager Name],"="&[@[Manager Name]])
Determines how many times the manager in this row appears in the table.- Result from 1 divided by the result from 2 times 100
The table now looks like this ...
Select "Table Tools" "Design" Ribbon, and click on Summarize with Pivot Table. Build the Pivot table to look like this ...
... and sort it ...
... to get this ...
Although it seems like a lot of steps to get set up, it's pretty easy to maintain the Table and this automatically keeps the Pivot Table maintained.
answered Apr 24 '16 at 6:42
OldUgly
31517
31517
add a comment |
add a comment |
up vote
0
down vote
Not sure why, but I realized two things over breakfast this morning ...
- Using a Table is good, but perhaps just adds complication to the problem.
- Although you calculate your %Unapproved as the % of timesheets the manager is responsible for, you may want to calculate it as the % of all unapproved timesheets.
So I thought I'd post an alternative answer.
Beside your raw data, put a header %No
and this calculation below (and fill down).
=IF(C2="No",1,0)/COUNTIF($C$2:$C$31,"="&C2)*100
The formula calculates, if this timesheet is unapproved, the percentage of all unapproved timesheets it is.
Your raw data now looks like this ...
Build your pivot table and sort on %No.
If you still want %Unapproved to be % of timesheets the manager is responsible for, use this equation in Column D.
=IF(C2="No",1,0)/COUNTIF($B$2:$B$31,"="&B2)*100
Thanks for your feedback - I changed jobs not long after asking the question, and had to leave the spreadsheet behind! :( I'm not sure the above would have done it though...I needed to get the highest number of unapproved timesheets - then in the case of a tie, rank by percentage. If I understand your solution correctly, it'll put someone with 1 unapproved timesheets out of 1 (i.e. 100%) higher than someone with, 2 out of 5 (i.e. 20%). I would want the 2/5 to rank first - more physical non-approvals - then to rank ties on % (i.e. 2/5 vs 2/2 - 2/2 is higher due to the higher %). Thank you!
– Louise
Aug 25 '16 at 12:32
add a comment |
up vote
0
down vote
Not sure why, but I realized two things over breakfast this morning ...
- Using a Table is good, but perhaps just adds complication to the problem.
- Although you calculate your %Unapproved as the % of timesheets the manager is responsible for, you may want to calculate it as the % of all unapproved timesheets.
So I thought I'd post an alternative answer.
Beside your raw data, put a header %No
and this calculation below (and fill down).
=IF(C2="No",1,0)/COUNTIF($C$2:$C$31,"="&C2)*100
The formula calculates, if this timesheet is unapproved, the percentage of all unapproved timesheets it is.
Your raw data now looks like this ...
Build your pivot table and sort on %No.
If you still want %Unapproved to be % of timesheets the manager is responsible for, use this equation in Column D.
=IF(C2="No",1,0)/COUNTIF($B$2:$B$31,"="&B2)*100
Thanks for your feedback - I changed jobs not long after asking the question, and had to leave the spreadsheet behind! :( I'm not sure the above would have done it though...I needed to get the highest number of unapproved timesheets - then in the case of a tie, rank by percentage. If I understand your solution correctly, it'll put someone with 1 unapproved timesheets out of 1 (i.e. 100%) higher than someone with, 2 out of 5 (i.e. 20%). I would want the 2/5 to rank first - more physical non-approvals - then to rank ties on % (i.e. 2/5 vs 2/2 - 2/2 is higher due to the higher %). Thank you!
– Louise
Aug 25 '16 at 12:32
add a comment |
up vote
0
down vote
up vote
0
down vote
Not sure why, but I realized two things over breakfast this morning ...
- Using a Table is good, but perhaps just adds complication to the problem.
- Although you calculate your %Unapproved as the % of timesheets the manager is responsible for, you may want to calculate it as the % of all unapproved timesheets.
So I thought I'd post an alternative answer.
Beside your raw data, put a header %No
and this calculation below (and fill down).
=IF(C2="No",1,0)/COUNTIF($C$2:$C$31,"="&C2)*100
The formula calculates, if this timesheet is unapproved, the percentage of all unapproved timesheets it is.
Your raw data now looks like this ...
Build your pivot table and sort on %No.
If you still want %Unapproved to be % of timesheets the manager is responsible for, use this equation in Column D.
=IF(C2="No",1,0)/COUNTIF($B$2:$B$31,"="&B2)*100
Not sure why, but I realized two things over breakfast this morning ...
- Using a Table is good, but perhaps just adds complication to the problem.
- Although you calculate your %Unapproved as the % of timesheets the manager is responsible for, you may want to calculate it as the % of all unapproved timesheets.
So I thought I'd post an alternative answer.
Beside your raw data, put a header %No
and this calculation below (and fill down).
=IF(C2="No",1,0)/COUNTIF($C$2:$C$31,"="&C2)*100
The formula calculates, if this timesheet is unapproved, the percentage of all unapproved timesheets it is.
Your raw data now looks like this ...
Build your pivot table and sort on %No.
If you still want %Unapproved to be % of timesheets the manager is responsible for, use this equation in Column D.
=IF(C2="No",1,0)/COUNTIF($B$2:$B$31,"="&B2)*100
answered Apr 24 '16 at 16:25
OldUgly
31517
31517
Thanks for your feedback - I changed jobs not long after asking the question, and had to leave the spreadsheet behind! :( I'm not sure the above would have done it though...I needed to get the highest number of unapproved timesheets - then in the case of a tie, rank by percentage. If I understand your solution correctly, it'll put someone with 1 unapproved timesheets out of 1 (i.e. 100%) higher than someone with, 2 out of 5 (i.e. 20%). I would want the 2/5 to rank first - more physical non-approvals - then to rank ties on % (i.e. 2/5 vs 2/2 - 2/2 is higher due to the higher %). Thank you!
– Louise
Aug 25 '16 at 12:32
add a comment |
Thanks for your feedback - I changed jobs not long after asking the question, and had to leave the spreadsheet behind! :( I'm not sure the above would have done it though...I needed to get the highest number of unapproved timesheets - then in the case of a tie, rank by percentage. If I understand your solution correctly, it'll put someone with 1 unapproved timesheets out of 1 (i.e. 100%) higher than someone with, 2 out of 5 (i.e. 20%). I would want the 2/5 to rank first - more physical non-approvals - then to rank ties on % (i.e. 2/5 vs 2/2 - 2/2 is higher due to the higher %). Thank you!
– Louise
Aug 25 '16 at 12:32
Thanks for your feedback - I changed jobs not long after asking the question, and had to leave the spreadsheet behind! :( I'm not sure the above would have done it though...I needed to get the highest number of unapproved timesheets - then in the case of a tie, rank by percentage. If I understand your solution correctly, it'll put someone with 1 unapproved timesheets out of 1 (i.e. 100%) higher than someone with, 2 out of 5 (i.e. 20%). I would want the 2/5 to rank first - more physical non-approvals - then to rank ties on % (i.e. 2/5 vs 2/2 - 2/2 is higher due to the higher %). Thank you!
– Louise
Aug 25 '16 at 12:32
Thanks for your feedback - I changed jobs not long after asking the question, and had to leave the spreadsheet behind! :( I'm not sure the above would have done it though...I needed to get the highest number of unapproved timesheets - then in the case of a tie, rank by percentage. If I understand your solution correctly, it'll put someone with 1 unapproved timesheets out of 1 (i.e. 100%) higher than someone with, 2 out of 5 (i.e. 20%). I would want the 2/5 to rank first - more physical non-approvals - then to rank ties on % (i.e. 2/5 vs 2/2 - 2/2 is higher due to the higher %). Thank you!
– Louise
Aug 25 '16 at 12:32
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1065768%2fsort-excel-pivot-table-by-percentage-of-count%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
I'm not following you exactly on what you are doing, so maybe this article can help you achieve what you want - Excel Pivot Table Filters - Top 10.
– CharlieRB
Apr 15 '16 at 13:19
Thanks Charlie - but I don't think Top 10 works. I want the 5 worst managers - only 5. Top 10 will return more than 5 values as it doesn't differentiate between the duplicated numbers (e.g. in the above I'd get 11, 7, 6, 5, 5, 5 rather than just 11, 7, 6, 5, 5). I basically want to sort highest to lowest on % Unapproved, then highest to lowest on "No" and chase the 5 worst people.
– Louise
Apr 18 '16 at 10:51