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"

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.3I'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:

B2andC2andD2,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

andC2 are less than 3.40andD2 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 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()MIKE

message edited by mmcconaghy

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 have

Nov Dec

3.4 3.3It does not check December as there is no 3 cell range.

We could probably find a work around if necessary.MIKE

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, FebThere will always be a 3 month rolling period

looking backwardsstarting 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.3This 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

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.40What 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.10I'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".

Thank you as always

message edited by ChGatsby

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 cellwith 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

Ask Your Question

Weekly Poll

Do you think Slack's direct listing "IPO" is a good idea?

Discuss in The Lounge

Poll History