Chart to display multiple variables in 2 different coloumns
 

We have 2 columns

A = Age groups - 7 different age groups denoted by 1 or 2 or 3
or 4 or 5 or 6 or 7
B = Gender - Denoted by 1=M and 0=F

Sample Data:

Age Groups Gender
1 0
3 1
4 0
7 0
3 0
4 0
6 1
1 1
7 0
3 1
4 1
5 0
6 0
7 0
7 1
6 0
5 1
4 0
1 1
2 1
3 1

We would like the chart to display the 7 Age Groups and what
percentage of the age groups are M or F.

Thanks in advance,

Lynn

Chart to display multiple variables in 2 different coloumns
 

Hi,

Suppose your data is in A2:B22 with titles on row 1, create a table like the
following say starting in E1:

Age Group M F
1 67% 33%
2 100% 0%
3 75% 25%
4 25% 75%
5 50% 50%
6 33% 67%
7 25% 75%

In F2 enter

=SUMPRODUCT(--($A$2:$A$22=E2),--($B$2:$B$22=1))/COUNTIF($A$2:$A$22,E2)

In G2 enter

=1-F2

Copy these down and then plot them.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"LLively" wrote:

We have 2 columns

A = Age groups - 7 different age groups denoted by 1 or 2 or 3
or 4 or 5 or 6 or 7
B = Gender - Denoted by 1=M and 0=F

Sample Data:

Age Groups Gender
1 0
3 1
4 0
7 0
3 0
4 0
6 1
1 1
7 0
3 1
4 1
5 0
6 0
7 0
7 1
6 0
5 1
4 0
1 1
2 1
3 1

We would like the chart to display the 7 Age Groups and what
percentage of the age groups are M or F.

Thanks in advance,

Lynn

Chart to display multiple variables in 2 different coloumns
 

Both F2 & G2 show #DIV/0!

I have made sure there are no spaces in the formula from the copy/paste and
no blanks or otherwise in the data cells.

"Shane Devenshire" wrote:

Hi,

Suppose your data is in A2:B22 with titles on row 1, create a table like the
following say starting in E1:

Age Group M F
1 67% 33%
2 100% 0%
3 75% 25%
4 25% 75%
5 50% 50%
6 33% 67%
7 25% 75%

In F2 enter

=SUMPRODUCT(--($A$2:$A$22=E2),--($B$2:$B$22=1))/COUNTIF($A$2:$A$22,E2)

In G2 enter

=1-F2

Copy these down and then plot them.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"LLively" wrote:

We have 2 columns

A = Age groups - 7 different age groups denoted by 1 or 2 or 3
or 4 or 5 or 6 or 7
B = Gender - Denoted by 1=M and 0=F

Sample Data:

Age Groups Gender
1 0
3 1
4 0
7 0
3 0
4 0
6 1
1 1
7 0
3 1
4 1
5 0
6 0
7 0
7 1
6 0
5 1
4 0
1 1
2 1
3 1

We would like the chart to display the 7 Age Groups and what
percentage of the age groups are M or F.

Thanks in advance,

Lynn