Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.

Chart to display multiple variables in 2 different coloumns in Excel Charts

Latest post Thu, Jul 9 2009 10:20 AM by Bob Phillips. 2 replies.
  • Thu, Jul 9 2009 8:36 AM

    • llively
    • Not Ranked
    • Joined on Thu, Jul 9 2009
    • Posts 2
    • Points 26

    Chart to display multiple variables in 2 different coloumns in Excel Charts

    Normal 0 false false false EN-US X-NONE X-NONE

    /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin-top:0in; mso-para-margin-right:0in; mso-para-margin-bottom:10.0pt; mso-para-margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:"Times New Roman"; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;}

    Seeking help on this problem.

    Reply to the question (below) proposed on other forums.

    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
    >

     

    • Post Points: 5
  • Thu, Jul 9 2009 8:52 AM In reply to

    • llively
    • Not Ranked
    • Joined on Thu, Jul 9 2009
    • Posts 2
    • Points 26

    Re: Chart to display multiple variables in 2 different coloumns in Excel Charts

    When inserting the formula:

    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.

    • Post Points: 21
  • Thu, Jul 9 2009 10:20 AM In reply to

    Re: Chart to display multiple variables in 2 different coloumns in Excel Charts

    I have just recreated it, and it worked fine for me.

    Regards

    Bob

    • Post Points: 5
Page 1 of 1 (3 items) | RSS
Copyright Excel User Group and the relevant contributors, 2010. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.