+ Reply to Thread
Results 1 to 4 of 4

Count "Yes"an "No" answers in a Survey with several questions.

  1. #1
    Registered User
    Join Date
    09-04-2015
    Location
    Porto Alegre, Brazil
    MS-Off Ver
    Office 365 Business Premium/E3
    Posts
    2

    Count "Yes"an "No" answers in a Survey with several questions.

    I have Sharepoint Surveys which return information in the form presented as the first table in the reduced test case file.

    I have created a second table to illustrate the possible answers.

    I would like to create a table counting the "Sim" and "Não" (Yes and No) and Chart it using Pivot Tables, but I haven't found a way to count the text information to give me the desired table format.

    I would appreciate help on this matter, thanks!

    (I'm new to Pivot Tables)

    reduced test case.xls:
    reduced test case.xlsx

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,252

    Re: Count "Yes"an "No" answers in a Survey with several questions.

    Your data is not in a form that allows Pivot Table use, since it is a cross-tab table rather than a database. But you can use simple formulas to get what you want:

    reduced test case solution.xlsx
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    09-04-2015
    Location
    Porto Alegre, Brazil
    MS-Off Ver
    Office 365 Business Premium/E3
    Posts
    2

    Re: Count "Yes"an "No" answers in a Survey with several questions.

    Thanks for the reply!

    The thing is:

    I need Pivot Charts, because they are created from PivotTables, because they can be named and added to a SharePoint page. I cannot do that with plain charts.
    (It's even possible to display plain charts, but I cannot name them and pick a specific one from a Workbook to display on a SharePoint page).

    That, I'd have to do that from a Pivot Table.

    I am interested in generating basically 3 types of charts:

    pie charts from numerical values about rural property land use;

    Combo Charts using column + line counting the % of Yes compared to Total Answers. (count of surveys = 100%)
    Several values (columns) would be grouped on each chart by topic.
    THe line combo would be the average of all values on the several column, to get an average performance metric.

    Radar Combo charts displaying the same information as the column + line combo above (because people want Radar Charts).

    The Charts themselves are not the problem, they already exist as plain spreadsheets+charts.

    The thing is the requirement to make them in Sharepoint Online, and to do that the data must come from a Pivot Table and the Charts must be Named Pivot Charts.

    Maybe I must look at the problem from a different angle, or arrange my data differently.

    The first table at the top left of the attached document represents the way the Survey is exported from Sharepoint to Excel via a Connection. I can't really change that apart from changing which columns are exported.

    Any different way to tackle the problem? Would there be a way I can reorganize the data so that I can create a Pivot Table with the desired results?

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,252

    Re: Count "Yes"an "No" answers in a Survey with several questions.

    With your data table on the activesheet (with the first cell being A1), run this macro, then base the pivot tables and pivot charts on the new database.

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 02-20-2015, 01:13 PM
  2. Replies: 4
    Last Post: 11-17-2013, 12:05 PM
  3. [SOLVED] How to USE """"" cells count """"" change font color
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 06:14 AM
  4. [SOLVED] How to Count number of "Error" and "OK" after the word "Instrument" found in table row
    By eltonlaw in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-17-2012, 06:26 AM
  5. Replies: 5
    Last Post: 10-12-2010, 06:46 AM
  6. Replies: 1
    Last Post: 07-16-2010, 02:44 AM
  7. Replies: 7
    Last Post: 05-13-2006, 05:02 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1