Analyzing for Skipped Deductions
For me, one of the hardest data format to handle is the date because it can come in many formats: text, date format, space, maybe a null space, zero or the word blank. It can come in different varieties that the IF, OR, and AND formulas can get complicated to handle the different formats. And then there is the start dates and end dates and all of the logic that comes with that. Right now I’m working on some reconciliation issues and part of the exercise is making sure the start and end dates matches the paycheck dates and that there are some internal logic. This past week I spent some time developing some checking formulas to warn me when something did not appear right and needs researching.
One especially difficult type of check is what I call the “skipped paycheck”, or when a month mysteriously does not show a deduction (paycheck always goes out but sometimes a benefit deduction is not made). I’ve had a similar problem before but in that instance, the names and the paycheck dates ran down the column. This time the names ran down the column and the paycheck months ran across the row which necessitated a different attack.
The formula is an array formula and I will attempt to explain how it works, but I’m kind of tired so we’ll see how well I do that. Below is the array formula and below that is a snapshot of part of the file that the formula relates to. An array formula always has brackets {} around them but you do not enter the brackets yourself. The brackets are created by doing Ctrl+Shift+Enter. Although you can’t see it in the cell when you have an array formula, the result of the formula will most often be an array of numbers or texts or date something like (1, 2, 3, 4). Normally, the cell shows a single number and to get at the full array result, you would have to do the F9 trick: go into the formula bar at the top, place your cursor inside the formula and highlight the section of the formula you want to see the answer and do F9. The results should show up as an array (for an array formula only).
{=IF((MIN(COUNTIFS($AD$2:$AI$2,ROW(INDIRECT(SMALL((AD7:AI7<>0)*(COLUMN(AD7:AI7)),COUNTIF(AD7:AI7,0)+COUNTIF(AD7:AI7,"")+1)&":"&MAX((AD7:AI7<>0)*(COLUMN(AD7:AI7))))),AD7:AI7,"<>"&0,AD7:AI7,"<>"&""))=0)=TRUE,"x","")}
In the snapshot, the monthly paychecks run from columns AD through AI. What I want to know is when the deduction skips if it happens. For us we maybe had only 3 instances and they may be valid but further research is required.
The rest of this posting gets technical or wonky but it will help you understand, somewhat, what each part of the formula is attempting to do, in case you have to adjust it for your situation.
SMALL function
First, let’s tackle part of the SMALL function. I want to find the smallest date of the paycheck. It could be the first month of the reconciliation or it could be the person started after the month of reconciliation which in this snapshot is October 2017. The SMALL function is designed to scope out the first month of paycheck in our reconciliation.
Let’s assume our employee has a paycheck series as (30,20, ,30,15). His paycheck had a deduction in October, November, January and February.
SMALL((AD7:AI7<>0)*(COLUMN(AD7:AI7)),COUNTIF(AD7:AI7,0)+COUNTIF(AD7:AI7,"")+1)
(AD7:AI7<>0) lists out whether the number is zero or not where a zero or blank becomes false. So in our example the array answer becomes (TRUE, TRUE, FALSE, TRUE, TRUE, FALSE)
COLUMN(AD7:AI7) just gives the column number of the column so in this example we get (30, 31, 32, 33, 34, 35). This part I probably made it a little more convoluted than it needs to be but since I was just starting to figure it out, this clunky method was developed. I suspect there is a better way but I’m too tired to divine it.
(AD7:AI7<>0)*(COLUMN(AD7:AI7)) then becomes (30, 31, 0, 33, 34, 0). This is the first part of the SMALL function. The first instance of the deduction was obviously done in October or column 30 and I want SMALL to pull that answer out. The smallest number of the array is zero but I don’t want that; I want 30, so the second part of the SMALL function comes into play.
COUNTIF(AD7:AI7,0)+COUNTIF(AD7:AI7,"")+1 counts all of those zeros and blanks and adds 1 to get the next smallest number. In my example, I have 2 zeroes so you don’t want the smallest or the next smallest but the 3rd smallest number. The COUNTIF formula in my example becomes 3.
And finally, SMALL( (30, 31, 0, 33, 34, 0), 3) becomes 30. So column 30 is when the first deduction happens.
MAX function
Next, I want the last deduction and that is handle with the MAX function. Continuing with my example, the last pay deduction happened in February, not March. The MAX function is very similar to the first part of the SMALL function.
MAX((AD7:AI7<>0)*(COLUMN(AD7:AI7))) becomes MAX( (TRUE, TRUE, FALSE, TRUE, TRUE, FALSE) * (30, 31, 32, 33, 34, 35) ). Multiplying the two arrays within the MAX function then becomes (30, 31, 0, 33, 34, 0). Taking the MAX of that array becomes 34 or column AH (February).
When I put SMALL and MAX together: SMALL((AD7:AI7<>0)*(COLUMN(AD7:AI7)),COUNTIF(AD7:AI7,0)+COUNTIF(AD7:AI7,"")+1)&":"&MAX((AD7:AI7<>0)*(COLUMN(AD7:AI7)))
The result becomes “30:34” when I do F9.
ROW and INDIRECT function
ROW(INDIRECT(SMALL((AD7:AI7<>0)*(COLUMN(AD7:AI7)),COUNTIF(AD7:AI7,0)+COUNTIF(AD7:AI7,"")+1)&":"&MAX((AD7:AI7<>0)*(COLUMN(AD7:AI7)))))
The next part I’m a little shaky on. The ROW piece as shown above is to convert “30:34” (or whatever your answer) back to (30, 31, 32, 33, 34) because I need it to be an array when I next do the COUNTIFS part. The SMALL and MAX is probably the clunky part but I was working my way through the solution and right now I haven’t figured out a cleaner version. Normally ROW formula cannot be written as ROW(“30:34”) (again, that’s just my answer for the specific example) – it has to be written as ROW(AD7:AH7) (which by the way, does not give me the answer I want). The INDIRECT in ROW(INDIRECT(“30:34”)) is what converts to (30, 31, 32, 33, 34). I lucked into that part of the formula. And that’s the best I can explain this.
COUNTIFS function
COUNTIFS($AD$2:$AI$2,ROW(INDIRECT(SMALL((AD7:AI7<>0)*(COLUMN(AD7:AI7)),COUNTIF(AD7:AI7,0)+COUNTIF(AD7:AI7,"")+1)&":"&MAX((AD7:AI7<>0)*(COLUMN(AD7:AI7))))),AD7:AI7,"<>"&0,AD7:AI7,"<>"&"")
There are 3 parts to the COUNTIFS: 1) the column range from the start of the paycheck deduction to the last of the paycheck deduction; 2) the months where the deduction does not equal to zero; and 3) the months where the deduction does not equal to blank.
First part: Row 2 shows the column numbers. AD:AI is the range and the ROW piece is the criteria. This criteria restricts the counting only to those months from first deduction to last deduction, not from October to March.
$AD$2:$AI$2,ROW(INDIRECT(SMALL((AD7:AI7<>0)*(COLUMN(AD7:AI7)),COUNTIF(AD7:AI7,0)+COUNTIF(AD7:AI7,"")+1)&":"&MAX((AD7:AI7<>0)*(COLUMN(AD7:AI7)))))
Second part: AD7:AI7,"<>"&0
Third part: AD7:AI7,"<>"&""
Put the entire COUNTIFS together the array answer gives me (1, 1, 0, 1, 1). The count was from the first deduction to the last deduction and showed a count of 1 when the deduction was not zero but counted as zero when the deduction was zero or blank.
Finally MIN and IF function
{IF((MIN(COUNTIFS($AD$2:$AI$2,ROW(INDIRECT(SMALL((AD7:AI7<>0)*(COLUMN(AD7:AI7)),COUNTIF(AD7:AI7,0)+COUNTIF(AD7:AI7,"")+1)&":"&MAX((AD7:AI7<>0)*(COLUMN(AD7:AI7))))),AD7:AI7,"<>"&0,AD7:AI7,"<>"&""))=0)=TRUE,"x","")}
Take the MIN of that COUNTIFS. If you had any month where there was no deduction between the first month of deduction and the last month of deduction, that month will show zero, as in our example, and the MIN will be zero.
Finally, if MIN is zero, then flag it as an “x” to tell me that this needs researching.
And that wraps up this post.
Next week, I think I'm going to keep things shorter and simpler. It's late and approaching bed hour.
You must be logged in to post a comment.