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

John Bartow jbartow at winhaven.net
Mon Mar 8 20:36:46 CST 2021 

After Stuart's suggestion turned out to prove that I was thinking wrongly, I wrote a function to apply your suggestion:
Function Closeness(n1 As Double, n2 As Double, n3 As Double) As Double
Dim ABval As Double, BCval As Double, ACval As Double
ABval = Abs(n1 - n2)
BCval = Abs(n2 - n3)
ACval = Abs(n1 - n3)
If (n1 + n2 + n3) > 0 Then 'had to add this because of rows with all zeros which are meaningless
    Closeness = ABval + BCval + ACval
Else
    Closeness = 100
End If
End Function

Results for test set sorted by preferable 'Distance':
ROW	N1	N2	N3	Distance
35	6.00	6.00	6.00	0.00
29	0.00	0.00	0.20	0.40
27	0.50	0.50	1.00	1.00
18	2.00	1.50	1.50	1.00
37	5.00	6.00	5.00	2.00
22	2.00	1.00	1.00	2.00
38	5.00	5.00	6.00	2.00
39	6.00	5.00	5.00	2.00
40	6.00	7.00	6.00	2.00
36	5.00	6.00	6.00	2.00
26	1.00	0.00	1.20	2.40
19	2.50	1.00	1.50	3.00
24	1.00	0.20	2.00	3.60
25	1.00	0.10	2.00	3.80
21	2.50	0.50	2.50	4.00
17	3.50	1.50	1.50	4.00
41	6.00	7.00	5.00	4.00
10	5.00	2.00	5.00	6.00
15	4.00	3.00	0.00	8.00
28	1.00	1.00	5.00	8.00
16	5.00	1.00	1.00	8.00
42	5.00	7.00	9.00	8.00
20	1.50	0.75	5.00	8.50
12	6.00	1.00	2.00	10.00
23	0.00	5.00	0.00	10.00
13	6.00	1.00	1.00	10.00
9	7.00	3.00	1.00	12.00
43	5.00	9.00	11.00	12.00
7	6.00	6.00	0.00	12.00
11	7.00	1.00	2.00	12.00
3	6.00	8.00	0.00	16.00
4	8.00	6.00	0.00	16.00
8	0.00	11.00	0.00	22.00
6	12.00	0.00	0.00	24.00
5	12.00	0.00	0.00	24.00
44	3.00	9.00	15.00	24.00
2	4.00	12.00	0.00	24.00
1	4.00	20.00	0.00	40.00
14	0.00	0.00	22.00	44.00
31	0.00	0.00	0.00	100.00
32	0.00	0.00	0.00	100.00
33	0.00	0.00	0.00	100.00
34	0.00	0.00	0.00	100.00
30	0.00	0.00	0.00	100.00

And so I have a beginning - thanks!
John
-----Original Message-----
From: dba-Tech <dba-tech-bounces+jbartow=winhaven.net at databaseadvisors.com> On Behalf Of Rocky Smolin
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

This may be a simplistic approach but...

Suppose you take the absolute value between A and B and the absolute value between B and C and sum them.  Then the lowest value would be the three numbers that are the closest.

You could also and the absolute value between A and C, add that to the first two absolute values.  Don't know if that's appropriate to your application, though.

But using this approach you don't have to consider the magnitude of the numbers.  6 and 7 have the same 'distance' value as 600 and 700.

Now - curious - what is the application?

r

On Mon, Mar 8, 2021 at 10:24 AM John Bartow <jbartow at winhaven.net> wrote:

> Does my latest reply to Stuart help?
>
> -----Original Message-----
> From: dba-Tech 
> <dba-tech-bounces+jbartow=winhaven.net at databaseadvisors.com>
> On Behalf Of Rocky Smolin
> Sent: Sunday, March 07, 2021 3:47 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
>
> Can you define 'best balance' a little more?
>
> r
>
> On Sun, Mar 7, 2021 at 12:53 PM John Bartow <jbartow at winhaven.net> 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

More information about the dba-Tech mailing list