Analyzing Survey Data In Excel
Apr 6, 2010After survey questions and responses are in excel, how do you analyze this data using a correlation matrix and regression analysis for reporting or testing hypotheses?
View 3 Replies
ADVERTISEMENT
Analyzing S Survey Form
Apr 20, 2013I would like to analyze a survey form.
See attached example : survey.xlsx‎
I am trying to display a total of answers per question and transform that into a grid for easy reporting but am not sure how to do it.
Sort Survey Data In Excel
Apr 4, 2013I want to analyze a survey which I made. Below you see an example of how I structured the answers.
Now I would like to rank the answes (rows 2-6) in a list according to often each item was mentioned.
I could do transpose the data manually and delete items which were mentioned various times. However, since it was a pretty big survey, it would take to long. Is there a formula to do it?
Original:
Person A
Person B
Person C
Water
Water
Sugar
Washing
Washing
Pasta
Boxes
Milk
Water
Frozen
Vegs
Fish
Cleaning
Cheese
Water
Example of how it should look like:
Water
3 (times mentioned)
100% (because everone mentioned it)
Analyzing Months Of Data
Jun 3, 2007I have a spreadsheet for a stock - HOT TOPIC (ticker: HOTT) and I have the daily closing prices for several years going back. If I wanted to create a table that showed which months were the worst performing months -- when it lost the most money -- how do I set that up? I am particularly focusing on June/July to see if those are the worst performing months for this stock.
Looking back say 3 years (so 2004, 2005, 2006). (Maybe year-to- date 2007 too).
I colored the cells that contain the summer months june/july/august...but i am guessing its just june/july that has the worst performance... How do I set this up?
Excel 2011 :: Analyzing List Of Repeating Codes With Values Greater Than -1?
Feb 4, 2013I am using Excel 2011 on a mac. I have a selection of data that lists a repeating code (say group 1, group 2, group 3 etc)and a value (-1,-2.3,3,6 etc) and need to find a quick way group together all the codes and next to that a value that counts how many values who share the same code hold a value greater than -1? I can do this myself in a pivot chat with count totals but my issue is how I can show the number of codes greater than -1.
Attached is an example of the data I want to sort.
Example of data that needs sorting.xlsx
Analyzing Differences In Date And Time Data Presented In Two Columns?
Apr 24, 2014I am wondering if there is way to analayse date and time data in a way that would allow me to look at time differences.
I have two columns of data, one has the date and time an entry was made onto a client database, the other shows the date and time an amendment or deletion was made.
What I would like is a formula that shows the time period difference between the two entries.
So for example if one column shows 14/02/2014 14:26 and the other shows 14/02/2014 14:28 it would show 1 (minute) as the result.
I have over 1000 entries to look at and manually it is taking a looooooooong time!
Excel 2003 :: Tabulating Survey Results
May 27, 2014Is there a way to show the results of a survey (basic y/n) as a percentage of total surveys submitted?
I have also been charged with keeping the customer data (name/house number) visible when it's displayed as a chart?
Attached is a sample : sample.xls
Excel 2003 btw...
Extract Data From Survey Checkbox
Dec 11, 2013I have a survey/form that was created in excel, and that is answered in excel. I am required to extract the data into a worksheet for each response received. I am able to pull the text that is entered, however i am having trouble pulling through the info from the checkboxes. So if a checkbox is ticked i cannot pull that through?
View 1 Replies View RelatedHow To Organize Survey Data That Has 2 Choices
Nov 17, 2013I did a survey of what kind of clothes people like to wear and why. So there are two things that people have to write down. ex) jeans and cus they're comfortable / shirts and cus they look good
The data that I received was organized as followed
I just put in random things just so you can get the idea of whats what
Id
jeans
shirts
cardigans
[Code].....
Okay so what I want to know is how I can get the responses from the data spreadsheet I showed you before into this organized data table above.
Analyzing Scores Per Subjects
May 2, 2014I need a formula to analyze the attached sheet.
I need to know
1-Number of students that passed with 70% and above in English, Maths and Biology
2- Number of students that passed with 50%-69% and above in English, Maths and Biology
3- Number of students that scored below 50% in English, Maths and Biology
4-Number of student that where absent for each of the subjects (English, Maths, biology)
Note: Total of all the columns per subject must be equal to the total number of students in the sheet.
Analyzing Text And Compiling
Feb 19, 2009I have a row of text (for example say they are cells c3, d3, e3, f3) that have one of four text strings (red, yellow, green, blue). In cell b3, I need it to report back the column heading of all the cells that have the text "blue".
So if cells c3 and f3 have text "blue", then I need their column headings to be inserted into cell b3.
Survey Form With More Than 2 Options
Apr 10, 2014I followed a youtube video on setting up a survey in excel, and it works great, but it was only for two options. I wanted to make it 5 options per question, but when I started adding to the code ( which I thought was correct, but was not ) I kept getting errors. It probably is a simple addition. How to make the survey options more than two?
There are three tabs. The first tab (Start) only has a button that leads to the QA Survey. The Second tab has the questions and answers. The third tab as three columns in it as well for the name of the person, question number, and answer choice.
Here is the code inside the form (QASurvey):
Code:
Private Sub button_next_Click()
''confirm there is a name
If TextBox1.Value = "" Then
MsgBox ("Please enter your name")
[Code] ...
Here is the code in Module 1:
Code:
''global variables
Public info() As Variant
Public results() As Variant
Public questionnumber As Integer
Here is the code on the sheet that has the button to start the Survey Form:
Code:
Private Sub Start_Button_Click()
QASurvey.Show
End Sub
Rank Questions In A Survey
Nov 7, 2006I am trying to rank questions within a survey with excel calculations and not VBA. The rank would take place for each individual section and would provide the top three right questions and top three wrong questions. The example below explains the ranking.
Brief facts:
Each survey has up to 30 sections.
Each section has up to 150 questions.
Each question has none or no limt of sub-questions.(usually between 1 and 20, weekly changes require this number to change)
The section's number of questions change weekly with updates.
This week section 2 begins with #12, next week begins with #16 because more questions were added to section 1.
There are three possible choices to answer a question (yes, no, or n/a.)
Sub-questions are checkboxes of explanations on why the question could be answered wrong.
Each question may have 0-10 points assigned.
Each sub-question may have 0-10 points assigned.
When a question is answered "no" is when a sub-question may be checked to help explain why.
A question with sub-questions may only be assigned points at the sub-question level. The sub-questions have the points and do not sum up to total the question value.
When a question is answered "no" and a sub-question is checked, the points received are 0 points for that sub-question. Unchecked sub-questions receive the full amount of points.
When a question is answered "yes" all points are received.
The Rank:
The rank would take place for each individual section and would provide the top three right questions and top three wrong questions at the question level. My example below has more than three to help explain further.
Select the highest actual score of question or sub-question. Start with 10 points to 0 points.
When a match is found that becomes the first "correct" question rank. #17 is 1 since 17.1 had an 8 point sub-question.
When there is a tie #14,15,18, the tie breaker is the sum of correct sub-questions added to the question score. #14 = 7 points.
The second sort is the # in ascending order #15 and then 18.
The hardest part of the rank is the flexibility of a changing survey with question/sub-question deletions and additions in each section. ...
Survey Form - How To Open Another Thread
Jan 2, 2013how to open another thread. I would like to do a survey form (as attached) but I wanted to use VBA to make it easy to compute the result.
View 2 Replies View RelatedSurvey Collection - Up / Down Spin Buttons
Jul 10, 2014I'm creating a spreadsheet to collect survey data and how I have it set up is this:
Question 1 is a simple yes/no/maybe question - it uses an ActiveX spin button that the user presses up/down to increase/decrease the total. It looks like this:
Private Sub Yes_SpinUp()
With Range("C3")
.Value = WorksheetFunction.Min(15000, .Value + 1)
End With
End Sub
Private Sub Yes_SpinDown()
With Range("C3")
.Value = WorksheetFunction.Max(0, .Value - 1)
End With
End Sub
Cell C3 is where I keep track of how many people answered yes
The button is called "yes" under the button properties
So basically, I have one privatesub for spinning the value up and down. It works fine and is easily copy-paste-able for other answers, it just takes up a lot of space. I'm wondering if there's code I can write to handle the up/down in one sub. If not, i'll stick with what I have.
Number Plate Survey Normalization
Mar 29, 2007I have a list of vehicle number plates (or License plates for some of you) that have been recorded on a video camera and then converted to a text file using recognition software.
Unfortunately the records are far from 100% accurate so any data manipulation (travel times between points etc) becomes a bit dodgy and clumbsy to allow for this. I want to keep all of the data entries but need to identify each vehicle with a little more accuracy. I'm not worried about whether the plate number recorded is correct, only that it is the same for each instance that a particular vehicle is recorded.
The data comes in looking roughly like this:
ABC123
DEF456
AC13
A123
DE56
DEF56
I would like it to look like this:
ABC123
DEF456
ABC123
ABC123
DEF456
DEF456
Or even this:
AB123
DF45
AB123
AB123
DF45
DF45
It doesn't matter which of the above or any other compination as long as there are at least 4 characters.
The surveys are taken over up to a week so there are generally about 5000 records for each time slot that need to be processed.
I have tried to modify the Fuzzy matching functions with no success.
Survey Results In Pivot Table
Jul 25, 2007I have just recently finished inputting data into a spreadsheet which is a whole bunch of survey responses and I am trying to figure out the best way of now “analysing” it. Unfortunately the questionnaire consists of various types of questions, including some open ended, some where the participant selected a number on a scale, or the participants could select any number of options (e.g. tick any that apply).
From a brief search, I see that you can’t have two headings as such for a pivot table so I am wondering what the best approach might be. I have attached a sample spreadsheet. I have a hunch though that it’s going to be a matter of analysing each question individually and using filters and countif formulas (see attached).
Survey Results Table Lookup Errors?
Jun 26, 2014An offset sumproduct series of functions provides me the total score percentage for the top level categories. There are 5-6 in total and can easily be charted in a radar.
The second step is to maintain the 'dynamic aspect' (being able to delete rows) and lookup the high-level category, and the subsection and return the score for the individual question. This way I can create a chart for each of the 5 top level categories and show the survey score for each answer in the category. Note each question has a maximum score of 5 and are scored 1-5.
I have a few errors looking up values and percentages coming back as 500% with the addition of new columns (in red in attached). The first sheet is error free, the second sheet is the 'build'.
Note: Charts will be housed on a separate sheet.
Array Or Countifs For Counting Survey Responses
Nov 4, 2009I have recorded the data from 12 records (i actually have hundreds, but im just using a small sample). Each record is represented by a vertical column. I want to find the total number of "x's" for only the records from Country1 (from all the answers). Then I want to find the number of "x's" from only Country2. I have tried the "countifs" function but cannot find a way to make it work. Would a different array function be better? How would I write it?
P.S. The answer for Country1 should be: 15.
The answer for Country2 should be: 5.
Selecting Latest Date (Particular Type Of Survey)?
Mar 18, 2013I have a spreadsheet which has a list of properties with a list of survey dates. The complication is that every property has multiple surveys and these are all on separate lines with the spreadsheet. The number of surveys could also be different depending on the property in question.
What I would like to do is to compile a report which only shows me when the latest particular type of survey (there are five types of survey, I'll call them 1, 2, 3, 4 and 5).
The survey type is shown in column C, the property is shown in column A and the survey date is shown in column F.
Tallying Survey Responses Using Macros, Buttons
Dec 13, 2004I conducted a survey and want to tabulate the results in excel. There is a lot of paper, so I want to list each answer on the spreadsheet and have a button next to it to tabulate the responses instead of doing it by hand. When I look at each survey, I want to click the button for the corresponding answer. I want to have excel tally/increment each response each type I click the button.
So what macro do I need to assign to each button in order to do this? Or can I just click on a cell and have it increment? Or what can I do to achieve this?
My attempts have given my circular reference errors, or if I do the iteration thing (tools, options, iterations), excel increments EVERYTHING in the spreadsheet by one instead of just one specific question.
Create A Formula To Average Survey Results Per Instructor?
May 28, 2014I am trying to come up with a good way to average a particular question in a survey result for instructors, however the caveat has me pretty puzzled. We're looking to be able to filter by mm/dd/yyyy date (which pulls mm/dd/yyyy hh:mm from raw data) and then have one average of all scores for each instructor listed. It gets a bit complicated in that the actual survey has 6 drop down boxes with all the instructors names, so one instructor could have data for the same (or any) date in any of the 6 colored columns (name, score, qualitative x 6).
My initial thought was to list out the names on a second sheet and then have some sort of averageifs to combine the columns, but I am a bit lost on how to take the dates into account, let alone how to sum the columns then average.
Making Sure An Option Button Has Been Selected Within A Group Before Exiting Survey
Nov 26, 2009I have a survey with different groups of Control Toolbox options buttons on it. I want to ensure that each question has an option button selected before the survey can be exited and emailed onwards. The grouped button names are: GroupA, Group1 through to Group6.
View 2 Replies View RelatedFetching Specific Data From Excel Cell To Different Columns Of Second Excel File?
Sep 7, 2012I have data in the following format-----
Name : XYZ
City : ABC
Place : sdfg
Error :
price : [X]
cost : [ ]
time : [ ]
[code]....
I want only name, place,desc and under error, i want that type which is marked cross in the brackets(In the above example it is price ) .
name, place,desc,error should be pasted to separate columns in second excel sheet.
Macro To Convert PDF To Excel Data To Excel Format
May 23, 2009Is there any macro that can be used to convert data which have been converted from PDF to Excel. Currently I'm using a traditional way by pressing F2 and enter to convert the data to excel format. It's tedious ( since the data range is quite big) and crammed my finger.
View 9 Replies View RelatedExcel 2010 :: Access Data Connection Query - Missing Data All Of Sudden
Oct 19, 2012I created a slick little excel sheet with the data coming in automatically via Access query. It has been working fine for months. Now all of the sudden there are a bunch of cells with missing data. The weird part is it's not as if whole columns are missing data, more like 90% missing. When I go to Access and run the query all cells are populated as the should be. There have been no changes to the query at all during this time.
btw...I am running Office 2010
Excel 2010 :: Click On Pivot Chart Data Point And Display Data
Apr 22, 2014Pivot Chart. I would like to set up something to where a user can click on an individual value on a pivot chart (currently a line chart set up with 4 data series) and somehow display some underlying data. I have a lot of information stored in a data worksheet that I can't display all at once, but if a user sees a questionable data point, he/she can click and learn more about it from source data, or even a new query of the data worksheet.
I am using Excel 2010
Excel 2010 :: Formula To Indicate Alphabetical Data Connected To Numeric Data
Jan 8, 2014I have a question about using conditional formatting in excel (2010); I made a table with the following columns:
"Supplier Name" "Supplier Lead Time" "Internal Lead Time" "Total Lead Time". I made two different scenarios to show different supplier lead times and different internal lead times, and used the minimum function in the "Total Lead Time" column to find the smallest total lead time to select the best supplier.
The last thing I would want to do with this set of data, is plug in a formula that would somehow indicate which supplier corresponds to the shortest total lead time (which supplier has the smallest supplier lead time). I'd like to be able to use a formula that enters the name of the supplier in a designated cell, which I could indicate as the "Preferred Supplier" cell. If this is not possible perhaps there is a way to highlight the supplier's name with conditional formatting?
Excel 2007 :: Moving Data From One Worksheet To Another Keeping Data And Formatting?
May 5, 2014I am working on a project that has 5 worksheets. I have been able to figure out everything else I need to do but this has me stumped. I have data in Sheet1 A6, that i want to place in Sheet2 A6, Sheet3 A6, Sheet4 A6 and Sheet5 A6 and keep data and formatting(BOLD AND UNDERLINE). So I change Sheet1 A6 and the other 4 sheets change also. I'm using Microsoft Excel 2007.
View 1 Replies View RelatedExcel 2007 :: Import Data From Worksheet Into Another And Group Data Into Sections
Mar 2, 2012I want to be able to run a macro that will export some of the data from a worksheet called Generated Report into another worksheet called Matrix. The data will be taken Generated REport and grouped into sections of the worksheet. These sections will be defined by the BRID value.
The Data that i want to export will be from the first 5 columns within the Generated Report(note there are other columns which data is not required from).
The following columns will be exported from Generated Report -
BRID,Requirement, Bug Description, Bug ID and Alt Bug ID.
BRIDRequirement Bug DescriptionBUG ID Alt Bug ID
PQ115 PQ115-Login Bug Description text xihllloloj43712 123
PQ116PQ116-Landing Bug Description text ghghghgoot 43713 126
For Matrix
The data will be imported into Matrix worksheet into the following columns as part of the row headings
- Requirement, Description, Bug ID and VF Bug ID.There will also be other columns (outlined below) which i will use to manually enter data
The data will be grouped and imported into the relevant section by its BRID eg PQ115, PQ116 etc.
I want each section (marked by BRID number)to be separated by row headings.
These row headings are to be generated after populating all data within each BRID number.
When the data has been populated in each row there is a column called 'Status' which contains drop down list boxes which can be assigned.
The default status will be set to 'To Do'. The complete row headings are displayed with Output from Generated Report underneath
BRID Priority Requirment Description BugID Alt Bug ID PC UpdateNotes Actual Result Type of DEfect Test Status Tested by
PQ115 PQ115-Login Bug Description text xihllloloj43712 123 To Do
BRID Priority Requirment Description BugID Alt Bug ID QC UpdateNotes Actual Result Type of DEfect Test Status Tested by
PQ116 PQ116-Landing Bug Description text ghghghgoot43713 126 To Do
So far the macro that i have been using allows me to locate the data for each specific BRID in Generated Report and copy into the corresponding BRID section within the Matrix. I have to repeat this step for each new BRID and i want to be able to whole automate this process.