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

[Trevor Newell]

Great! That's good to hear.
Let us know if you need any more help.

[lpike]

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

[lpike]

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.

[jarvis24]

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.

[emat]

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

[lpike]

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

[Trevor Newell]

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

[lpike]

Thanks very much, I'll have a look at this over the next couple of days.
Best wishes,
Lindsey