Formula to get which starting month did a person get a consistent rating of at least 3

[joni101]

I am wondering if there is a way to determine which month did an employee get a consistent performance of at least 3. The problem is we have an employees who have no available data for certain months tagged as N/A. I'm not sure if an array can be used to perform what is needed.

Your help will be highly appreciated.

 

[Toadstool]

Hi Joni101,

I would assume N/A should be ignored, or do you want it treated as less than 3?
Can I assume "Consistent" means up to the last month for that Employee?

i.e. report the first date for an employee above 3 where no later entries are less than 3 or later entries are N/A

 

[joni101]

Hi Toadstool,

N?A can be treated as less than 3. For the consistent part, I only need to know the first 2 consecutive month the employee became consistent in getting 3 or higher rating.

 

[Peter_SSs]

Welcome to the MrExcel board!

See if this does what you want.

21 09 23.xlsm
	A	B	C	D	E	F
1
2
3	Jan-21	A	4		A	Jun-21
4	Feb-21	A	2		B	May-21
5	Mar-21	A	N/A
6	Apr-21	A	3
7	May-21	A	2
8	Jun-21	A	4
9	Jul-21	A	5
10	Jan-21	B	2
11	Feb-21	B	3
12	Mar-21	B	N/A
13	Apr-21	B	2
14	May-21	B	4
15	Jun-21	B	4
16	Jul-21	B	3
joni101

Cell Formulas
Range	Formula
F3:F4	F3	=AGGREGATE(15,6,A$3:A$15/((B$3:B$15=E3)*(C$3:C$15>=3)*($C$3:$C$15<>"N/A")*(B$4:B$16=E3)*(C$4:C$16>=3)*($C$4:$C$16<>"N/A")),1)