Percent of Total - How to build report

zico8

Board Regular
Joined
Jul 13, 2015
Messages
210
Hi,

I have some task - that looks like very easy, but... I cannot find solution :(

I have data like this:
Column1Column2
ANo
AYes
ANo
BNo
BYes
CYes
CYes
CNo

<tbody>
</tbody>

And want to get report showing the percent of Yes/Total:

Column1Yes/Total
A33,33%
B50,00%
C66,67%

<tbody>
</tbody>

How to build that?
Does Pivot Table do this? Or I need some transformation in PowerQuery?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,298
Office Version
365
Platform
Windows
Would this be any use?
Formula in E2 is copied down.

Excel Workbook
ABCDE
1Column1Column2
2ANoA
3AYesB50.00%
4ANoC66.67%
5BNo
6BYes
7CYes
8CYes
9CNo
10
% Yes
 

zico8

Board Regular
Joined
Jul 13, 2015
Messages
210
The result is OK but I am looking for some solution that I can create that report automatically in the gentle way - no matter how many data I have in my table.
More advanced sample thet need to be solved too is to have two parameters for each record of my table, like:

Column1Column2Column3
AXNo
AXYes
AYNo
BYNo
BXYes
CYYes
CYYes
CYNo

<tbody>
</tbody>

with result:
X (Yes/Total)Y (Yes/Total)
A50,00%0,00%
B100,00%0,00%
C66,67%

<tbody>
</tbody>


so... I think that should be some solution to do this with PT
 

zico8

Board Regular
Joined
Jul 13, 2015
Messages
210
Hi,

I have found that if I change each "Yes" to 1, and each "No" to 0 - creating these reports will be very easy in Pivot Table.
But is there any way to do it?
 

zico8

Board Regular
Joined
Jul 13, 2015
Messages
210
Done this by adding one column with formula:
If(Column3="Yes";1;0)
 

archangel99

New Member
Joined
Sep 22, 2010
Messages
7
Hi,

I have found that if I change each "Yes" to 1, and each "No" to 0 - creating these reports will be very easy in Pivot Table.
But is there any way to do it?
You can pull the yes/no column into your pivot table (column) and then compute the column percentage.

 

zico8

Board Regular
Joined
Jul 13, 2015
Messages
210
archangel99 - what you added to each field?
Rows: 'Header1';
Columns: 'Header2', 'Y/N';
Values: ??
 

zico8

Board Regular
Joined
Jul 13, 2015
Messages
210
But the result is different and incorrect.
Please see B+X. We have only YES, so the result for YES should be 100%
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,298
Office Version
365
Platform
Windows
Again this is without a PT, but see if any use.

Data in columns A:C is made into a formal table ('Table1' for me). This allows automatic expansion/contraction of the results.
Formula in E2 is copied down as far as you might ever need.
Formula in F2 is copied across and down as far as the column E formulas.

Excel Workbook
ABCDEFG
1DataX/YY/NXY
2AXNo0.00%
3AXYesB100.00%0.00%
4AYNoC66.67%
5BYNo
6BXYes
7CYYes
8CYYes
9CYNo
10
11
% of Total
 

Forum statistics

Threads
1,082,360
Messages
5,364,939
Members
400,815
Latest member
Joaquin Phoenix

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top