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

[Stuart McLachlan]

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
>