Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Random Matching
Hello everyone, I'm doing some Random Matching in Excel 2003 for statistical purposes: I've got a file with two worksheets containing student records (ID, courses, grades, etc). One sheet contains 'Students who were tutored' (200 or so records) and the other 'Students who were not tutored' (800 or so records). Now I'd like to match up (pair up) those students of same sex, same course and same age from both of these into one new sheet - while maintaining a RANDOM selection for those 'Students who were not tutored' of the same sex etc. A few years ago I used something like this, but I can't recall what all this means (using the steps I used): - Created a tab called *Y* (for yes - tutored) lists all the tutored students listed, and *N* (for not tutored). - On the *N* tab, I have created a series called TBL1 producing random numbers using the *-=RAND()-*, next to this column I have a column of numbers generated based on the following formula *-{=RANK(TBL1,TBL1)}-* - On a seperate tab called *RND_CALC*, I have the first column listing the student number of the Tutored students using the following: *-=OFFSET(Y!$A$1,INT((ROW(A1)+2)/2),)-*. In the second listing the non-tutored students using: *-=OFFSET(N!$A$1,N!K2,)-* - On the RND_CALC tab, I then have a "SEX", "AGE", "COURSE" column. Each having the following: SEX =--(VLOOKUP($A2,Y!$A$2:$D$25,COLUMN(B1),0)=VLOOKUP($B 2,N!$A$2:$D$62,COLUMN(B1),0)) AGE =--(VLOOKUP($A2,Y!$A$2:$D$25,COLUMN(C1),0)=VLOOKUP($B 2,N!$A$2:$D$62,COLUMN(C1),0)) COURSE =--(VLOOKUP($A2,Y!$A$2:$D$25,COLUMN(C1),0)=VLOOKUP($B 2,N!$A$2:$D$62,COLUMN(C1),0)) Can someone help me make some sense on how to ensure random matching with this setup? Thanks in advance, Sab. -- Sabotage1945 ------------------------------------------------------------------------ Sabotage1945's Profile: http://www.excelforum.com/member.php...o&userid=14769 View this thread: http://www.excelforum.com/showthread...hreadid=536034 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Random Matching
A few years ago I used something like this,
but I can't recall what all this means (using the steps I used) .. Perhaps this previous response would help recollection <g: http://tinyurl.com/zn7p9 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Sabotage1945" wrote in message news:Sabotage1945.26twx2_1145983512.1129@excelforu m-nospam.com... Hello everyone, I'm doing some Random Matching in Excel 2003 for statistical purposes: I've got a file with two worksheets containing student records (ID, courses, grades, etc). One sheet contains 'Students who were tutored' (200 or so records) and the other 'Students who were not tutored' (800 or so records). Now I'd like to match up (pair up) those students of same sex, same course and same age from both of these into one new sheet - while maintaining a RANDOM selection for those 'Students who were not tutored' of the same sex etc. A few years ago I used something like this, but I can't recall what all this means (using the steps I used): - Created a tab called *Y* (for yes - tutored) lists all the tutored students listed, and *N* (for not tutored). - On the *N* tab, I have created a series called TBL1 producing random numbers using the *-=RAND()-*, next to this column I have a column of numbers generated based on the following formula *-{=RANK(TBL1,TBL1)}-* - On a seperate tab called *RND_CALC*, I have the first column listing the student number of the Tutored students using the following: *-=OFFSET(Y!$A$1,INT((ROW(A1)+2)/2),)-*. In the second listing the non-tutored students using: *-=OFFSET(N!$A$1,N!K2,)-* - On the RND_CALC tab, I then have a "SEX", "AGE", "COURSE" column. Each having the following: SEX =--(VLOOKUP($A2,Y!$A$2:$D$25,COLUMN(B1),0)=VLOOKUP($B 2,N!$A$2:$D$62,COLUMN(B 1),0)) AGE =--(VLOOKUP($A2,Y!$A$2:$D$25,COLUMN(C1),0)=VLOOKUP($B 2,N!$A$2:$D$62,COLUMN(C 1),0)) COURSE =--(VLOOKUP($A2,Y!$A$2:$D$25,COLUMN(C1),0)=VLOOKUP($B 2,N!$A$2:$D$62,COLUMN(C 1),0)) Can someone help me make some sense on how to ensure random matching with this setup? Thanks in advance, Sab. -- Sabotage1945 ------------------------------------------------------------------------ Sabotage1945's Profile: http://www.excelforum.com/member.php...o&userid=14769 View this thread: http://www.excelforum.com/showthread...hreadid=536034 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Random Matching
Thank you MAX!!!! I searchd ALL over for this info... including the old thread, but could never find it. I appreciate you finding this for me. After ALL my frustrations - you've saved my life and the little hair I have left (yup, I could kiss you right now). Thanks again, Sab. -- Sabotage1945 ------------------------------------------------------------------------ Sabotage1945's Profile: http://www.excelforum.com/member.php...o&userid=14769 View this thread: http://www.excelforum.com/showthread...hreadid=536034 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Random Matching
You're welcome !
Thanks for feedback .. How can I do a matching based on a plus or minus 1 (or whatever the number we chose to be)? So something like "+/-1%" Afraid I'm not quite sure what your'e after here. Perhaps try Debra's page at: http://www.contextures.com/xlFunctions03.html -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Sabotage1945" wrote: Thank you MAX!!!! I searchd ALL over for this info... including the old thread, but could never find it. I appreciate you finding this for me. After ALL my frustrations - you've saved my life and the little hair I have left (yup, I could kiss you right now). Thanks again, Sab. -- Sabotage1945 ------------------------------------------------------------------------ Sabotage1945's Profile: http://www.excelforum.com/member.php...o&userid=14769 View this thread: http://www.excelforum.com/showthread...hreadid=536034 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Random Matching
How can I do a matching based on a plus or minus 1 (or whatever the number we chose to be)? So something like "+/-1%" Afraid I'm not quite sure what your'e after here. Again thanks for the reply and help! What I guess I was getting at is, say we had students whom we are trying to match... we have matched their Sex, Age, Class, etc, but also need to match their Average grade within a plus or minus X% range. How can one write a formula to do so? Here is an example: .. Student ..... Age ...... Class ...... Sex ..... Average Mark% ================================================== Bill Clinton ..... 26 ..... MATH101 .... M ......... 78.3% Will Smith ...... 24 .... MATH101 .... M ......... 63.8% Susan Lang .... 25 .... MATH101 .... F ......... 79.8% John Lenon .... 26 .... MATH101 .... M ......... 77.9% In this example, we'd have a match between "Clinton" and "Lenon" since their categories ALL match... even though their "Average Mark%" isn't an exact match, we could consider them 'statistically' close since their marks are +/-1% of one another. Many thanks in advance for considering this question. Kindest regards, Sab. -- Sabotage1945 ------------------------------------------------------------------------ Sabotage1945's Profile: http://www.excelforum.com/member.php...o&userid=14769 View this thread: http://www.excelforum.com/showthread...hreadid=536034 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Random Matching
Extending the comparison set-up in the new sheet
(with Av mark% assumed in col E in sheets: T and NT) In the new sheet, Insert a new col F In F1: Av Mark% (label) Put in F2, and copy down to F17: =--(ABS(VLOOKUP($A2,T!$A$2:$E$5,COLUMN(E1),0)-VLOOKUP($B2,NT!$A$2:E$17,COLUMN(E1),0))<=1%) Then just slightly adjust the formulas in col G In G2, filled down to G17: =SUM(C2:F2) In G1: =SUM(G2:G17)/(COUNTA(C:F)-4) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Sabotage1945" wrote: How can I do a matching based on a plus or minus 1 (or whatever the number we chose to be)? So something like "+/-1%" Afraid I'm not quite sure what your'e after here. Again thanks for the reply and help! What I guess I was getting at is, say we had students whom we are trying to match... we have matched their Sex, Age, Class, etc, but also need to match their Average grade within a plus or minus X% range. How can one write a formula to do so? Here is an example: .. Student ..... Age ...... Class ...... Sex ..... Average Mark% ================================================== Bill Clinton ..... 26 ..... MATH101 .... M ......... 78.3% Will Smith ...... 24 .... MATH101 .... M ......... 63.8% Susan Lang .... 25 .... MATH101 .... F ......... 79.8% John Lenon .... 26 .... MATH101 .... M ......... 77.9% In this example, we'd have a match between "Clinton" and "Lenon" since their categories ALL match... even though their "Average Mark%" isn't an exact match, we could consider them 'statistically' close since their marks are +/-1% of one another. Many thanks in advance for considering this question. Kindest regards, Sab. -- Sabotage1945 ------------------------------------------------------------------------ Sabotage1945's Profile: http://www.excelforum.com/member.php...o&userid=14769 View this thread: http://www.excelforum.com/showthread...hreadid=536034 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Random Matching
Thank you for your *WONDERFUL* help Max... Cheers, Sab. -- Sabotage1945 ------------------------------------------------------------------------ Sabotage1945's Profile: http://www.excelforum.com/member.php...o&userid=14769 View this thread: http://www.excelforum.com/showthread...hreadid=536034 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Random Matching
"Sabotage1945" wrote:
Thank you for your *WONDERFUL* help Max... Cheers, Sab. Glad it helped ! Thanks for the feedback .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seed numbers for random number generation, uniform distribution | Excel Discussion (Misc queries) | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
vlookup based on random result returns incorrect result | Excel Worksheet Functions | |||
Random Sampling | Excel Discussion (Misc queries) | |||
VBA "Rnd" Function: Truly Random? | Excel Discussion (Misc queries) |