Solved Excel Formula for Two in a Row Criteria

September 3, 2018 at 06:40:30
Specs: Windows 10
Hi, I want to create an excel file where it would remind me that an employee must be put in a Performance Improvement Program in case his/her scorecard is below 3.40 for two out of 3 month rolling period. Say for example:
B1 is 3.30. C1 is 3.40, D1 is 3.30. A1 would say "enrolled"
B1 is 3.40, C1 is 3.40, D1 is 3.30, E1 is 3.30. A1 would say "enrolled"

See More: Excel Formula for Two in a Row Criteria

Reply ↓  Report •

#1
September 3, 2018 at 12:30:16
This should get you what your looking for, it's not the most elegant of formulas.

From your post:
two out of 3 month
I presume your sheet looks something like:

     A         B   C    D    E    F    G    H    I    J    K    L    M
1)           Jan  Feb  Mar  Apr  May  Jun  Jul  Aug  Sep  Oct  Nov  Dec
2) Enrolled  3.4  3.4  3.3  3.3

I'm also assuming that you do not do the evaluation till after the end of the month, so the current month is always blank.

Enter the formula in cell A2

=IF(OR(
COUNTIFS(B2,"<3.40",C2,"<3.40",D2,""),
COUNTIFS(C2,"<3.40",D2,"<3.40",E2,""),
COUNTIFS(D2,"<3.40",E2,"<3.40",F2,""),
COUNTIFS(E2,"<3.40",F2,"<3.40",G2,""),
COUNTIFS(F2,"<3.40",G2,"<3.40",H2,""),
COUNTIFS(G2,"<3.40",H2,"<3.40",I2,""),
COUNTIFS(H2,"<3.40",I2,"<3.40",J2,""),
COUNTIFS(I2,"<3.40",J2,"<3.40",K2,""),
COUNTIFS(J2,"<3.40",K2,"<3.40",L2,""),
COUNTIFS(K2,"<3.40",L2,"<3.40",M2,""),
COUNTIFS(L2,"<3.40",M2,"<3.40")),"Enrolled","")


The COUNTIFS() function ANDs the Criteria Ranges together, so the statment is:
B2 and C2 and D2,

So, we simply check cells B2 and C2 to see if either is less than 3.40,
and we also have to make sure that D2 is blank,
else we will always get the first hit and never move beyond that point.

If both B2 and C2 are less than 3.40 and D2 is blank, then we have a TRUE statment and the text "Enrolled" is displayed.

The rest of the COUNTIFS() do the same thing.

Now if you want to add a bit of color to your life,
then use Conditional Formatting to color cell A2
so it will stand out and be noticed.

1) Select your cell or range of cells, IE A2
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.
5) Enter the formula:

=OR(COUNTIFS(B2,"<3.40",C2,"<3.40",D2,""),COUNTIFS(C2,"<3.40",D2,"<3.40",E2,""),COUNTIFS(D2,"<3.40",E2,"<3.40",F2,""),COUNTIFS(E2,"<3.40",F2,"<3.40",G2,""),COUNTIFS(F2,"<3.40",G2,"<3.40",H2,""),COUNTIFS(G2,"<3.40",H2,"<3.40",I2,""),COUNTIFS(H2,"<3.40",I2,"<3.40",J2,""),COUNTIFS(I2,"<3.40",J2,"<3.40",K2,""),COUNTIFS(J2,"<3.40",K2,"<3.40",L2,""),COUNTIFS(K2,"<3.40",L2,"<3.40",M2,""),COUNTIFS(L2,"<3.40",M2,"<3.40"))

6) Click on the Format button
7) Select the Fill Tab
8) Select a pretty color
9) Click OK
10) Click OK

The formula is the same as above without the IF() function as
Conditional Formatting need only return TRUE for it to work,
so there is no need for the IF()

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  Report •

#2
September 3, 2018 at 16:55:28
I think I may have misinterpreted what your looking for.

My last post will find 2 occurrences, of less than 3.4, in a sequence
IE B2 and C2 or D2 and E2

But after re-reading your post, I think your looking for 2 occurrences,
of less than 3.4 within 3 cells.

If that is true, then this formula should get you what you want.

Enter the formula in cell A2

=IF(OR(
AND(COUNTIF(B2:D2,"<"&3.4)=2,E2=""),
AND(COUNTIF(C2:E2,"<"&3.4)=2,F2=""),
AND(COUNTIF(D2:F2,"<"&3.4)=2,G2=""),
AND(COUNTIF(E2:G2,"<"&3.4)=2,H2=""),
AND(COUNTIF(F2:H2,"<"&3.4)=2,I2=""),
AND(COUNTIF(G2:I2,"<"&3.4)=2,J2=""),
AND(COUNTIF(H2:J2,"<"&3.4)=2,K2=""),
AND(COUNTIF(I2:K2,"<"&3.4)=2,L2=""),
AND(COUNTIF(J2:L2,"<"&3.4)=2,M2=""),
AND(COUNTIF(K2:M2,"<"&3.4)=2)),"Enrolled","")

As for the Conditional Format formula, just remove the IF() function:

=OR(
AND(COUNTIF(B2:D2,"<"&3.4)=2,E2=""),
AND(COUNTIF(C2:E2,"<"&3.4)=2,F2=""),
AND(COUNTIF(D2:F2,"<"&3.4)=2,G2=""),
AND(COUNTIF(E2:G2,"<"&3.4)=2,H2=""),
AND(COUNTIF(F2:H2,"<"&3.4)=2,I2=""),
AND(COUNTIF(G2:I2,"<"&3.4)=2,J2=""),
AND(COUNTIF(H2:J2,"<"&3.4)=2,K2=""),
AND(COUNTIF(I2:K2,"<"&3.4)=2,L2=""),
AND(COUNTIF(J2:L2,"<"&3.4)=2,M2=""),
AND(COUNTIF(K2:M2,"<"&3.4)=2))

The only glitch is the last TWO months, if you have

Nov Dec
3.4 3.3

It does not check December as there is no 3 cell range.
We could probably find a work around if necessary.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#3
September 4, 2018 at 12:23:56
✔ Best Answer
re "It does not check December as there is no 3 cell range."

Well, there would be a 3 month range around the month of December unless they fire the guy. ;-)

Oct, Nov, Dec
Nov, Dec, Jan
Dec, Jan, Feb

There will always be a 3 month rolling period looking backwards starting in Month 3 of employment and continuing until the person is fired.

I assume that looking backwards 3 months is how the determination to enroll the person in the program is made and therefore I assume that the last three months is all that needs to be considered by the formula in Column A.

I think we need to know how ChGatsby handles the change in year as far as the sheet layout. Does the new year wrap around to B1 or does it continue in N1 or something else?

If the months continue going off to the right forever, then the following formula seems to work.

With this lay out...

     A         B   C    D    E    F    G    H    I    J    K    L    M    N    O   P ...
1)           Jan  Feb  Mar  Apr  May  Jun  Jul  Aug  Sep  Oct  Nov  Dec  Jan  Feb  Mar ...
2) Enrolled  3.4  3.4  3.3  3.3
3)    OK     3.3  3.3  3.4  3.4  3.4
4) Enrolled  3.4  3.4  3.4  3.4  3.4  3.4  3.4  3.4  3.4  3.4  3.3  3.4  3.3

This should work, assuming nothing else in the rows except for the monthly values:

=IF(SUM(
OFFSET(A2,0,COUNT(B2:XFD2))<3.4,
OFFSET(A2,0,COUNT(B2:XFD2)-1)<3.4,
OFFSET(A2,0,COUNT(B2:XFD2)-2)<3.4)>1,"Enrolled","OK")

Of course, this could be adjusted for a range that is smaller than the entire row, but if the next January wraps back to Column B, then things get tricky. I'm not going to deal with that until we know what we are working with.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

Related Solutions

#4
September 4, 2018 at 15:21:58
In my format, the months continue going off to the right so your formula worked perfectly. By the way, I
changed the options to "PIP" and "Passed." I wish I could interpret that formula :-)

EMPLOYEE	STATUS	2018											
		JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC
John	PIP	3.80	3.65	3.30	3.65	3.40	3.30	4.20	3.10				
James	Passed	3.80	3.40	4.10	4.10	3.55	4.10	3.40	3.40				

What I did next was removed those who have a "PIP" status and put them in Sheet 2 because they are
now subject to corrective action if they fail to meet a score of 3.40 within a six month cleansing period.
In my example, John got enrolled in PIP in September.

EMPLOYEE	STATUS	2018												2019											
		JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC	JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC
John		3.80	3.65	3.30	3.65	3.40	3.30	4.20	3.10																

I'm not sure if this is possible, but I want the status in Sheet 2 to default to "WW1" given that John is
already enrolled in PIP. In case he failed again within the six month cleansing period, the status would
change to "WW2". If he failed for the third time, status would be "S" and fourth would be "D". If he
passed within the six month cleansing period, his status would be changed to "Graduate".


Reply ↓  Report •

#5
September 4, 2018 at 16:07:16
Thank you as always

message edited by ChGatsby


Reply ↓  Report •

#6
September 4, 2018 at 19:23:27
re: "I wish I could interpret that formula"

Let's break it down, from the inside out...

=OFFSET(A2,0,COUNT(B2:XFD2))

The OFFSET function requires 3 arguments:

A cell reference, a Row offset number and a Column offset number.

In this case, we start with A2 as the cell reference, 0 as the Row offset, and use the COUNT function to determine the Column offset. The COUNT function simply counts the number of items in the referenced range. e.g in my example Row 2, there are 4 items in the range B2:XFD2, therefore the Column offset is 4. 4 columns to the right of A is E. That will return the value in E2, the last cell with data in Row 2.

Next:

=OFFSET(A2,0,COUNT(B2:XFD2))<3.4

That will return either TRUE or FALSE depending on the value in E2.

Next:

=OFFSET(A2,0,COUNT(B2:XFD2)-1)

See the "-1"? That lowers the Column offset by 1, returning the value in the next to the last cell with data in the Row, or D2.

Next:

=OFFSET(A2,0,COUNT(B2:XFD2)-1)<3.4

That will return either TRUE or FALSE depending on the value in D2

OFFSET(A2,0,COUNT(B2:XFD2)-2)<3.4

See the "-2"? You figure that one out. ;-)

Now we can widen our view a bit:

=SUM(
OFFSET(A2,0,COUNT(B2:XFD2))<3.4,
OFFSET(A2,0,COUNT(B2:XFD2)-1)<3.4,
OFFSET(A2,0,COUNT(B2:XFD2)-2)<3.4)

That will return either a TRUE or FALSE for each of the last 3 cells in a given Row and then SUM them. e.g...

=SUM(TRUE,FALSE,TRUE)
=SUM(FALSE,FALSE,FALSE)
etc.

When you SUM a Boolean function, you are SUMming a 1 (TRUE) or a 0 (FALSE)

=SUM(TRUE,FALSE,TRUE) is the same as =SUM(1,0,1) which equals 2.

Therefore IF we SUM the TRUE's and FALSE's returned by the 3 OFFSET functions, which look at the last 3 cells via the COUNT function, we can determine if at least 2 of those values are <3.4.

=IF(SUM(TRUE,FALSE,TRUE)>1,"Enrolled,"OK")

=IF(SUM(1,0,1)>1,"Enrolled,"OK")

=IF(2>1,"Enrolled","OK")

=Enrolled

I hope that makes sense.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Ask Question