Create A Formula To Average Survey Results Per Instructor?

May 28, 2014

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.

View 2 Replies


ADVERTISEMENT

Survey Results In Pivot Table

Jul 25, 2007

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).

View 2 Replies View Related

Survey Results Table Lookup Errors?

Jun 26, 2014

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.

View 1 Replies View Related

Excel 2003 :: Tabulating Survey Results

May 27, 2014

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...

View 3 Replies View Related

Array Formula To Create List Of Filtered Results

May 15, 2014

I have a sheet of data that I can filter (e.g. only show male pupils).

On the next sheet I want to display the list of filtered results with no gaps.

As the user will be able to select which column of data to show from the first sheet I am referencing it as follows:

INDIRECT("'Progress Matrix'!"&ADDRESS(MATCH($B$6, 'Progress Matrix'!$A:$A, 0)+1,
MATCH(C$6, 'Progress Matrix'!$1:$1, 0))&":"&ADDRESS(300, MATCH(C$6, 'Progress Matrix'!$1:$1, 0))))

(However this formula no longer seems to work as I think I changed something).

I've tried to incorporate

SUBTOTAL(3,OFFSET(A2,ROW(A2:A100)-ROW(A2),0))

to only show filtered results but with no success.

Progress Tracker.xlsm

View 6 Replies View Related

Create Formula That Creates Average But Excludes Top 10% And Bottom 10% Values?

May 5, 2014

how to create a formula that creates an average but excludes the top 10% and bottom 10% values?

View 5 Replies View Related

Average Multiple Results For The Lookup

Dec 11, 2008

I have a lookup that needs to average multiple results for the lookup... columns
Q to W

Looks like it is only returning the first found value.

View 3 Replies View Related

Change Formula To The Results Of The Formula For Cells That Have Results

May 9, 2006

I have a range that has formulas that are based on other fcells outside the range.

What I want to do is, if the cell has a value to remove the formula and paste the results. If the filed is blank (no results from the formula) to leave the formula in place.

There could be a marco to run when this process is needed.

example attached

View 9 Replies View Related

Lookup A List Of Values & Average Results

Jan 25, 2007

I have a simple one today but on a time crunch so I don't have time to go through the book.

I am trying to hlookup a list of values from a table and average the results. The values are setup horizontally.

View 9 Replies View Related

Results Show Results Of Formula, But Should Be Blank

Jul 27, 2006

I am trying to create formula that will show overtime worked in a given day. The code I am using is a simple one ([ cell - 8], for hours worked). The problem is when the time cells are blank/not used it shows a -8 in the cell. what I need to do to create a code that will eliminate the -8 from showing. The cell its self is taking the result from another cell with a formula and then subtracting 8 from the result of the formula in the other cell.

View 2 Replies View Related

Create List Of Calculated Results

Nov 14, 2006

I have created a formula that returns 2 dates and the number of days that lie between those dates (less weekends)..

eg.

Friday 22/12/06 Monday 8/1/07 10 days

What I would like to do, but can't work out how, is create a button (and a function or macro or whatever) that transfers that data to another sheet, adding to columns of previously calculated data.

View 9 Replies View Related

Create A List Of Results Based On Criteria

Sep 21, 2012

Col A has list of order numbers, col D has list of corrisponding order status (open , closed etc etc)

I simply want a formula to display a list of all the 'Open' order numbers in a separate sheet.

Obviously to size of the list will vary depending on how many open orders there are.

View 1 Replies View Related

Create Dropdown Menu That Results In Values?

Aug 12, 2013

I need cell d4 to display the number in column b next to the applicable insurance company when the insurance company is selected from the drop down menu in cell c4. How do I do this?

View 4 Replies View Related

Create List Of Results With Lookup Function?

Mar 16, 2012

I'm working on an invoice generator, and I want to add a "lookup by phone number" feature. I've got an invoice database with invoice info stored horizontally for past invoices. On a separate sheet, I want to be able to type in the phone number in range ("O11:Q11") and have a list of invoice numbers and dates matching that phone number appear in Columns R and S respectively.

Here is some workbook info:

Invoice generator is in 'phonelookup'
Invoice database is in 'invoice database' and the table is called CompInvoices
In CompInvoices, Invoice numbers are col1, dates are col2, and phone numbers are col7.

View 1 Replies View Related

Create Pie Chart From Table Summary Results

Jul 2, 2009

Attached is five months of dummy sales data for eight products. My objective is to filter this data with date ranges (using Column A in Data sheet), and have the corresponding pie slices (shown in Pie Chart Sheet) be the summation of the Products grouped by their designated letter. Currently, the result I am getting is 6 pie slices for "Product A" and this is wrong. I want to see "Product A" show up only once in the LEGEND and only as one aggregated pie slice. The same holds true for the rest of the Product Letters.

I want to avoid using Pivot Tables. My objective is to keep my layout clean and simple. I would like to know if anyone out there could help me figure out a formula that will automatically catagorize all of the resulting filtered data by the Product Letter they share. Once the formula categorizes or groups together all of the resulting filtered data by Product Letter, I would like the formula to calculate a summation for each Product Letter category. After the formula finishes the summation task I would like the formula to feed these results to a Pie Chart. The resulting chart I would like to see will show individual pie slices for each Product Letter resulting from the date range filtering operation performed on Column A. The resulting letters can be anywhere from "A" to "H". My goal is to only have one individual pie slice representing each of the Product Letters resulting from a Date range filter. I thought such a task would be as easy as baking a pie, but it's turned out to be beyond my Excel skills.

View 9 Replies View Related

How To Create New Excel Sheet From Results Sorted From Table

Sep 12, 2013

I have a simple sheet consisting of about 900 rows and 4 columns. The cells in column D are filled with numbers. I converted column D to a table and sorted to show only numbers over 10,000. I can now see the results including the cell values for rows A,B,C, and D. I would like to delete the rest of the unused rows, or create a new sheet with my results. Right now, some row numbers are not visible, so the visible rows go from 4, 27, 45, 48, 52, etc. When I try to copy and paste what I see in my results into a new sheet, the entire original sheet still pastes.

My final goal is to just have a sheet with 4 columns, and the corresponding values as long as the values in column D are over 10000. I do not need a table, just a regular sheet.

View 3 Replies View Related

How To Create Month To Date Average

Aug 20, 2008

I'm having difficulty understanding this concept, more so the formula.

So to figure out the MTD for Day 1,2,3,4, how would it be?

I want each day to have a MTD
etc.

Day 1: 33,966,611.42
Day 2: 33,966,611.42
Day 3: 33,966,611.42
Day 4: 34,074,098.36

View 9 Replies View Related

Formula =AVERAGE(B16:L16) To Give The Average

Jan 7, 2008

I'm using the formula =AVERAGE(B16:L16) to give me the average.

However I have a couple of problems with this. Firstly I would like to exclude the value zero from the average. Secondly to also ignore the lowest and highest values.

Example, if the values in the cells are 0,1,2,3,4,5,6,7,8,9,10 then the current result shows 5, by ignoring the 0 and lowest value 1 and highest value 10 the average should be 4.5.

View 9 Replies View Related

Create Average Based On Grouping Of 3 Columns

Mar 22, 2013

If my spreadsheet has columns A-H.

A is Date
B is Room Number
C is Location
D,E,F all have numbers results in them.
G is the average of D,E,F for each person
H is group average.

If I want to define a group average in column H where it calculates the average of a group that attended the same location, date and room, for each group that attended would that be possible?

So For Example (Columns DEF left out for clarity)

Date
Rm
Location
G Inidivual Average

[Code]....

So because room number changed it calculated as a different group. Is this possible?

View 3 Replies View Related

Using A Percentage Weighting To Create An Average Score

May 26, 2009

I have 6 different sections of questions, each section having a final average score between 1 and 5.

I would like to somehow create a percentage weighting on each of these sections and then have a final score accross all section, taking into account this weighting?

View 9 Replies View Related

Create Rolling 30 Day Average Based On Dates In Heading

Apr 16, 2014

I am trying to create a rolling 30 day average based on a date in a particular cell. I have 62 columns that I am looking at. We can call A1 7/1/14 and the 62nd column 8/31/14 for simplicity sake. I want to grab 30 values in row 2 as these columns are filled in daily so 8/1/14 takes the average of 7/2/14-8/31. On 8/5, I want 7/6-8/4 so on and so forth. The average that I need has to include at least one averageif stating that only nonzero "<>0" figures should be tabulated (just have business day transaction data but need all dates listed to tabulate 30 days). The range of dates will be fixed every month and info copied to tabulate on the first day.

View 1 Replies View Related

How Do I Create A Running Average That Will Only Calculate The Averages In % Each Month

Mar 20, 2008

I need to do the following and can't figure it out. How do I create a running average that will only calculate the averages in % each month. Example: Opt 1 for Jan, Feb, Mar =1 each= 3 total = 100%; OPt 2 for Jan, Feb, Mar =1,0,1= 2 = 66%; Opt 3 for Jan, Feb, Mar = 0, 0, 1 = 1 total = 33%. My problems is I want monthly running average that shows the yearly percentage up to date but only for the months there is a value 1 or 0. How can this be done because the way I have it now, those % are being divided by 12 and that isn't the correct %

View 9 Replies View Related

Analyzing S Survey Form

Apr 20, 2013

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.

View 2 Replies View Related

Survey Form With More Than 2 Options

Apr 10, 2014

I 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

View 5 Replies View Related

Rank Questions In A Survey

Nov 7, 2006

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. ...

View 10 Replies View Related

Pivot Table / Create Average Of Proportions (Custom Calculation)

Aug 2, 2013

I am trying to find expected proportion of code per country by looking at current values. I have a list of countries and associated classifications (0-5) with counts, similar to as follows:

Country
code
count

USA
1
65465

USA
2
54651

USA
3
65411

[code]...

I am interested in creating a pivot table with the average of each code as a proportion of each country. The final table would be expected proportion of codes. The pivot table for this set would look like this:

Row Labels
Average

1
5.4%

2
3.9%

3
4.7%

[code]...

Mean per code of the proportion of code per country

View 1 Replies View Related

Survey Form - How To Open Another Thread

Jan 2, 2013

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 Related

Extract Data From Survey Checkbox

Dec 11, 2013

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 Related

Survey Collection - Up / Down Spin Buttons

Jul 10, 2014

I'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.

View 6 Replies View Related

Analyzing Survey Data In Excel

Apr 6, 2010

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 Related







Copyrights 2005-15 www.BigResource.com, All rights reserved