# 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

#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 monthI 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 A22) On the ribbon click Conditional Formatting3) 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 button7) Select the Fill Tab8) Select a pretty color9) Click OK10) Click OKThe 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()MIKEmessage edited by mmcconaghy

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 E2But 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 haveNov Dec3.4 3.3It does not check December as there is no 3 cell range.We could probably find a work around if necessary.MIKEhttp://www.skeptic.com/

Report •

#3
September 4, 2018 at 12:23:56
 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, DecNov, Dec, JanDec, Jan, FebThere 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.message edited by DerbyDad03

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".

Report •

#5
September 4, 2018 at 16:07:16
 Thank you as alwaysmessage edited by ChGatsby

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.4That 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 D2OFFSET(A2,0,COUNT(B2:XFD2)-2)<3.4See 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")=EnrolledI hope that makes sense.

Report •