Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Etobicoke, Ontario, Canada
    Posts
    198
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Graphs/Charts (9.0.6926 SP-3)

    I set up a simple spreadsheet for my daughter so that she could complete a class survey. Now she wants % Charts and I'm stumped. I feel like such a dope! <img src=/S/grovel.gif border=0 alt=grovel width=31 height=23>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Graphs/Charts (9.0.6926 SP-3)

    % charts of what?

  3. #3
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Etobicoke, Ontario, Canada
    Posts
    198
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Graphs/Charts (9.0.6926 SP-3)

    Sorry. There were 100 surveys and 20 questions. She wants a % out of 100 chart for each question. Also, I'm not sure what to do about the questions with no answer.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Graphs/Charts (9.0.6926 SP-3)

    I have attached a version of the workbook with pie charts for questions 1b and 2.
    - If you click once on a "pie", you can see where its data come from; you can also see this in the Series tab of Chart | Source Data...
    - If you select Chart | Chart Options..., you can see how the chart title and the data labels are set up.
    You can copy a chart by copy and paste or by dragging it with Ctrl held down. You can then modify the source data.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Graphs/Charts (9.0.6926 SP-3)

    Here is an alternate method from Hans' file.

    Instead of creating 22 individual charts, you can use the spinners to go thru them one-by-one or use the combobox to select the question to chart.

    Steve

  6. #6
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Etobicoke, Ontario, Canada
    Posts
    198
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Graphs/Charts (9.0.6926 SP-3)

    Thanks alot. I actually do know how to do it but I was having trouble updating the values. The pie slices were the proper size but the values were wrong. Under Chart Options/Data Labels there is an additional checkbox above 'Legend key next to label' that only appears under certain circumstances. I can't remember exactly what it said or how to re-create it but once I checked it my values and labels were updated.
    <img src=/S/headthrob.gif border=0 alt=headthrob width=15 height=15>

  7. #7
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Etobicoke, Ontario, Canada
    Posts
    198
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Graphs/Charts (9.0.6926 SP-3)

    Steve, that's pretty cool! How can I learn to do that? <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Graphs/Charts (9.0.6926 SP-3)

    The 'Results' sheet is essentially the same as your had it. It does not require any changes

    The 'cboInfo' sheet is new.

    A5:A26 is the question numbers
    B5:B26 lists the questions (this range is named [via insert named - define] 'cboQuestions')
    E5:K26 has the responses or blanks
    D5 has the formula:
    =COUNTA(E5:K5)
    D5 is copied to D626
    It counts the number of responses for each question.
    C5:C26 lists starting columns for the Answers (in the 'Results' sheet)
    C5 has 2, c6 has the formula:
    =C5+D5
    C5 is copied to C6 to C25
    [Question1a starts in Colb (=2) and the rest are calc based on the number of responses...]

    This sheet has several named ranges (insert - name - define)
    cboSelect is cboInfo!$A$1. This holds the "rowindex" from the quesion list (Q1a = 1, 1b = 2, 2 = 3, etc)
    Question is cboInfo!$B$1 which is:
    =INDEX(A5:A26,cboSelect)&": "&INDEX(cboQuestions,cboSelect)
    It gets the "selected" (from cboSelect) Quest Num and the Question together

    AnsCol is cboInfo!$C$1:
    =INDEX(C5:C26,cboSelect)
    which gets the "selected" Ans column

    and NumAns is cboInfo!$D$1:
    =INDEX(D526,cboSelect)

    Thus changing the value in A1 from 1 to 23 will get a new entry of the Question number and the question and also

    get the starting column and number of responses. Now with the information we can get the information for the chart.

    The chart needs 2 ranges: The "values" are the sums from the appropriate columns of row104 of the Results sheet

    and the "Category Labels" are the response labels from cboInfo cols E and F from the appropriate rows.

    To get them to change with the changing of cboInfo!A1 we create dynamic ranges:
    Insert - name -define
    Name:
    Answers
    Refers to:
    =OFFSET(INDIRECT("cboinfo!$A$1"),ROW(cboQuestions) +cboSelect-2,4,1,NumAns)
    <add>
    Name:
    SurvResults
    Refers to:
    =OFFSET(INDIRECT("Results!$A$104"),0,AnsCol-1,1,NumAns)
    <ok>

    The Answers(responses) are in in the range, starting at cboinfo!$A$1, going the number of rows from the starting row of 'cboQuestions' plus the value of cboSelect-2, then 4 columns over. The range is 1 row tall and has NumAns for the number of columns.

    The SurveyResults are in the range, starting at results!$A$104, going 0 rows down, 1 less than the start column over. The range is 1 row tall and has NumAns for the number of columns.

    These ranges are added as the source data to the chart. As a title the range named Question is used (this is the combination of the number and question.

    Finally instead of manually changing cboInfo!A1, we add a combobox from the FORMS toolbar, and under the format control - control change the input range and cell link as indicated, expand the dropdown lines.

    In addition to using the combobox to select a particular one, I like scrolling thru the list with a spinner. Under formatcontrol - control, change the min/max values and the cell link.

    So either changing the spinner or the combobox change A1, which changes B11. Changing B1 changes the chart title, changing C11 also changes the chart ranges and the chart is thus updated.

    This is all done with items from the forms toolbar and formulas. No VB is required.

    Steve

  9. #9
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Etobicoke, Ontario, Canada
    Posts
    198
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Graphs/Charts (9.0.6926 SP-3)

    Yikes! <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15> Thanks (I think)! <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •