get percentages in pivot table?

[atlascanada]

I have 3 columns of data: animal (deer, fish, bear) /completeness(incomplete,complete, fragment) /level(1,2,3,4,5)

I wonder if there is a way to have a pivot table with animal down the side, level along the top and in the middle just the percent of "complete"

for instance there in level 1 there are 4 fish, 2 are complete and 2 are fragments, so i would like it to read "50%"

is this possible? or is there a way to do it without a pivot table?

any help would be great! thanks

 

[NiMip]

You can get that result, but it's a bit busy-looking and the only way I can think to only show % complete is to hide rows. I'm not a huge user of PivotTables though, so someone might have a good way to clean it up.

Once you've created your PivotTable:
1. Add "Level" to Column Labels
2. Add both "Animal" and "Completeness" to Row Labels
3. Add "Completeness" to Values
4. With a result in your PivotTable selected, in the ribbon under PivotTable Tool > Options > Calculations > Show Values As, select % of Parent Row Total
5. (Optional) Hide the rows containing incomplete and fragment

If you want to do this without a PivotTable, you could manually create your list of animals as row headings, levels as column headings and use a formula like:
=IFERROR(COUNTIFS(animalRange,$A2,levelRange,B$1,completenessRange,"complete")/COUNTIFS(animalRange,$A2,levelRange,B$1),"No Result")

[TABLE="class: grid, width: 629"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD="align: right"]B[/TD]
[TD="align: right"]C[/TD]
[TD="align: right"]D[/TD]
[TD="align: right"]E[/TD]
[TD="align: right"]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]deer[/TD]
[TD="align: right"]14%[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]25%[/TD]
[TD="align: right"]71%[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]bear[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]67%[/TD]
[TD="align: right"]33%[/TD]
[TD="align: right"]29%[/TD]
[TD="align: right"]20%[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]fish[/TD]
[TD="align: right"]29%[/TD]
[TD="align: right"]50%[/TD]
[TD="align: right"]43%[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]0%[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]crow[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]33%[/TD]
[TD="align: right"]50%[/TD]
[TD="align: right"]71%[/TD]
[TD="align: right"]25%[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]magpie[/TD]
[TD="align: center"]No Result[/TD]
[TD="align: right"]67%[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]40%[/TD]
[TD="align: right"]25%[/TD]
[/TR]
</tbody>[/TABLE]

 

[atlascanada]

perfect for what I needed. thanks a lot =)