Pie chart in excel (with conditions) 1

[irinnew]

Hi Experts,

I have a table (excel spreadsheet) with following fields:

ID (char)
Agecat (char)
Gender (char)
Indicator (integer)

ID Agecat Gender Indicator
1 A F 0
2 B F 1
3 A F 1
4 C F 0
5 C F 1
6 B M 0
7 B M 1
8 D M 1
9 C M 0
10 C M 1


1.I need to show a percentage of Gender By Age for those who have an indicator =1.
2. I need this percentage among all other members of the table.
3. I need to represent it in a PIE CHART format

Could you please give me a hand?

Thank you in advance,

Irin

 

[GlennUK]

For your example, what would want the chart to look like exactly?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[WalterContrata]

Irin,

I think that pie charts represent a whole sample by a pie, which is cut into slices showing the size of the components. In this case, what is the whole sample? Is it all all of the ID's in the table, or just a selection that meets some condition? Is it one pie chart that we are after, or a series of pie charts for, say different ages?

Best Regards,
Walter

 

[iren]

Walter,
Yes, saying a whole thing I mean all of the IDs in the table. In such a case sectors belonging to M an F should be sliced by age groups but F as well as M sectors will not take a whole circle because there are a lot of IDs in the table while those IDs with indicator= 1 are just a part (not 100%).
I would say, that it could look like a circle with yellow sector for M (sliced by age)for indicator=1; red sector for F (sliced) for indicator=1 ....and the rest in blue (not sliced) with indicator=0
Sorry for my poor English:-(

Irin

 

[WalterContrata]

Hey Irin,

No problem with your English; it is very clear.

Others may have better methods, but I would
[ol]
[li]Make a help column, combining, Age, Gender, and Indicator into one cell for each row (e.g. "AF1, BM0, etc.).
[/li]
[li]Make a summary table. The first column contains labels for the pie slices (e.g. "Male, A"), and the second column counts the number that match that label, using the COUNTIF function on the helper column[/li]
[li]Make a pie chart of the summary table.[/li]
[li]Manually color the slices in a way that makes sense.[/li]
[/ol]

Best Regards,
Walter

 

[WalterContrata]

Assuming that your data are in columns A-D.

A B C D
ID Agecat Gender Indicator
1 A F 0
2 B F 1

[ol]
[li]I put helper column in E, and function is
= B2 & C2 & D2
filled all the way down to the bottom of the data.
[/li]

[li]Summary table is something like[/li]
AgeGender Count
MA =COUNTIF($E$2:$E$11, "=AM1")
MB =COUNTIF($E$2:$E$11, "=BM1")
MC ....
FA
FB
FC =COUNTIF($E$2:$E$11, "=CF1")
Null =COUNTIF(D211,0)

[/ol]

Last row counts all of the null results.

Hope this helps,
W

 

[iren]

Walter,

Thank you! I did the first step and now I am trying to create a summary table....

 

[irinnew]

Dear Experts,

I know it is a naïve question but I really have no idea how to create a summary table. Even do not know how to start it…

Should I create it in another sheet? How to point to the initial sheet then?

Could you please give me a hand?

Thank You in advance!

Irin

 

[WalterContrata]

Hello Irin,

By "summary table", I meant a table that summarizes the data; it is not a special feature of Excel. You can put it on another sheet, if that is convenient. You can even put it in another workbook, although I don't recommend it!

I would use 2 columns for the summary table. In the left column, I would type labels that indicate a set of results (e.g. MA for males in age category A). In the left column, I would type formulae that count the number of results in the set, for example,

=COUNTIF(range, "=AM1")

Here range refers to the helper column that you made in step 1. Just type "=COUNTIF(" then use the mouse to select all of the cells in the helper range (even on another sheet). Excel will put the appropriate reference in the formula for you. Then finish typing ", "=AM1").

Hope this helps,
Walter

 

[WalterContrata]

Irin,

An example is at

http://www.geocities.com/straight_manus/TempOfficeFiles/Pie.zip

or

http://www.geocities.com/straight_manus/TempOfficeFiles/Pie.zip

The first sheet has fake data on it, and the second has a summary and a pie chart.

Hope this helps,
Walter

WC

 

[irinnew]

Walter,

I am following your steps and finally I got Frequency=11 at the very 1st cell but when I drag it it is still “11” in the all of cells

1.I am typing COUNTIF(
2.Selecting cells range
3. Typing ,
4. Typing “=AM1”)
5. Run
6. Getting 11 at the very top cell
7. Dragging ….and get all of them 11 ?

I noticed, however, that the formula generated differs from yours:
Yours looks like:

=COUNTIF(FakeData!$E$2:E101, A2 & 1)

while mine looks like:

=COUNTIF(FakeData!E2:E101, “=AM1”)

However when I tried artificially change it to yours it does not work…

What I am doing wrong?

Thank You!

Irin

 

[WalterContrata]

Hey Irin,

Check out how the COUNTIF function works. It has two arguments. The first argument is a range of cells, and the second argument is a value or a condition. The function counts the number of cells in the range that match the condition.

In our case, the range is the helper column range, and the condition is "=AM1", "=BM1", "=CM1", "=AF1", ...
(By the way, you don't need the "=".)

You want to save time by dragging the COUNTIF formula down, and that is a great idea. But Excel doesn't know that it needs to change the second argument. In the example, I made the second argument refer to the adjacent column, so that it gives the appropriate value in each row. Alternatively, you can edit each cell, typing the appropriate second argument in.

By the way, what did Excel do with the first argument when you dragged the formula down? Does each copy of the formula refer to the same range of cells? There are two types of reference to ranges, relative and absolute. The absolute style ($E$2:$E$101 or R2C5:R101C5) refers to the same range, even after you drag or copy it somewhere. The relative style (E2:E101 or RC[2]:R[100]C[2]) changes when you copy or drag.

Hope this helps,
Walter

 

[irinnew]

Walter,

Thank you very much for your Help. I was able to complete the 1st part of my task
Which to create a sliced sector which I did with your help!!!

The second part is to create a circle with 2 sectors. The biggest sector is ON ON DRUGS population (with indicator=1) and the smallest is NOT ON DRUGS (indicator=0). After that I need to create two Pie of Pie based on the smallest sector (NOT ON DRUGS)
- to create a Pie of Pie to show just gender regardless age
- to create a Pie of Pie to show just Age regardless a gender

I am confused again….How it is possible?

Thank you again for all your help!

Irin

 

[WalterContrata]

Irin,

Sorry not to respond sooner. Work has been very busy.

For the 1st part, can you see a way to summarize the data again, using the COUNTIF function? This time the range would be the indicator column, and the condition would be "0" for one row and "1" for the other.

For the second part, I would be tempted to sort (Data -> Sort) the raw data by indicator. That would get the NOT ON DRUGS population all together. You can use COUNTIF again to count the number of male and female, or the number in each age group.

Hope this helps,
Walter

 

[irinnew]

Walter,

Thank you very much for your help.

Now I was able to create a pie exactly as it should be ("not on drugs"-"0" takes 1/3

of the whole pie but I am still confused with a sub-pie which is 33%

I know how to split out the sub-pie (33%) : for ex: 20%; 10% : 3%

I am not sure however how to split out the sub-pie in order it to show a percentage among just sub-pie sector considering the sub-pie sector itself is 100%.

I that possible?

THANKS A LOT!

Irin

 

[WalterContrata]

Irin,

I think that Excel will split out any sectors you like to make a second pie chart. From your last post, you have a table like

OnDrugs1 20
OnDrugs2 10
OnDrugs3 3
NotOnDrugs 67

(In this case, the sum is 100, but it need not be.)

In Excel 2000, it works as follows.

Select the data.
Click on the chart wizard.
For chart type, choose "Pie Chart".
For chart sub-type, choose "Pie of Pie"
(icon shows two pie charts)
Finish.

Now you have a main pie chart with a split out chart, but you still need to split out the right data.

Double click anywhere on the one of the pie-charts. A menu appears.

Choose the option tab.
Select "Choose series by custom".
Click "OK".

Now you can drag sectors between the charts. Drag the sectors representing the "NotOnDrugs" groups to the chart on the right. If an "OnDrugs" sector is in the chart on the right, drag it to left.

Does this work for you? Is it what you want?

Best Regards,
Walter

 

[irinnew]

Walter,

I need something different now and it can be illustrated with a following table:
help cnt %whole% among "0"
less35ye1 152
35_54ye1 1137
55_64ye1 814
more65ye1 250
less35ye0 25 1% 2
35_54ye0 485 14 42
55_64ye0 470 13 41
more65ye0 177 5 15
____________________________________
Subtotal (0) 1157 33 100
Total 3510
****************************************************
As you can see I have “0” members 25+485+470+177=1157 and it is a one third (33%) of the whole pie of 3510

When I follow you technique I get a sub-pie spitted out into percentage 1% + 14%+ 13%+ 5%=33%

However I want them to be splitted into 2% (25/1157*100%); 42% (485/1157*100%); 41% (470/1157*100%); 15% (177/1157*100%)

25+42%+41%+15%=100%
In other words ,currently I got each age group of sub-pie percented among a whole pie while I need them percented among just a sub-pie sector (as if this sector “0” is 100% rather than 33%)

I am not sure if it is possible however…..

Thank you again,

Irin

 

[WalterContrata]

Irin,

Is it that you want the labels of the split out pie chart to show percentages of the split out population? It may be possible to do that, but I do not see how. Would it be an acceptable work-around to make a second pie chart of the split-out data only?

Best Regards,
Walter

 

[irinnew]

Walter,

This thought came to my mind either. My manager, however, requested me to format it as a sub-pie. I just wanted to explore all options before I would have to ask her if it might me acceptable to make just a second pie.:-( Hopefully so. Thank you very much for all of your help!

Irin