[dba-Tech] finding the most consistent combination of 3 columns

John Bartow jbartow at winhaven.net
Mon Mar 8 20:32:00 CST 2021 

Thanks for the advice. Unfortunately what it proved is my thinking was wrong. I didn't need st dev. It made for a nice bell curve but that's not how I imagined it would end up.

John Bartow
WinHaven IT Consulting

-----Original Message-----
From: dba-Tech <dba-tech-bounces+jbartow=winhaven.net at databaseadvisors.com> On Behalf Of Stuart McLachlan
Sent: Monday, March 08, 2021 1:19 PM
To: Discussion of Hardware and Software issues <dba-tech at databaseadvisors.com>
Subject: Re: [dba-Tech] finding the most consistent combination of 3 columns

Oops (1,1,1) and (99,99,99) both return zero, so you will need to do a second sort on N1 for any sets that return a closeness of zero.


On 9 Mar 2021 at 5:12, Stuart McLachlan wrote:

> Not a long, they are whole numbers. You numbers should to be DOUBLES.
> 
> Standard deviation would be a good start, but then (6,7,8) would have
> the same value as 66,67,68)   and clearly, the second set are "closer"
> to each other.
> 
> However, if you divide the SD by the mean, you get a more appropriate
> value:
> 
> In that case (6,7,8) and 60,70,80) are equally "close"
> 
> 
> Function Closeness(n1 As Double, n2 As Double, n3 As Double) As Double
> Dim mean As Double, SD As Double mean = (n1 + n2 + n3) \ 3 SD =
> Sqr(((n1 - mean) ^ 2 + (n2 - mean) ^ 2 + (n3 - mean) ^ 2) / 3)
> Closeness = SD / mean End Function
> 
> 
> 
> 
> 
> 
> On 8 Mar 2021 at 18:23, John Bartow wrote:
> 
> > I'm sorry, as you may have figured out, I'm not thinking the
> > clearest right now. I should have stated: Having all 3 numbers be
> > exactly the same is best. Having 2 numbers the same and one number
> > slightly different would be next best, etc.
> > 
> > These are percentages with 2 decimal places. So they could be
> > represented by 'nn.nn". IIRC correctly the most appropriate field
> > type in Access would be "Long".
> > 
> > 5.00,5.00,5.00 would have preference over 4.00,4.00,4.00 but I doubt
> > that is ever going to occur. There are RARELY equivalents across all
> > three columns. I haven't noticed one yet. So that's very much a
> > secondary concern. 
> > 
> > I was imagining a function that returned a value of preference
> > possibly in another excel column or access query field. So the
> > function would be a separate value that could be sorted against.
> > 
> > Thanks for your patience in my posting this properly!
> > John B
> > -----Original Message-----
> > From: dba-Tech
> > <dba-tech-bounces+jbartow=winhaven.net at databaseadvisors.com> On
> > Behalf Of Stuart McLachlan Sent: Sunday, March 07, 2021 4:16 PM To:
> > Discussion of Hardware and Software issues
> > <dba-tech at databaseadvisors.com> Subject: Re: [dba-Tech] finding the
> > most consistent combination of 3 columns
> > 
> > Ah, that's completely different to how I understood your
> > requirement.
> > 
> > Are you now saying that 5,5,5 and 4,4,4 have equal priority? Or is
> > your original example with the largest values first still relevant?
> > 
> > You say "2 decimal places, but then say that a difference of "1"
> > would be next best.
> > 
> > Do you really mean "2 decimal places" or ar they integers in the
> > range 1 to 99 ?
> > 
> > Which of these is the "best match"
> > 5,5,4  or 5,4,5 or 4,5,5   or are the equal?
> > 
> > 
> > 
> > 
> > 
> > On 7 Mar 2021 at 20:53, John Bartow wrote:
> > 
> > > I'm going to try to clarify my goal in regards to my question.
> > > 
> > > I have 3 columns of data which currently reside in Excel - but
> > > that was just a convenient way to get them into digital format.
> > > They data are all numerical and never less than zero.  They can
> > > have a maximum of 2 decimal points.
> > > 
> > > What I'm specifically looking for is a way to evaluate the three
> > > columns of each row to find the best balance of numbers between
> > > the three columns. Having all 3 numbers be exactly the same is
> > > best. Having 2 numbers the same and one number be plus or minus
> > > one would be next best, etc.
> > > 
> > > I am thinking that this would be a solution of "standard
> > > deviation" but I am not sure.
> > > 
> > > Just trying to avoid going down a rabbit hole. So I turn to the
> > > people I know have better brains than I. John B -----Original
> > > Message----- From: dba-Tech
> > > <dba-tech-bounces+jbartow=winhaven.net at databaseadvisors.com> On
> > > Behalf Of John Bartow Sent: Sunday, March 07, 2021 1:22 PM To:
> > > Discussion of Hardware and Software issues
> > > <dba-tech at databaseadvisors.com> Subject: Re: [dba-Tech] finding
> > > the most consistent combination of 3 columns
> > > 
> > > 1,1,1
> > > 
> > > -John
> > > -----Original Message-----
> > > From: dba-Tech
> > > <dba-tech-bounces+jbartow=winhaven.net at databaseadvisors.com> On
> > > Behalf Of Gustav Brock via dba-Tech Sent: Sunday, March 07, 2021
> > > 1:54 AM To: Discussion of Hardware and Software issues
> > > <dba-tech at databaseadvisors.com> Cc: Gustav Brock
> > > <gustav at cactus.dk> Subject: Re: [dba-Tech] finding the most
> > > consistent combination of 3 columns
> > > 
> > > Hi John
> > > 
> > > Which set would rank first: 1,1,1 or 3,1,1?
> > > 
> > > /gustav
> > > 
> > > ________________________________
> > > Fra: dba-Tech
> > > <dba-tech-bounces+gustav=cactus.dk at databaseadvisors.com> på vegne
> > > af John Bartow <jbartow at winhaven.net> Sendt: 7. marts 2021 06:52
> > > Til: DBA-Tech (dba-tech at databaseadvisors.com)
> > > <dba-tech at databaseadvisors.com> Emne: [dba-Tech] finding the most
> > > consistent combination of 3 columns
> > > 
> > > Hi All,
> > > I am looking to find the best combination of 3 columns of numbers
> > > and then sort by the highest ranking. For instance given the
> > > values below I want to find 5,5,5 and then 4,4,4 and then 3,2,3
> > > and then 3,2,2. 0,4,8 5,1,1 1,3,5 3,2,3 12,0,0 0,10,0 0,0,15 4,4,4
> > > 5,5,5 3,2,2
> > > 
> > > Suggestions on how to proceed in Excel or Access.
> > > 
> > > John B
> > > _______________________________________________
> > > dba-Tech mailing list
> > > dba-Tech at databaseadvisors.com
> > > https://databaseadvisors.com/mailman/listinfo/dba-tech
> > > Website: http://www.databaseadvisors.com 
> > > _______________________________________________ dba-Tech mailing
> > > list dba-Tech at databaseadvisors.com
> > > https://databaseadvisors.com/mailman/listinfo/dba-tech Website:
> > > http://www.databaseadvisors.com
> > > _______________________________________________ dba-Tech mailing
> > > list dba-Tech at databaseadvisors.com
> > > https://databaseadvisors.com/mailman/listinfo/dba-tech Website:
> > > http://www.databaseadvisors.com
> > > 
> > 
> > 
> > _______________________________________________
> > dba-Tech mailing list
> > dba-Tech at databaseadvisors.com
> > https://databaseadvisors.com/mailman/listinfo/dba-tech
> > Website: http://www.databaseadvisors.com
> > _______________________________________________
> > dba-Tech mailing list
> > dba-Tech at databaseadvisors.com
> > https://databaseadvisors.com/mailman/listinfo/dba-tech
> > Website: http://www.databaseadvisors.com
> > 
> 
> 
> _______________________________________________
> dba-Tech mailing list
> dba-Tech at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/dba-tech
> Website: http://www.databaseadvisors.com
> 


_______________________________________________
dba-Tech mailing list
dba-Tech at databaseadvisors.com
https://databaseadvisors.com/mailman/listinfo/dba-tech
Website: http://www.databaseadvisors.com

More information about the dba-Tech mailing list