Free Microsoft Excel 2013 Quick Reference

# Survey Tabulation and Analysis

WIN XP
Excel XP
Win Vista (64)
Excel 7

Hi

I have a small amount of knowledge of excel and I am trying to tabulate and analyse data from a survey.

1. 26 questions
2. Broken into 5 sections
3. Some simple Yes No
4. Some multiple choice with only 1 choice allowed
5. Some multiple choice with up to 3 choices allowed..
6. The choices at present are represented in a,b,c etc.
7. I have 100 returned survey sheets. Therefore 100 sets of unique data.

I need to be able to enter the information and then have both totals and percentiles.

I have used COUNT statements in other spreadsheets, but this appears to be un-suitable for my present needs.

Q1. In some questions I have up to 9 choices with 3 requested, how do I have excel calculate the 1st choice, 2nd choice, 3rd choice and show them ?

Q2. Should I use the a,b,c format or numerical.

Q3. Will I best be served by putting the questions/answer's into Pivot Tables (whatever they are) ?

Thank you for any help the members of the forum can give, and I am sure I will have many more as I work my way through.

## Related Results

### Survey Tabulation

I am going to have about 7000 surveys filled out and the data entered in
excel. Is excel the best program to use to tabulate the results? The survey
will contain demographic questions such as ages and gender of children, any
special needs children in the home (y/n), plan on moving in the next three
years (y/n). I just don't know how to best set up the spreadsheet. Thanks
for any suggestions.

### Tabulating and analyzing survey data

I have about 80 comleted surveys with 75 items per
survey. Most items are 5 point scales , strongly agree to
strrongly disagree, plus age gendre etc. Ihave XP with
office 2002. what is a good approach to tallying and
cross referencing this info?

### Can't seem to find way to have easy data entry AND analysis - idea

Each day, 3-10 different foods (from a selection of >100) are fed and the
weights recorded. My spreadsheet is currently set up with the following
columns:

Date|Food1Name|Food1Amount|Food2Name|Food2Amount|F ood3Name|Food3Amount|...etc...

The way I have it set up, though, it isn't simple to get a sum of the total
amount of, for example, apples fed in a week/month/etc., because they may be
listed under "Food1" on one day, but "Food2" on another day.

I COULD have a column for each foodstuff, but that then makes data entry a
pain for users, who then have to scroll left and right to find the
(hopefully) correct column.

I've looked at pivottables, sumif, sumproduct, and vlookup-type solutions,
but none of them seem to be quite what I need or so complex that I wouldn't
be able to show others easily.

Is there a different way to approach this that would allow easy data entry
AND easy (simple) data analysis?

Thanks,
Heidi

### Excel 2003 with SharePoint 2003 and Analysis Services 2005

Hi,

I'm having this weird problem that a downloaded Excel 2003 NL file from
SharePoint NL can't update a pivot based on a cube in SSAS US.

The error message I get is: "XML for Analysis parser: The LocaleIdentifier
property is not overwritable and cannot be assigned a new value"

The SP-Services run an US-Server and the SSAS same story.

However! when I set the regional settiings to English, all is well but
English formatted dates and figures troughout the office is not acceptable.

Other solutions (but again not really acceptable):
- use mozilla (who downloads the excel-file and then opens it and not the
shared document solutions)
- open the excelsheet in IE (drawback here is losing some default excel
functionality)
- don't use SharePoint but the good old filesystem

Is this a BUG MS? I came to this conclusion because only when opening via SP
this error occurs. Any suggestions are welcome...
--
best regards,

Leon

### BAM and cube analysis from a user machine

I have some cubes in Analysis Services, I created them
using BAM. From the server containing my SQL server and
analysis services I have no problem connecting using
localhost to access my cubes using excel add-in, cube
analysis. But when I try to connect from another user
machine I can't, I tried setting up an ODBC connection
them, I do have all the security rights for seeing them,
I created the server. I have windows xp on the user
machine with excel 2003 sp1. Windows Server 2000 with
excel 2003 on the server machine.

### Excel GETPIVOTDATA and Analysis Services

I have a problem with get GETPIVOTDATA, and getting data from an Analysis
Services cube.

When you drill down quite far, GETPIVOTDATA starts to display "#N/A", this
seems to be because GETPIVOTDATA is cutting the 'variables' short.

e.g. GETPIVOTDATA has the following:
=GETPIVOTDATA("[Measures].[Value]",\$A\$3,"[IS Structure TEST]","[IS Structure
TEST].[All IS Structure].[RETAINED INCOME].[NET INCOME BEFORE TAXATION AND
AFTER INDIRECT COST].[NET INCOME BEFORE TAXATION AND BEFORE INDIRECT
COST].[OPERATING INCOME].[NON-INTEREST INCOME].[CHEQUE ACCOUNT
FEES].[Management fees: Cheque a")

When it should have
=GETPIVOTDATA("[Measures].[Value]",\$A\$3,"[IS Structure TEST]","[IS Structure
TEST].[All IS Structure].[RETAINED INCOME].[NET INCOME BEFORE TAXATION AND
AFTER INDIRECT COST].[NET INCOME BEFORE TAXATION AND BEFORE INDIRECT
COST].[OPERATING INCOME].[NON-INTEREST INCOME].[CHEQUE ACCOUNT
FEES].[Management fees: Cheque accounts]")

Is there anyway around this? Also, it would help if I can set it up to pass
the member keys, and not the member names, as the keys are obviously a lot
shorter.

Any help would be appreciated.

### Excel 2003 with SharePoint 2003 and Analysis Services 2005

Hi,

I'm having this weird problem that a downloaded Excel 2003 NL file from
SharePoint NL can't update a pivot based on a cube in SSAS US.

The error message I get is: "XML for Analysis parser: The LocaleIdentifier
property is not overwritable and cannot be assigned a new value"

The SP-Services run an US-Server and the SSAS same story.

However! when I set the regional settiings to English, all is well but
English formatted dates and figures troughout the office is not acceptable.

Other solutions (but again not really acceptable):
- use mozilla (who downloads the excel-file and then opens it and not the
shared document solutions)
- open the excelsheet in IE (drawback here is losing some default excel
functionality)
- don't use SharePoint but the good old filesystem

Is this a BUG MS? I came to this conclusion because only when opening via SP
this error occurs. Any suggestions are welcome...
--
best regards,

Leon

### HELP: survey data collection & analysis

I need urgent help and am new to Excel. I've got 5 excel surveys which is
similar to:

http://www.surveymonkey.com/ExportEx...rtExamples.zip

I am using SurveyMonkey.com to do 5 surveys (which are all exactly the
same), however it's separated for 5 societies. Results can be
file). So there are 5 of those ExportExamples.zip (above), one for each
society am surveying. How can I combine the 5 Condensed.csv files so it's
one big file?

I need the Condensed in one big file. Then I want to make Excel, or any
program, to randomly chose 2 winners/users from the combined-list. (It's for
a draw of 2 winners). Is this possible in Excel, or some other program? How
can I do this?

Many thanks
Kev

### Macros for Survey Result Tracker

Hello,
Can someone outthere help?
I have been tasked to handle a Survey team and track the survey resuts.
It has 3 questions with 1 answer possible from 3 (number) choices. Need to add up responses from each question for each choice selected.
I extract the result daily and it gives me an average of 30-40 responses per day.
I want to enter first returned survey through a user form, push a button on the form and have the responses go to another worksheet, then user form is cleared. Next returned survey is entered, push a button and those answers are then added to the first and so on.
Then I want it to automatically produce a report showing the number of surveys returned and the number of responses for each answer.
I hope I've explained this adequately.Attached is the sample data

### Data input and analysis/Macro

I 've just started working with macros...

Using buttons click to activate Excel's built in data input form and add data in the form of Data>form title box. I want to be able to see only the title box from Data>form on a new worksheet. When I Click on the button ot brings me the titla box and the worksheet that has My list which I don't want to see. Anyone's help will be appreciated.

Dila 24

### Survey Results In Pivot Table

Hi all

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 with some of the questions if it helps. 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).

Your input would be appreciated before I jump in and start doing everything manually .

Thanks
Averil

### Compiling survey results

Survey has 20 questions with 1 answer possible from 5 choices. Need to add up responses from each question for each choice selected. I want to enter first returned survey through a user form, push a button on the form and have the responses go to another worksheet, then user form is cleared. Next returned survey is entered, push a button and those answers are then added to the first. 3rd survey is returned, etc. for all returned surveys. Then I want it to automatically produce a report showing the number of surveys returned and the number of responses for each answer. I hope I've explained this adequately. Thanks for any and all assistance. I can create the user form, I just don't know how to do the rest of it.

### Install Analysis Toolpak via VBA

Hi all

I'm having a problem with the install of the: Analysis Toolpak and Analysis Toolpak - VBA.

If the ATP and ATP - VBA are already installed then there is no problem!

If the add ins are not installed, it installs them but it errors when I then want to select a certain sheet. (errors also if I try to put a value in a protected cell through VBA even when the protection is through user interface only, but have made this example small for easy understanding)

Here is an example of the code:
Code:
```Private Sub Workbook_Open()

AddIns("Analysis ToolPak - VBA").Installed = True
Sheets("Sheet2").Select

End Sub```
I get the error message "Select method of worksheet failed."

If I then close the workbook and reopen it, everything is fine as the add ins are installed. The reason I need a solution to this is that the wb is used on many pc's that don't have the addins installed automatically.

I can get round the problem by closing the wb automatically and informing the user to reopen due to addins being installed, but that seems messy.

Is there a way around this?

Cheers

Kev

### Calculating survey data

Hello,

I'm doing a user survey spreadsheet and want to know how many people from different groups are satisfied. Its based on a Rating scale.

The ratings are given in Column AK and Gender is in Column AQ. Males are Code 1 and females are Code 2. Records cover rows 13 to 613.

I need a formula that adds up how many males gave a rating of between 0 and 4. I also need a similar formula that adds up how many males gave a rating of 5.

Any suggestions would be greatly appreciated.

Dean

### Data Selection and Analysis

I have a problem with a report generator that I'm trying to produce.

Requirements

- Import data from an AS/400

- Make selections over data,
(3 different fields, including a date field, between 2 dates, and 2 other fields which have 6 possible options each)

- Create a pivot table over the data

- Update a template with the data

I'm having problems making selections over the data, I've tried pushing variables into the sql statement to make the selections at data import stage.

Unfortunately, this has turned out to be overly complicated, when i try to record the process of using microsoft query, the last part of the code has been chopped off and therefore returns a syntax error.

I tried to filter the data and then run a pivot table over the filtered records but the pivot table picks up all records, not just the filtered records.

I've also tried to make my selections at pivot table level but having problems restricting to the data to fall between 2 dates.

I wonder if anyone can give me a push in the right direction to make my selections. Please ask if you require any further info.

Thanks

Matt

### Survey Feedbacks and suggestion

Hi Bill

Hope this is not the wrong place to post..

Been reviewing the chapters of your latest book (yet to be available) and yes it would make a handy reference book beside the computer or as a gift. One thing though, if your free gift could have options of the latest version of of the 2200 VBA samples--that would be really nice.

I bought your offer bundle few years back when it was 1600 VBA samples and it has been an invaluable tool.

Been thinking but \$49 for an additional 800 VBA is a bit steep. Maybe other readers have similar thoughts too and US dollars is expensive as many readers like me will find that to be.

On the side- offering mix and match bundles for people who have bought from your online store before would be interesting. E.g. I have the 1600 VBA CD, Mr Excel on Excel, the forum help CD and Guerilla Data Analyis.

but looking at the newer books and the updated CDs....just a thought

### Verify Analysis ToolPak with VBA

I created a workbook that requires the Analysis ToolPak and Analysis
ToolPak-VBA add-ins to function. I've discovered that many of the people I
gave the workbook to do not have those add-ins enabled, so they get the
#NAME? error.

Is there a way to automatically check for the Analysis ToolPak? I am
thinking that if the ToolPak is not enabled, a message box would pop up
telling the user what the problem is, and how to install the ToolPak.

Thanks!

### Editing and analysis of cells in multiple worksheets AND workbooks

My work currently has me creating workbooks with up to 12 worksheets in each
for every day of trade. I cannot find anywhere if it is possible edit
formulas AND analyse multiple worksheets in multiple workbooks whether they
are open or closed. Is this possible, as it currently takes me forever and a
day to open every workbook since the beginning of the Aussie financial year
(July 2006) and edit them and analyse one by one.

### Financial Modeling and Analysis

Anyone know of a good book that addresses the specific aspects of
Financial Analysis in an Excel based framework?

Ron

--
Ron Jeremy
------------------------------------------------------------------------
Ron Jeremy's Profile: http://www.excelforum.com/member.php...o&userid=34588

### Count items when specific text and date criteria are met

Trying to summarize survey data and need to report the number of
specific text responses given before a date and report those given
on/after a date. The date and response information is contained in one
worksheet while the summary is displayed in another worksheet within
the same workbook (Excel 2000, Win XP). I've named the data ranges in
an attempt to make this easier. For example:

"TDate" "TQ7"
10/11/2004 q7= very easy
11/12/2005 q7= fairly easy
12/14/2004 q7= difficult

I'm trying this formula to get a count of all the question 7 responses
on/after the date:
=SUMPRODUCT(("Tdate">=DATEVALUE("01/25/2005"))*("TQ7"""))

I'm trying this formula to count specific responses by date:
=SUMPRODUCT(("Tdate">=DATEVALUE("01/25/2005"))*(COUNTIF(TQ7,"*very
easy"))

To make things even stranger, I've "created" the date for "TDate" by
calculating information from 3 other columns using the following
formula (the date information is buried in a field with lots of other
stuff so this is my attempt to isolate it):
=DATE(D2,B2,C2)

Not sure if this date approach causes a problem with calculations or if
my formulas are just completely off base.

Help greatly appreciated! Trish

### How do I configure Analysis Services for Excel 2003 users?

Hi,

I have been preparing a set of cubes in Analysis Services 2005 to be viewed
using an Excel 2003 front end and am struggling with deployment issues.
Excel reports have been designed as pivot tables based on AS cubes, linking
through the OLEDB 9.0 driver for Analysis Services. The pivots run perfectly
on the machine they were designed on (which is also the server). However
when attempting to refresh or alter the pivots on any other machine this
returns the error 'Initialization of the data source failed.'

All these other computers are set up with SQL Server developer edition and
can all access and alter the cubes via BIDS. They are also all using the
OLEDB AS 9.0 driver and can create the OLAP cube data source through Excel
however they fail at the final point when the data is trying to be retrieved,
returning the error above.

Using VBA to retrieve the connection string for a pivot shows it is, by
default:

OLEDB;Provider=MSOLAP.3; Cache Authentication=False;
Persist Security Info=True; User ID="";
Initial Catalog=OpsReporting; DataSource=WLDN0163639;
ImpersonationLevel=Impersonate; Location=WLDN0163639;
Auto Synch Period=20000; Default Isolation Mode=0;
Default MDX Visual Mode=0;MDX Compatibility=0;MDX Unique Name Style=0;Non
Empty Threshold=0;SQLQueryMode=Calculated;Safety Options=2;Secured Cell
Value=0;SOURCE_DSN_SUFFIX="Prompt=CompleteRequired ;Window
Handle=0x702C6;";SQL Compatibility=0;Compression Level=0;Real Time
Olap=False;Packet Size=4096

with the server name being WLDN0163639 and the AS database being
OpsReporting.

Surely it must be that the connection string is incorrect but I seem to have
a shortage of understanding as to what each of the properties within the
connection string do and what all potential options are as I have never had
to alter the defaults till now. The most obvious candidate is the UserID,
however all the alternatives I have tried (e.g. adding â€śIntegrated
Security=SSPIâ€ť) have failed.

We are using Windows Authentication (all our computers are on Windows and
none are connecting from outside the company) but I am not clear on how the
security works remotely with AS and especially what alternatives there are
for ways of connecting (i.e. types of connection strings). Logon accounts
for SQL Server and Analysis Services are both set to Local System as this
seems to be the only way to get them to work. I am uncertain how to tell if
AS is using TCP/IP or Named Pipes (or something else?); SQL Server is using
TCP/IP in all the DSNs which works fine but I have seen comments about AS2005
having problems using http. I am not sure how to go about changing this in
AS if it is an issue and am also unsure about using IIS. I have looked at a
whole range of possible solutions (mentioned in other threads) such as
changing ports but they all seem to be a shot in the dark; I think I may be
missing something simpler here?

Appreciate any assistance.

TIA,

Rob

### Pivot Table for survey data w/ questions as Rows & poss answrs as

Hi,

I'm trying to use a Pivot Table to present some survey results, and
could use a point in the right direction.

Details:
--------
- The first question is "what department do you work for?"

- The rest of the questions all have the same possible answers
(agree, strongly, disagree, strongly disagree).

- The questions are the column headings in the source data set.

In the Pivot Table, I'm looking to:
-----------------------------------
- have the rows be the individual questions (col headings from data set)
- have the columns be the four possible answer (col data from data set)
- have the data be the occurence of the possible answers for each of the
columns
- be able to split out and/or compare the answers by department.

Is this possible using Pivot Tables?

I tried having the Department be the PT's "Page Field" and various
combinations of placing the other fields in the Row Fields, Column
Fields & Data Fields, but to no avail.

It doesn't seem to want to let me have mulitple rows of questions; instead
it seems to want to make me have separate Pivot Table's for each question.

Could anyone give me a pointer or two in the right direction?

Thanks in advance for any help,

Ben

### Pivot Table for survey results with set of possible answers in PT's Rows Field?

Hi,

I'm trying to use a Pivot Table to present some survey results, and
could use a point in the right direction.

Details:

- The first question is "what department do you work for?"
- The rest of the questions all have the same options as answers
(agree, strongly, disagree, strongly disagree).
- The questions are the column headings in the source data set.

I'm looking to:
- have the columns be the four options, and the rows be the individual
questions.
- be able to split out and/or compare the answers by department.

Is this possible using Pivot Tables?

I tried having the Department be the "Page Field" and various
combinations of placing the other fields in the Row Fields, Column
Fields & Data Fields, but to no avail.

Could anyone give me a pointer or two in the right direction?

Thanks in advance for any help,

Ben
Ben Nardone
Pathfinder International, www.pathfind.org
617-924-7200 x605

### Mix/Max/Avg Help based on dynamic ranges

WinXP Excel 2k3

utilization (print/scan/fax/copy) imported all as text from web app.

Linked to headcount data for locn sizes based on location# in survey via
this formula in column range \$I\$7:\$I\$151...
=VLOOKUP(Analysis!G34&"*",Area!\$E\$5:\$F\$787,2,FALSE )
....and now want to derive some stats from the answers in a summary tab
(Matrix!).

Matrix!\$B\$5:\$B\$8 are arbitrary ranges which will change until a set range is
deemed optimal for building device profiles based on locn size and device
volume, and so formulas need to recalc each time ranges change.
HC Range
1-10
11-30
31-50
50-

All the other calcs on Matrix! are based on HC Range col, because they go
fetch the info in Analysis! based on whether or not that locn fits the range
size on this column. This is the dynamic range part. Bernie D. was kind
enough to help with counting the # of locns which fit the range on each cell;
I've then been able to modify his formula in Matrix!\$G5:\$G8...
=COUNTIF(Analysis!\$I\$7:\$I\$151,">="&VALUE(LEFT(Matr ix!\$B5,FIND("-",Matrix!\$B5)-1)))-COUNTIF(Analysis!\$I\$7:\$I\$151,">"&VALUE(MID(Matrix! \$B5,FIND("-",Matrix!\$B5)+1,LEN(Matrix!\$B5))))
....to give me average # of devices per device type by replacing countif with
sumif function and dividing by the results of the countif formula...
=(SUMIF(Analysis!\$I\$7:\$I\$151,">="&VALUE(LEFT(Matri x!\$B5,FIND("-",Matrix!\$B5)-1)),Analysis!\$V\$7:\$V\$151)-SUMIF(Analysis!\$I\$7:\$I\$151,">"&VALUE(MID(Matrix!\$B 5,FIND("-",Matrix!\$B5)+1,LEN(Matrix!\$B5))),Analysis!\$V\$7:\$V \$151))/Matrix!\$G5

I thought I would be able to use this methodology throughout but...Now I'm
trying to figure out device volumes. Problem is, I've hit a wall trying to
use the min, max and avg within the range condition. The Analysis! tab needs
to also be free to be sorted and resorted for other purposes w/o affecting
the results of the Matrix!. So I want each formula to give me the Min, Max
and Avg of all records in Analysis!\$7:\$151 that fall headcountwise within the
range specified, AND I need each formula to ignore zero and blank cells.
Volumes are located as follows:
Print Volume: Analysis!\$AH\$7:\$AH\$151
Copy Volume: Analysis!\$CQ\$7:\$CQAH\$151
....etc, I can plug in the others once I have a working formula

I hope I gave enough info and didn't confuse things. I'll be more than happy
to share the file if I'm told where to send it. I've been working on it for 2
days now. I tried this syntax which in my feeble mind is what I want but
excel won't accept it, it shades [\$I\$151">="] as an error
=min(if(and(Analysis!\$I\$7:\$I\$151">="&VALUE(LEFT(Ma trix!\$B5,FIND("-",Matrix!\$B5)-1)),Analysis!\$I\$7:\$I\$151">"&VALUE(MID(Matrix!\$B5,F IND("-",Matrix!\$B5)+1,LEN(Matrix!\$B5)))),Analysis!\$AH\$7: \$AH\$151))

Hopefully, whatever I get from this post I can also use for MAX and AVERAGE
functions.

Thank you all who managed to read the whole thing before falling asleep. :-)
When you wake up, I would LUUUUUV some help!

--
Hile