Empirisoft Support

    Welcome to Empirisoft Support
Results 1 to 28 of 28

Thread: Generating and - saving - random values from responses.xls files

  1. #1
    Join Date
    May 2009
    Posts
    15

    Generating and - saving - random values from responses.xls files

    [note: thread title changed by moderator]

    I've been advised that MediaLab is a good tool to use for factorial surveys. I've searched the user guide but can't find any mention of them- can anyone advise me about how to construct one? I'm new to MediaLab so basics are very much appreciated.
    Last edited by jarvis24; 11-24-2009 at 05:20 AM.

  2. #2
    Join Date
    Mar 2009
    Posts
    401
    The manual doesn't actually mention factorial surveys, because it has enough power to do factorial surveys, as well as many other types of experiments.

    Have you tried creating a questionnaire or experiment file yet? You may want to take a look at the samples while first starting out - from there, try building your own questionnaire.

  3. #3
    Join Date
    May 2009
    Posts
    15
    Hi newell,

    Thanks for the advice, I've just had a look at the samples. I've almost found what I need- this bit:

    You can insert the value of a subject's prior response to any fill-in-the blank or scale response item by typing the appropriate variable name surrounded by < > within any question wording text. For example, if you had previously asked subjects about their race using a fill-in-the-blank question or a scale response and you had called this variable race, then you could insert the subjects response in a subsequent question wording like this: What percentage of your friends are <race>? For scale responses, the text label of the selected response is used.

    is going on the right lines but I don't want to use participants' prior responses in the instruction text, I would like to use options that will be randomly chosen by the program. I'm aiming to create vignettes with different variables that are randomly selected, e.g. " you are working with a <15/35/55> year old <man/woman> with <a learning disability/ a physical disability/ no disability> " etc - these variables would contribute to making decisions (in the format of scale responses or other simple meaures) about actions that could be taken. Can you advise further?

    Many thanks,

  4. #4
    Join Date
    Nov 2005
    Posts
    3,328
    Try taking a look at the section on Advanced Features--especially the subsection on calculated values. You could create variables that would be "stand-ins" for the vignette values. For example, you could generate the three values for each the three items in your example by using Excel's conditional logic and randomization function (e.g., to select one from 15/35/55 and one from man/woman, etc) . Take a look at that section and the accompanying sample and see if you can get the gist of that functionality. If it seems in the ballpark let us know and we could elaborate on how to proceed.

  5. #5
    Join Date
    May 2009
    Posts
    15
    Hi Jarvis,

    Thanks for the advice, I've had a look at Excel's conditional logic and it does what I'm hoping for- I just need to find out how to transfer it to Medialab now. The formulas I've used are:

    =RANDBETWEEN(1,3)

    =LOOKUP(A45,{1,2,3},{"A PHYSICAL DISABILITY","A LEARNING DISABILITY","NO DISABILITY"})

    =RANDBETWEEN(1,3)

    =LOOKUP(A47,{1,2,3},{"PHYSICAL ABUSE","FINANCIAL ABUSE","INSTITUTIONAL ABUSE"})

    etc, then

    =CONCATENATE("You are working with a ",A50," with ",A46,". You suspect that ",A52," ",A48," is occurring. ")

    which translates to a combination of the above, i.e.
    You are working with a WOMAN with A PHYSICAL DISABILITY. You suspect that SEVERE PHYSICAL ABUSE is occurring.

    Is there is way to link to the excel spreadsheet, or do you have to create a Medialab file with the above info in it?

    Many thanks,

    Lindsey
    Last edited by jarvis24; 11-24-2009 at 04:48 AM.

  6. #6
    Join Date
    Mar 2009
    Posts
    401
    Hi Lindsey,

    As Blair has noted, you'll want to use the 'advanced features' that MediaLab supports. See the following link for more information:
    http://www.empirisoft.com/medialab/h...d_features.htm

    MediaLab has the ability to harness the power of excel through 'responses.xls'. This workbook allows a user to modify their experiment to communicate with excel during each screen. When a response is created, it can be written and passed through excel's coniditional logic - which will then allow for complex variables and skip patterns to be written and occur, respectively.

    Take a look at the above link and try out the advanced features sample. These items should be able to help point you in the right direction. Let me know how it goes!

    Regards,
    Trevor

  7. #7
    Join Date
    May 2009
    Posts
    15
    Thanks for the prompt response!
    I've had a stab at it and have managed to get the calculated values working with a few testers, but not on my actual experiment. The problem is that rather than getting the content of the vignette (named "vig" on the responses.xls worksheet) I just get <vig> displayed in the instructions. But I get reports etc so excel and Medialab are talking, just not quite enough...!
    Ideas of what might be going wrong are:

    -I've had to add in a few extra rows- all named as calculated variable- that aren't features in the experiment at all (i.e. aren't dependent on responses from participants). I need them there to carry out the conditional logic in excel in order to create the vignettes- I've looked but the formula can't be used in a 3D reference (i.e. from another sheet) so have to be on the same one as the experimental and calculated variables (i think!)

    - The contents of the cell is quite a number of words, is there a restriction on how many it can translate?

    - Do calculated values need to be dependent on past responses of participants or can they be independent stimuli?

    Thanks again- have attached experiment file for your perusal..

    Lindsey
    Attached Files Attached Files

  8. #8
    Join Date
    Mar 2009
    Posts
    401
    Hm. After taking a chance to look at your files, I noticed that the file 'responses.xls' had null values on row 14. This may have been the issue. I've updated your excel spreadsheet and have attached it to this post - let me know if the modification solves the problem.

    - The contents of the cell is quite a number of words, is there a restriction on how many it can translate?
    I don't believe so.

    - Do calculated values need to be dependent on past responses of participants or can they be independent stimuli?
    They should be allowed to exist as independent stimuli.
    Attached Files Attached Files

  9. #9
    Join Date
    May 2009
    Posts
    15
    hmm.. the instruction 1 and 2 page is just blank now; it doesn't even say "here is scenario1 <vig> " ...! I've tried moving the zip file out of the experiment file but no change- help! I've attached the zip file again. Thanks!
    Attached Files Attached Files
    Last edited by lpike; 07-02-2009 at 04:45 AM. Reason: forgot attachment

  10. #10
    Join Date
    May 2009
    Posts
    15
    Hello again,

    I'm back on the case after a little break, did you have a chance to look at the above spreadsheet to see what could be the problem?

    Thanks,
    Lindsey

  11. #11
    Join Date
    Nov 2005
    Posts
    3,328
    This took quite a while to figure out--but I think I may have found the problem. Eventually I recalled from years back that MediaLab can sometimes have trouble using calculated values from Excel files that use functions from the Analysis Tool Pack Add-In (see Tools Menu in Excel)

    It just so happened that the function you were using to generate random numbers came from this toolpack, e.g.,

    =randbetween(1,3)

    In the attached revision, I've replaced these uses of the randbetween function with Excel's own rand function like this:

    =(INT(RAND()*3))+1

    The latter generates an integer value between 1 and 3 but it does not use the Analysis Toolpack so the calculated vignette strings display with no problem--at least on the sample when I just ran it.

    Hope that helps,
    Blair
    Attached Files Attached Files

  12. #12
    Join Date
    May 2009
    Posts
    15
    it's working.

  13. #13
    Join Date
    Mar 2009
    Posts
    401
    Great! That's good to hear.
    Let us know if you need any more help.

  14. #14
    Join Date
    May 2009
    Posts
    15

    reports

    I need more help

    Having collected lots of lovely vignettes, I've just realised that reports aren't doing what I need to them to do. The idea was to find out which factors affect people's judgements of when to make a safeguarding alert, and to do this i need to record which vignette they see, along with their responses to it. I've used conditional logic as advised, and looking at the reports, I thought they recorded the vignette that was shown with the responses. However looking more closely, I've realised the reports change every time I open the file, same as the instructions. This is a disaster (!) because it means I've got no record of what vignette was actually shown to participants, as it changes every time I open it- it's not even the same the first time I open the report (I checked!) is there any way to make the reports record the actual screen that was presented, or a way to stop the conditional logic from changing randomly in the reports as it's programmed to in the responses file?
    I've attached the questionnaire, responses file and an example of a report- F9 changes the vignette combinations!

    many thanks,

    lindsey
    Attached Files Attached Files

  15. #15
    Join Date
    May 2009
    Posts
    15

    Same again..

    Hello again,

    Have you managed to make any headway on this issue- I could really do with some help asap! I've tried adding graphs etc to sheet 2 as a way to depict the vignette that was shown, but this does exactly the same thing of changing each time the file is opened. I seem to be in a catch 22 situation of needing the randomising formula in the responses file to make the vignettes work, but then having my data sabotaged by those randomising formulas as they prevents recording the vignette that the participant saw! Is there any way to "freeze" the report file so it is locked from changing or recalculating regardless of the formulas that are in it? i don't know if this involves programming something into excel...
    Help will be very much appreciated! Thanks in advance.

  16. #16
    Join Date
    Nov 2005
    Posts
    3,328
    I think I've found a way for you to find these values in your existing data files. I've run into this issue before and never figured out how to "capture" the random numbers like this. I tried something new this morning and may have found a solution. I right clicked on your 1.xls file and selected "Open With" and chose Microsoft Access. Access knows how to read Excel file work sheets as data tables but doesn't execute the functions. When I pulled up the 1.xls file, I saw values that were fixed--the same each time I tried to open the file. I suspected these were the actual values generated prior to the last time the file was saved. To test this possibility, I reopened your 1.xls file in Excel. As you would expect, a new set of random numbers were generated. So I saved this file as 1b.xls and open that in Access. Low and behold, the new random numbers appeared--unchanged--from what I had just seen and saved in Excel. There were some issues with other variables (you'll see #ref here and there) but I didn't look beyond whether the random numbers could be captured--and the latter appears to be so. I've zipped and attached a "captured" set of the random numbers that were generated for subject 1 located in 1.xls. Take a look at this 1.mdb file and see if it makes sense. Note when you open the Excel file in Access, just click "Finish" when given the option and then it will automatically save the new mdb file in the same folder that contains the xls file.
    Attached Files Attached Files
    • File Type: zip 1.zip (6.5 KB, 1 views)
    Last edited by jarvis24; 11-24-2009 at 05:18 AM.

  17. #17
    Join Date
    Dec 2009
    Posts
    3

    Related probem

    it seems like my problem is related. I'd like to take advantage of the advance features on medialab and produce something that looks like the Sample6 experiment ( the one on advanced feature). The 'help' file there (on the Sample6 window) says that the trick is creating an excel document responses.xls. Done. But it is not working!
    My experiment has 3 conditions. Only for one condition I would like to calculate the score of the subjects. How do I do it? Could this be the problem? I am sending my experiment, if that is of any help. many thanks for your collaboration

    em
    Attached Files Attached Files

  18. #18
    Join Date
    May 2009
    Posts
    15
    Quote Originally Posted by jarvis24 View Post
    I think I've found a way for you to find these values in your existing data files. I've run into this issue before and never figured out how to "capture" the random numbers like this. I tried something new this morning and may have found a solution. I right clicked on your 1.xls file and selected "Open With" and chose Microsoft Access. Access knows how to read Excel file work sheets as data tables but doesn't execute the functions. When I pulled up the 1.xls file, I saw values that were fixed--the same each time I tried to open the file. I suspected these were the actual values generated prior to the last time the file was saved. To test this possibility, I reopened your 1.xls file in Excel. As you would expect, a new set of random numbers were generated. So I saved this file as 1b.xls and open that in Access. Low and behold, the new random numbers appeared--unchanged--from what I had just seen and saved in Excel. There were some issues with other variables (you'll see #ref here and there) but I didn't look beyond whether the random numbers could be captured--and the latter appears to be so. I've zipped and attached a "captured" set of the random numbers that were generated for subject 1 located in 1.xls. Take a look at this 1.mdb file and see if it makes sense. Note when you open the Excel file in Access, just click "Finish" when given the option and then it will automatically save the new mdb file in the same folder that contains the xls file.
    Hi Jarvis- thanks for your reply. Really nice idea- but annoyingly I tried it this morning after finding a computer with Access on it, and it doesn't work . I wrote down the actual vignettes that were shown and compared it to the access file- not the same. It does tell you the last values that excel was displaying- but they don't tally to what was shown on the screen when the vignettes were displayed. It's almost like the process of reading the file triggers the randomisation, or there's no memory of the last set of random numbers that were produced.

    I spoke to a technician this morning who asked if there was any way to write the results straight into an Access file rather than excel? I know you can write txt files too so maybe this could be an option? But i don't know if this would tally with teh responses file still being excel.

    Otherwise maybe I'll just have to make as many seperate experiments as participants, generate the vignettes in one file and paste just the values into numerous responses files. but that would be a bit laborious!

    Also, I've been trying a few other things- I've added custom items for vignettes as recomended in this thread [FONT='Calibri','sans-serif']http://www.empirisoft.com/Support/showthread.php?t=1316 , and it works, but again with different vignettes to those shown; i also tried adding another few lines for vig, vig2, vig3, vig4 into the responses file so it would record the vignette as a response, and again it works beautifully- just not with the vignette that was shown! It's like it has an opportunity to re randomise again every time before it's written. argh.

    Thanks again,
    Lindsey
    Last edited by lpike; 12-04-2009 at 07:49 AM. Reason: needed to add more

  19. #19
    Join Date
    Mar 2009
    Posts
    401
    Hi Lindsey - attached you'll find a modified version of the responses.xls file that you were previously using.

    The file has been altered to only randomize values once during an experiment.
    You should be able to edit responses.xls without any random values being assigned.
    You should also be able to view data created by the file without any new random values being generated.

    Try the responses.xls file out with the attached questionnaire and let me know if the experiment works the way you want it to.
    If it does, I'll gladly explain how the attached files accomplish the task.

    -

    Make sure you enable iterative calculations for the responses.xls file attached. To do so, simply follow the tutorials below.

    Office 2007 Iterative Calculations:
    http://office.microsoft.com/en-us/ex...tes%20formulas

    Office 2003 Iterative Calculations:
    http://www.office.microsoft.com/en-u...H010004941033#
    (Select the link: "Make a circular reference work by changing the number of times Microsoft Excel iterates formulas")
    Attached Files Attached Files

  20. #20
    Join Date
    May 2009
    Posts
    15
    Thanks very much, I'll have a look at this over the next couple of days.
    Best wishes,
    Lindsey

  21. #21
    Join Date
    May 2009
    Posts
    15
    it works!! hooray!! thanks very much for this, amazing. The data files now stay stable and show the same vignette as the aprticipant would see on screen.
    Would love to know how you did it?!

    Many thanks again,
    lindsey

  22. #22
    Join Date
    Nov 2005
    Posts
    3,328
    As a note to other users, Trevor provided me with the following summary of how the random values generated by Excel can be captured using the method he describes above:
    The system uses a simple set of switches. On the original xls sheet I give the first two trial value fields a value of -99. I then use the following equation to make sure that the randomization on the xls file only happens once:
    Cell B2 - " =IF(AND(B62<>-99, B63=-99),(INT(RAND()*4))+1,B2) "
    The above syntax tells the xls sheet that it will only randomize if the first trial doesn't have a value of -99, and the second trial does have a value of -99. This scenario will only take place once on the excel sheet (when MediaLab first changes the value -99 and then moves onto change the next). Once MediaLab assigns different values to the fields marked with a value of -99, we set on a switch. The data files never change because the excel sheet will never randomize unless both the first and second trial contain values of -99.
    Just in case it helps anyone else looking to capture the seemingly incapturable! --Blair

  23. #23
    Join Date
    Dec 2010
    Posts
    4

    How to read excel responses.xls file

    Hi Empirisoft,

    Please help!
    I believe I am not able for Medialab to read the excel file responses.xls. It shows the word <vig1> but not the actual sentences that should be generated with the random strings in the excel file.
    Please take a look at the files I have enclosed (this is really my first attempt ..). I know that in a previous thread this problem came up, but I can't see what is wrong here ..
    Attached Files Attached Files
    Last edited by acattani; 02-18-2011 at 05:05 AM.

  24. #24
    Join Date
    Nov 2005
    Posts
    3,328
    Acattani,

    It gets tricky if you are trying to SAVE randomly generated values in an XLS file because they'll be different every time you open the document. However, this is only a problem if you want o see what values were generated *after* a session when you open the .xls file. Is that the case, or is your issue with generating and presenting the random stimuli in the first place? If it's the latter, try entering in some simulated responses where appropriate (i.e., where they are currently now blank). When you do that, do you see results in the new variable cells you are creating? Does that make sense?

    Blair

  25. #25
    Join Date
    Dec 2010
    Posts
    4

    Randomly generated vignettes

    Thank for your response, but I am not sure I understood. My actual problem is in generating and presenting the random stimuli in the first place when running the study. But, rather than showing the full vignette it only shows 'ok' (in place of the full random string included in <vig1>) and on the next slide it shows '<vig2>' (in place of the full random string included in <vig2>). So I cannot simulate any response for the vignettes ... Hope it makes sense.

  26. #26
    Join Date
    Nov 2005
    Posts
    3,328
    If I understand correctly--in your responses.xls file, you have a calculated variable called "vig1" which you are inserting into a questionnaire variable wording by using <vig1>? If so, this makes sense. I think the trouble might be arising from the fact that you have named the questionnaire item "vig1" also. Consequently, the response for that instruction item is getting inserted which is "ok" -- the data value for an instruction item.

    A fix here I think would be to simply rename the variable name in your .que file from "vig1" to "vig" or something like that. So there is no confusion that the inserted <vig1> value is coming from a calculated cell in responses.xls and not from a response to an item within the MediaLab .que file.

  27. #27
    Join Date
    Dec 2010
    Posts
    4
    Thank you - i made some changes in the variable name and made other changes .. so atlas it now work! Thank you!
    One thing at a time .. I am now starting to check what values were generated *after* a session when a open the participant .xls file.

  28. #28
    Join Date
    Nov 2005
    Posts
    3,328
    Glad you found the resolution--and the individual responses.xls files for each subject (they can come in very handy!!)

Similar Threads

  1. Can questionorder.xls be referenced by responses.xls formula during a session?
    By samjacks7986 in forum MediaLab Older Versions: How Do I...
    Replies: 2
    Last Post: 04-27-2009, 03:29 PM
  2. saving large .que files
    By Jamal in forum MediaLab Older Versions: Suggestions
    Replies: 1
    Last Post: 04-21-2009, 03:49 PM
  3. Submit custom item values to responses.xls
    By Thomas in forum MediaLab Older Versions: How Do I...
    Replies: 5
    Last Post: 10-16-2008, 02:01 PM
  4. Saving calculated variables from responses.xls in data files?
    By jarvis24 in forum MediaLab Older Versions: How Do I...
    Replies: 1
    Last Post: 05-27-2008, 07:28 AM
  5. Generating random tones
    By vulteef in forum MediaLab Older Versions: How Do I...
    Replies: 1
    Last Post: 01-02-2006, 03:45 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •