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

> 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 ***, 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 ...... *** ..... 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.

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 ***, 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 ...... *** ..... 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
>
>

Thank you for your WONDERFUL help Max...

Cheers,
Sab.

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