I 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.
After 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 View RelatedI 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
how 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 RelatedI 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.
I 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.
I 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?
I 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!
I 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
I 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. ...
I 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 RelatedI'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.
I 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)
I 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.
I 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.
I 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).
An 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.
Is 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...
I 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.
I 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.
I 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.
I 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.
I 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 RelatedI'm missing something in my UserForm initialization code. If I fill the form out once and click 'OK' (run the code to put the form data into a sheet), when I go back into the form all the old info is still there. If I then click 'Cancel' (Unload Me) and reopen the form, the old data is cleared out. What am I missing to make it clear it out the first time?
View 2 Replies View RelatedI'm having trouble trying to come up with a way to insert data fields into a spreadsheet form. I have a travel authorization form that I would like to have automatically fill in the required fields based on typing in a name. i.e. I would type in an employees name, and it would automatically fill-in the correct address, etc for that employee. I have attached a spreadsheet that contains one sheet as the form, and another sheet containing the employee data. I know nothing about VBA, but I have a feeling that is where I need to go.
View 3 Replies View RelatedI am needing to create a form that exports data (a quote) to an Excel Db (table) and is then able to recall the data back into the form. (the default form in excel does this and I want to copy that.)
Once the data is called back in, I can then export it to another Table to show that the quote has been approved and will be used.
I am having trouble with the VBA coding that copies the inputted quote in Cell C2 (the reference for the quote number) of the "Form" sheet and looks it up in the "Database" sheet. I have tried several variations of code, but nothing works so far.
-SS
Sub RecallQuote()
'
' RecallQuote Macro
'
Sheets("Form").Select
Range("C2").Select 'this is the cell that holds the quote number to look up from the table
[Code] ......
I have a spreadsheet I use to keep track of weekly sales patterns and use for estimating the amount of a product I would need to order taking into account what I would expect to sell in a given week and what stock I have at present. On the example I've attached, I show where I enter my storeroom count figures, which are organised by supplier and the position in which a particular product appears on the supplier's order form. I have a page which lists the orders by suppliers and which are used to place the orders by e-mail or telephone.
At present I have each supplier section of the order form directly linked to a cell on the storeroom count as per columns K to M on the attached file. However, this means that as products are de-listed by suppliers and extra products become available, I have to edit the formula in each cell as the products now appear in a different position on the storeroom count and may otherwise end up on the order form for a different supplier. I would like to set it up so that I just have to select the supplier name and the table below will automatically fill with the required info, in order of the position they appear on the supplier's form. I'm struggling to combine vlookup and hlookup. Is there a way to do it or do I need to rethink?
I'm trying to pull up a second form from a command button within a form. There's a command button in a sheet to open the first form (frmOrderInput.) Then there's another command button in that form to open the second form (frmPriceInput.)
The Module to open the first is this:
I have got a userform that fits my 24inch monitor screen perfectly, however it doesn't fit other screens. How do I get windows style scrollbars added to the form so people can use these to see the whole form?
View 3 Replies View Relatedfind the attached workbook
I have a Database and user form, in the user form i have a field named “Vehicle No” this is a combo box from which a user needs to select the Vehicle numbers, and all these are working fine now, I need your help in the following:
When user selects the second field named "Select Vendor name" i need a pop up window which shows all the Vehicles belongs to the vendor which they have selected, and with the popup window user selects the vehicle number then the Vehicle number combo box should be filled.
Currently users have to select by scrolling through Combo box which takes long time and difficult to find by scrolling.
