Generating and - saving - random values from responses.xls files
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.
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.
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 " 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,
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.
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
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/help/advanced_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
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
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.
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!
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
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
Great! That's good to hear.
Let us know if you need any more help.
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
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.
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.
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
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 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
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/excel/HP100662431033.aspx#Make%20a%20circular%20reference%20work%20by%20changing%20the%20number%20of%20times%20Microsoft%20Excel%20iterates%20formulas
Office 2003 Iterative Calculations:
http://www.office.microsoft.com/en-us/excel/HP052002851033.aspx?pid=CH010004941033#
(Select the link: "Make a circular reference work by changing the number of times Microsoft Excel iterates formulas")
Thanks very much, I'll have a look at this over the next couple of days.
Best wishes,
Lindsey
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
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
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 ..
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
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.
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.
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.
Glad you found the resolution--and the individual responses.xls files for each subject (they can come in very handy!!)
