Symbolic Logic, Part II: The “And” Operator

In the last post, we discussed the statement P, which we defined as “The car is green,” and how the statement is either True, or it is False.  We also talked about “not P”, which we symbolized as ~P.

Now, let’s say we have another statement:

The car has four doors.

We’re going to call this statement Q. It also has two possible states: True and False. Here is its truth table:

Q

True

False

And here is the truth table for Q and ~Q:

Q	~Q
True	False
False	True

Now we can combine P and Q to create a compound statement thusly:

The car is green and it has four doors.

We are going to symbolize this conjunction like so:

P ∧ Q

That little upside-down “V” between P and Q represents the operator “and,” and the statement as a whole would be read “P and Q.” Now, P∧Q also evaluates as a whole statement to either True or False, but this depends on the values of both P and Q. In the case of the ∧ operator, both P and Q must be True for P∧Q to be True. Here is the truth table for this:

P	Q	P∧Q
True	True	True
True	False	False
False	True	False
False	False	False

So, if we say P∧Q, but the car actually has two doors, then it doesn’t really matter what color the car is, the entire statement is false.  The rule of the “And” operator is this:

A compound statement using the “And” operator evaluates to True if and only if both component statements are True; otherwise, the compound statement is False.

If we were going to say that:

The car is green and does not have four doors.

We would symbolize it thusly:

P ∧ ~Q

And the truth table would look like this:

 

P	Q	~Q	P ∧ ~Q
True	True	False	False
True	False	True	True
False	True	False	False
False	False	True	False

So, in the case of our hypothetical two-door, green car, P ∧ ~Q will evaluate to True, since it is true that the car is green and it does not have four doors.  Note that this statement is also True if the car has five doors, or three doors, or anything other than four doors.

Next: The “Or” Operator

Symbolic Logic, part I: Introduction to Basic Concepts

One important area of math is “symbolic logic,” which is also known as “formal logic” or sometimes “Boolean algebra.”
Here’s how it starts.  I’m going to start with a statement:

The car is green.

We are going to call this statement P.  Note that when we say “statement,” we mean something that is factual, not opinion.  So “The car is green” is okay (as long as we agree what “green” is: but, I digress), but “The car is beautiful” is not.  Our statement P can have one of two states: It can either be True or it can be False.  If we were to put these possible values into a table, they would look like this:

P

True

False

It may seem a bit silly to have such a table, but I’m introducing it here to introduce the concept of the “Truth table.” This is the truth table of the statement P.

Note that there is no “Maybe” listed here.  That’s because in logic, a statement is either totally true or it is totally false.  There is no middle ground; the car is either green or it is not.  If it’s “kinda” green, then it’s not green, and the statement would be false.  Now, we could get into arguments about the definition of “green,” and get into specific color wavelengths on the electromagnetic spectrum, but for our purposes, green is green, and a car is either green or it isn’t.

Then there is this statement:

The car is not green.

We could call this statement “not P.”  We would symbolize it thusly: ~P.  Whenever P is True, ~P is false, and vice versa.  The truth table would look like this:

P	~P
True	False
False	True

Next: We will be introducing compound statements.

Fixing the Variance Percentage Program

Okay, the previous VBA function has a few flaws: it doesn’t test for empty cells, and it is rather inefficient when it comes to memory; it stores the results in an array.

For reference, here is the original code:

Public Function LowestCompPer(vRg As Range) As Double
    Dim n As Integer
    Dim t As Integer
    Dim p() As Double
    Dim i As Integer
    Dim wsf As WorksheetFunction
    Dim j As Integer
    Dim k As Integer
    Set wsf = Excel.WorksheetFunction
    n = vRg.Count
    t = (n * (n - 1)) / 2
    ReDim p(t) As Double
    k = 1
    For i = 1 To n - 1
        For j = i + 1 To n
            p(k) = Abs(vRg(i) - vRg(j)) / (wsf.Max(Abs(vRg(i)), Abs(vRg(j))))
            k = k + 1
        Next j
    Next i
    Dim min As Integer
    min = 1
    For i = 1 To t
        If p(i) < p(min) Then
            min = i
        End If
    Next i
    LowestCompPer = p(min)
End Function

Now we’re going to fix this.  Here we go:

Public Function LowestCompPer(vRg As Range) As Double
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Dim FullCells As New Collection
    Dim PerDbl As Double
    Dim min As Double
    'get a count of how many cells aren't empty
    'also keep a collection of cells that aren't empty
    'This will kill two birds with one stone: it will allow us
    'us to get rid of the triangular number calculation,
    'as well as make sure that all cells we're referring to
    'actually have values in them.
    For i = 1 To vRg.Count
        If vRg(i) <> "" Then
            FullCells.Add vRg(i)
        End If
        
    Next i
    'k keeps track of whether or not the first minimum has been calculation.
    '0=no,  1=yes
    k = 0
    For i = 1 To FullCells.Count - 1
        For j = i + 1 To FullCells.Count
            'PerDbl temporarily stores the results of our comparison formula
            PerDbl = Abs(FullCells(i) - FullCells(j)) / _
                (Application.WorksheetFunction.Max(Abs(FullCells(i)), _
                Abs(FullCells(j))))
            'We have to start somewhere.  Remember, all percentages will be 
            'positive
            'Since min always start at 0, we have to initialize min
            'Once we've done that, though, we don't want to change it 
            'again unless we've found a smaller number
            If k = 0 Then
                min = PerDbl
                k = 1
            ElseIf PerDbl < min Then
                min = PerDbl
            End If
        Next j
    Next i
    'Never forget to clean up!
    Set FullCells = Nothing
    LowestCompPer = min
End Function

It may seem strange, but it works.  It may seem like we have traded one inefficiency (looping twice) with another (storing full cells in a Collection), but this was necessary to make sure that the entire range is full.

Leave any comments or questions you may have below.

Variance Problem, Part 2: Electric Boogaloo

To continue from the last post:

Your boss approaches you.  He has a list of 40 SKUs that have been inventoried.  He says, “We need to adjust the inventory on these products.  Each of these SKUs has 4 counts associated with them.  This is represented on this list as the difference between what we actually have and what the computer is telling us we should have.  The only way we can adjust these is if two counts for each product agree with each other within 10%.  Find which ones can be adjusted, and make the appropriate adjustments.”

Here is the table with the SKUs and the four count variances per SKU:

SKU	Var 1	Var 2	Var 3	Var 4
00001	- 42	- 44	- 52	- 42
00002	- 80	-38	- 34	- 93
00007	- 11	+ 2	- 15	- 9
00013	- 85	- 131	- 51	- 74
00020	- 86	- 212	- 83	- 77
00028	- 99	- 202	- 157	- 88
00037	+ 92	+ 16	+ 46	+ 97
00040	+ 91	+ 191	+ 70	+ 82
00050	+ 12	+ 2	+ 18	+ 13
00060	+ 43	+ 48	+ 30	+ 44
00069	+ 31	+ 51	+ 39	+ 34
00074	- 57	- 105	- 70	- 50
00075	- 88	- 31	- 71	- 72
00077	+ 56	+ 104	+ 55	+ 53
00082	+ 68	+ 43	+ 69	+ 73
00090	- 29	- 62	- 26	- 32
00093	- 55	- 73	- 81	- 65
00098	- 34	- 17	- 28	- 32
00108	- 13	- 7	- 18	- 14
00109	- 40	- 7	- 59	- 44
00113	+ 42	- 11	+ 31	+ 39
00117	- 50	- 90	- 65	- 44
00124	- 40	- 92	- 61	- 34
00127	+ 87	- 13	+ 89	+ 97
00135	+ 91	- 10	+ 43	+ 102
00138	- 50	- 42	- 56	- 43
00141	+ 2	+ 0	+ 2	+ 2
00144	- 61	- 19	- 77	- 64
00152	- 5	+ 2	- 6	- 6
00162	+ 30	+ 44	+ 31	+ 28
00169	+ 15	+ 5	+ 22	+ 18
00173	- 66	- 111	- 35	- 58
00179	- 9	- 1	- 5	- 10
00189	- 36	- 2	- 52	- 39
00194	+ 89	+ 219	+ 114	+ 88
00202	- 49	+ 21	- 31	- 55
00212	- 89	- 146	- 95	- 100
00218	- 75	- 105	- 32	- 74
00223	- 78	- 179	- 83	- 63

If you remember the Variance Problem from the last post, you will remember that the formula to get the variance percentage looks a little something like this:

For each of these SKUs, this will have to be done 6 times (again, see the last post to see why).  That means we will have to do this calculation 240 times.  Woof.
Okay, so we do a macro.  We have several choices, here.

1. We could have a macro that goes through each SKU one-by-one, does all of the calculations, and highlights the ones that are good.
2. We could create a user-defined function that we could put into the “F” column to show the lowest variance percentage.

Since I like to show my work, I choose option 2.

First, I save this spreadsheet as an .xlsm file, so it can have macros in it.  Next, I go into the VBA editor (Alt+F11) and this is the formula I put into the editor:

Public Function LowestCompPer(vRg As Range) As Double
    Dim n As Integer
    Dim t As Integer
    Dim p() As Double
    Dim i As Integer
    Dim wsf As WorksheetFunction
    Dim j As Integer
    Dim k As Integer
    Set wsf = Excel.WorksheetFunction
    n = vRg.Count
    t = (n * (n - 1)) / 2
    ReDim p(t) As Double
    k = 1
    For i = 1 To n - 1
        For j = i + 1 To n
            p(k) = Abs(vRg(i) - vRg(j)) / (wsf.Max(Abs(vRg(i)), Abs(vRg(j))))
            k = k + 1
        Next j
    Next i
    Dim min As Integer
    min = 1
    For i = 1 To t
        If p(i) < p(min) Then
            min = i
        End If
    Next i
    LowestCompPer = p(min)
End Function

Here’s how it works.  First, an arbitrary number of cells get passed to the function (you in the back, be quiet—I'm getting to it, I promise). We need to know how many comparisons need to be made (t). Then we need to create a variable that will hold all of the calculations for us. It then goes through all of the necessary combinations and puts two numbers through our variance comparison formula and stores it in the p(k) array. Then, it goes through all of these results stored in the p array and determines the lowest (again, you in the back: BE QUIET) percentages. It then returns the lowest percentage.

What you do is you put the following in the “E” column:

=LowestCompPer(B2:E2)

After autofilling, you get something like this:

SKU	Var 1	Var 2	Var 3	Var 4	Compare Percentage
00001	- 42	- 44	- 52	- 42	0%
00002	- 80	- 38	- 34	- 93	11%
00007	- 11	+ 2	- 15	- 9	18%
00013	- 85	- 131	- 51	- 74	13%
00020	- 86	- 212	- 83	- 77	3%
00028	- 99	- 202	- 157	- 88	11%
00037	+ 92	+ 16	+ 46	+ 97	5%
00040	+ 91	+ 191	+ 70	+ 82	10%
00050	+ 12	+ 2	+ 18	+ 13	8%
00060	+ 43	+ 48	+ 30	+ 44	2%
00069	+ 31	+ 51	+ 39	+ 34	9%
00074	- 57	- 105	- 70	- 50	12%
00075	- 88	- 31	- 71	- 72	1%
00077	+ 56	+ 104	+ 55	+ 53	2%
00082	+ 68	+ 43	+ 69	+ 73	1%
00090	- 29	- 62	- 26	- 32	9%
00093	- 55	- 73	- 81	- 65	10%
00098	- 34	- 17	- 28	- 32	6%
00108	- 13	- 7	- 18	- 14	7%
00109	- 40	- 7	- 59	- 44	9%
00113	+ 42	- 11	+ 31	+ 39	7%
00117	- 50	- 90	- 65	- 44	12%
00124	- 40	- 92	- 61	- 34	15%
00127	+ 87	- 13	+ 89	+ 97	2%
00135	+ 91	- 10	+ 43	+ 102	11%
00138	- 50	- 42	- 56	- 43	2%
00141	+ 2	+ 0	+ 2	+ 2	0%
00144	- 61	- 19	- 77	- 64	5%
00152	- 5	+ 2	- 6	- 6	0%
00162	+ 30	+ 44	+ 31	+ 28	3%
00169	+ 15	+ 5	+ 22	+ 18	17%
00173	- 66	- 111	- 35	- 58	12%
00179	- 9	- 1	- 5	- 10	10%
00189	- 36	- 2	- 52	- 39	8%
00194	+ 89	+ 219	+ 114	+ 88	1%
00202	- 49	+ 21	- 31	- 55	11%
00212	- 89	- 146	- 95	- 100	5%
00218	- 75	- 105	- 32	- 74	1%
00223	- 78	- 179	- 83	- 63	6%

Note here that there are some here that do not fit within the boss's requirements (for example: 00169) because none of the count variances are within 10%. Who did these counts, anyway?

A confession: This is not the optimal solution. The function stores the comparison percentage in an array, and it turns out the array is not necessary. I made this function in the heat of the moment after getting a project at work much like the one referred to here. Also, the function will throw an exception if it encounters an empty cell (since it is intended to be used in a cell, you will get a “#VALUE!” error).

Next, I'm going to show how to fix this to be more efficient and to be able to handle empty cells.

The Variance Problem

Instead of doing the introduction thing (which is, frankly, boring) I figured I would leap right in head first.

I'm sure many of you have seen Vi Hart's video on "The 12 Days of Christmas" (The Gauss Chrismath Special)  This video presents a math problem that is highly applicable to a computer programming problem I had been working on right around the same time.  Remember this video: we'll be coming back to it.  Here we go.

You are presented with four numbers and are asked to find the two numbers that are the closest to each other, and give the lowest ratio of variance according to the following formula:

The strange "v" like symbol in the denominator means "|x₁| or |x₂|, whichever is greater." The formula is asking to take the absolute value of the difference between the two numbers, and divide by the larger of the absolute values of the two numbers.

The question is this: what is the minimum number of comparisons to determine the minimum variance?  Since we have 4 numbers, one might be tempted to give the obvious answer: 4²=16, since these four numbers can be combined in groups of two in 16 different ways.    If we were to make a table of all possible combinations, and the four numbers being compared were A, B, C, and D, it would look like this:

A,A	A,B	A,C	A,D
B,A	B,B	B,C	B,D
C,A	C,B	C,C	C,D
D,A	D,B	D,C	D,D

But there is a lot of redundancy here.  Try the numbers 4 and -6 in the formula above:

Now, let's reverse these numbers:

It doesn't matter what order they're put in.  Thus, (A,B) = (B,A). That means that we can eliminate almost half of the above table (shaded in yellow):

A,A	A,B	A,C	A,D
B,A	B,B	B,C	B,D
C,A	C,B	C,C	C,D
D,A	D,B	D,C	D,D

Also, since A=A, do we really need to compare it?  No matter what number we plug into the variance formula in both the x₁ and the x₂ positions (say, 4 and 4), it will always evaluate to 0.  That means that we can get rid of these "self-tests":

A,A	A,B	A,C	A,D
B,A	B,B	B,C	B,D
C,A	C,B	C,C	C,D
D,A	D,B	D,C	D,D

So, we're left with 6 groupings that we need to get the variance for.

Now, let's say we have 5 numbers we need to compare?  We'd have 5² = 25 possible combinations:

A,A	A,B	A,C	A,D	A,E
B,A	B,B	B,C	B,D	B,E
C,A	C,B	C,C	C,D	C,E
D,A	D,B	D,C	D,D	D,E
E,A	E,B	E,C	E,D	E,E

Now, going through our process above, we can eliminate many of these:

A,A	A,B	A,C	A,D	A,E
B,A	B,B	B,C	B,D	B,E
C,A	C,B	C,C	C,D	C,E
D,A	D,B	D,C	D,D	D,E
E,A	E,B	E,C	E,D	E,E

This will leave us with 10 comparisons that need to be made.  Wait a minute, we eliminated half of the tests, 2 × 10 = 20.  And we had 5 numbers in this case, 20 + 5 = 25.  Before, we had (2 × 6) + 4 = 16.  So what we've done is 2t+n=n2, where t is the number of comparisons left after taking out duplicate test and self-tests, and n is the count of numbers to be compared.  The problem here is that we know n.  We need to know t.  So let's solve for t:

Now, this is odd, because this looks very similar to Ms. Hart's formula for triangular numbers:

Which is the formula to tell you the sums of all integers between 1 and n.  Why the difference of n between P and t?  Remember above that we subtracted all of the self-tests from our group, which, when summing consecutive integers, we don't have to do.  In fact, it could be said that t = P - n.

Coming up, the VBA function that can compare any arbitrary count of variables and return the lowest variance ratio.