OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Survey Responses???

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
NiceGuy88
General User
General User


Joined: 05 May 2012
Posts: 7

PostPosted: Tue May 08, 2012 1:55 am    Post subject: Survey Responses??? Reply with quote

I've made a survey and the results are automatically added to a spreadsheet which I import(copy/paste) to open office to do all my formulas on.

One question is a Check box question with multiple answers. It posts these into a single cell split up with a comma, for example:

option1, option2, option3

FOR THE LIFE OF ME I cant get it to add up the individual 'options' and list them in another cell:

option1 =
option2 =
option3 =

I keep trying COUNTIF formulas but only seems to work if there is only one option in the cell!

Can someone please give me a formula, or a macro to do this....I would be forever in your debt!

Thanks
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue May 08, 2012 2:33 am    Post subject: Reply with quote

Are you running out of cells? There are a billion cells on every sheet.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
NiceGuy88
General User
General User


Joined: 05 May 2012
Posts: 7

PostPosted: Tue May 08, 2012 2:47 am    Post subject: re:Survey Responses??? Reply with quote

That's not the issue. The data is recorded into the cell automatically from the survey so I don't want to change how it is otherwise it wont match up with the survey. I just want to find out how many people picked option 2 for example.

A1 - option1, option2, option3
A2 - option2
A3 - option1, option3

WITHOUT changing whats in column A.....how can I add them up?

Thanks[/list]
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue May 08, 2012 5:18 am    Post subject: Reply with quote

Either you design your input properly or you need some sophisticated formulas. Rubbish in, rubbish out.

Quote:
I keep trying COUNTIF formulas but only seems to work if there is only one option in the cell!

Use a regular expression or turn off the "match whole cell option"
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Tue May 08, 2012 5:35 am    Post subject: Reply with quote

When this option does not have a tick in the checkbox...
Search criteria = and <> must apply to whole cells
The COUNTIF function counts all cells that contain the option being counted regardless of other text in the cells.
If A1:A3 has...
option1, option2, option3
option2
option1, option3
=COUNTIF(A1:A3;"option1") returns 2.
When the above option has a tick in the checkbox the same formula would return 0 because none of the three cells has "option1" only.

To get to the option go Tools|Options...|OpenOffice.org Calc|Calculate

Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue May 08, 2012 6:18 am    Post subject: Reply with quote

http://www.mediafire.com/file/yzl5i23hc003yzb/PseudoForm.ods
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group