Follow-up: Check the validity of Logic Problem #1 in Excel–Part 1

This exercise was originally posted at: http://maritimeorca0.blogspot.com/2014/03/check-validity-of-logic-problem-1-in.html

The Task

Create a table in Excel that matches the table in Logic Problem #1 then create a formula (or formulas) to test the validity. Our first validity check will make sure that we have 6 different months and that they are in the correct order. I would also recommend using data validation to ensure that all months entered match the requirements of the logic problem.

My Solution

I built my table and created my solution. My spreadsheet can be found at:https://onedrive.live.com/redir?resid=2E6EFB80915ED5BD!7018&authkey=!AKHKuyxKAdaH25g&ithint=file%2c.xlsx

I named my worksheet Table and added a second worksheet, Validation, to validate the dates. The validation table includes auto-filled months from January 1, 2001 through December 1, 2002. They have been formatted to show the full month with an abbreviated date (formatted as mmmm yy). This allows me to use Excels handling of dates without showing the day of the month or the full year (it’s supposed to be year 1, not 2001). I selected cells C3 through C19 on the Table worksheet, added data validation (Data tab), and used the same date format. I am using a list with A2 through A25 on the Validation worksheet as my criteria.

Rather than use overly complicated formulas, I used a column for intermediate calculations, and labeled it as Date Check.

My first cell under month check was simple since I don’t have to worry about previous data.
=IF(C3="","NA",1)

This check to see of C3 (first date) is empty. If so, we are going to indicate that with an “NA.” Otherwise, we will mark it as the first valid date.

The second Date Check formula is a little more complicated.
=IF(C4="","NA",IF(C4=MAX(C$3:C3),MAX(J$3:J3),IF(C4>MAX(C$3:C3),MAX(J$3:J3)+1,"INVALID")))

Let’s break that down a little
=IF(C4="","NA",Handling of non-empty)

This starts the same way as the previous. We tell Excel that we want to report “NA” if we don’t yet know the month. If we do have a value, it become trickier because we have to check multiple things. Here’s the Handling of non-empty that I mentioned above.
IF(C4=MAX(C$3:C3),MAX(J$3:J3),IF(C4>MAX(C$3:C3),MAX(J$3:J3)+1,"INVALID"))

Once again, I’m going to break it down.
IF(C4=MAX(C$3:C3),MAX(J$3:J3),Handling of unequal)

We are checking to see if the the date matches the latest date (maximum value) so far. If it does, we are repeating the highest value already in the Date Check column. This will be the same month in the sequence as the date that we matched. In most cases, that would be the previous date. The only way this will differ is if we skip ahead in entering dates. By using the range C$3:C3 and J$3:J3, we fix the start of the range and allow the end of the range to adjust when we fill the formula. In other words, we are looking at the cell range from the beginning of the column to the last cell above the formula. It’s a one-cell range for now, but we will have a 16-cell range by the time we reach the 17th goal.

We are now to the handling of unequal date values.
IF(C4>MAX(C$3:C3),MAX(J$3:J3)+1,"INVALID")

With the formula complete, I filled through J19 to have a working formula for each month.
Since the purpose of this check is to make sure our data is valid, we need to make sure that the later goals are later in the date sequence. We check to ensure that the month is after all previous months. If it is, we increment our sequence count by one. If the date is before a goal that was scored earlier, we will return “INVALID.”

If we have the correct solution to the logic problem, the first goal will be on the first date, and we will have exactly five more dates in order that will trigger the increments. This will give the final goal a Date Check value of 6. We also know that any “INVALID” cells and/or values of 7 indicate that we have messed up somewhere. A value of 5, however, could merely mean that we’re not done yet. The goal of this exercise is not to create a list of values to interpret. It’s meant to let Excel tell you whether or not the data is valid. For that reason, I added one more formula to check the data produced by the Date Check formulas.
IF(COUNT(J3:J19)=17,IF(J19=6,"YES","NO"),IF(OR(COUNTIF(J3:J19,"INVALID")>0,MAX(J3:J19)>6),"NO","SO FAR"))

As you can see, this is another formula that might be easier to explain if I break it down.
=IF(COUNT(J3:J19)=17,Handling of complete,Handling of incomplete)

If the logic problem has been solved, each cell in the data check should have a value. This formula counts the numerical values. If the count equals 17, we will be able to check the number of different months in cell J19.
Here’s how I checked if a completed table has the six different months
IF(J19=6,"YES","NO")

If six months are properly represented, the last cell (J19) will have a value of 6. Any other value indicates that the solution is incorrect.

Now to the handling of wrong or incomplete situations.
IF(OR(Invalid Condition 1, Invalid Condition 2),"NO","SO FAR")

We are looking for conditions that indicate that this logic problem is incorrect. Any of those conditions will return “NO.” If we are not wrong and we did not fully match the criteria for completion, we will display “SO FAR.” This basically means that we have some “NA” values indicating that we have not yet entered some of the data.

One of the invalid conditions is if one month is earlier than an earlier goal.
COUNTIF(J3:J19,"INVALID")>0

When months are not ordered correctly, Excel is returning “INVALID” for that month. If any month (greater than zero) is invalid, then this logic problem must be incorrect.

The other invalid condition is if we have more than six months represented.
MAX(J3:J19)>6

Values of less than six are possible if we are correct so far but have not finished. Adding data will not remove months, so higher values must be wrong.
 

Notes

The original idea of this exercise was to create a series of challenges for each clue. I broke these clues up into rules (items from the intro) and actual clues (numbered items in the logic problem). I still want to take this approach, but it looks like most rules are more difficult to figure out than most clues. For this reason, I will not take a fully linear approach. Instead, I will alternate between the rules and the clues. As always, my revolving exercise types could put clue #1 years down the road.
 

Feel Free to Comment

I do not provide space to those who visit my blog. If you would like to share any files, you are going to have to find your own online storage. At that point, you can provide links with your comments. I would like to point out that macros can create security risks. If possible, save all documents as a standard workbook (not macro-enabled) unless macros are vital to your solution. If you are going to download any solutions, make sure that you are not loading macros by default. If a spreadsheet includes macros, check the code before running them.  

About Sharing

Nobody should copy my work and then take credit. The nature of this exercise, however, could produce very similar results for different people. For this reason, I am unlikely to push too hard for credit. You can also add to my solution and pursue variations. Unless otherwise specified, similar guidelines should apply to solutions from other contributors.
 

Upon completion of this exercise, please fill out the following form:
https://docs.google.com/spreadsheet/viewform?formkey=dEhDTnd1bFZTY3ZBclhHX0k3VV9pcGc6MA
Responses to the above form are collected and stored in a spreadsheet that can be viewed at:
https://docs.google.com/spreadsheet/pub?key=0Agcdh--NHOS0dEhDTnd1bFZTY3ZBclhHX0k3VV9pcGc&output=html