If statement with multi criteria

[zinah]

Hi,

I have below list and need help with adjusting the current formulas to meet the logic below:

"if any employee received merit or promotion before official date, or received promotion on official date, or merit transaction and increase % is more than budget %, then R, otherwise NR".
Another issue I noticed, with Column G formula, the current formula shows "before" when it's = 4/1/2023 and what I need is = show after if it's after 4/1/2023 and before if it's 4/1/2023, otherwise "official date".

Can anyone help please?

If logic with specific criteria.xlsx
	A	B	C	D	E	F	G
1		Official Date	4/1/2023
2	EEID	Effective Date	Increase Percent	Budget %	Transaction Type	Designation	before or after official date
3	Employee 1	4/1/2023	5.00%	4.50%	Merit > Merit	R	before
4	Employee 2	4/1/2023	4.60%	4.50%	Merit > Merit	R	before
5	Employee 3	4/1/2023	4.50%	4.50%	Merit > Merit	NR	before
6	Employee 4	4/1/2023	4.00%	4.50%	Merit > Merit	NR	before
7	Employee 5	4/1/2023	6.00%	4.50%	Merit > Merit	R	before
8	Employee 6	4/1/2023	11.00%	4.50%	Promotion	R	before
9	Employee 7	1/1/2023	20.00%	30.00%	Change	NR	before
10	Employee 7	7/1/2023	20.00%	30.00%	Promotion	R	after
11	Employee 9	4/1/2023	20.00%	30.00%	Merit > Merit	NR	before
12	Employee 10	1/1/2023	20.00%	30.00%	Change	NR	before
13	Employee 11	7/1/2023	20.00%	30.00%	Change	R	after
14	Employee 12	4/1/2023	20.00%	30.00%	Merit > Merit	NR	before
15	Employee 13	4/1/2023	10.00%	10.00%	Merit > Merit	NR	before
16	Employee 14	4/1/2023	10.00%	10.00%	Merit > Merit	NR	before
17	Employee 15	4/1/2023	7.50%	4.50%	Merit > Merit	R	before
18	Employee 16	4/1/2023	15.00%	10.00%	Merit > Merit	R	before
19	Employee 17	4/1/2023	3.00%	4.50%	Merit > Merit	NR	before
20	Employee 18	4/1/2023	4.60%	4.50%	Merit > Merit	R	before
21	Employee 19	1/1/2023	20.00%	30.00%	Change	NR	before
22	Employee 20	1/1/2023	20.00%	30.00%	Change	NR	before
23	Employee 21	7/1/2023	20.00%	30.00%	Change	R	after
24	Employee 7	4/1/2023	20.00%	30.00%	Merit > Merit	NR	before
25	Employee 23	1/1/2023	20.00%	30.00%	Change	NR	before
26	Employee 24	4/1/2023	4.50%	4.50%	Merit > Merit	NR	before
27	Employee 25	1/1/2023	20.00%	30.00%	Change	NR	before
28	Employee 26	7/1/2023	20.00%	30.00%	Change	R	after
29	Employee 27	4/1/2023	20.00%	30.00%	Merit > Merit	NR	before
30	Employee 28	1/1/2023	20.00%	30.00%	Change	NR	before
31	Employee 29	7/1/2023	20.00%	30.00%	Change	R	after
32	Employee 30	4/1/2023	20.00%	30.00%	Merit > Merit	NR	before
33	Employee 31	4/1/2023	10.00%	10.00%	Merit > Merit	NR	before
34	Employee 32	4/1/2023	4.70%	4.50%	Merit > Merit	R	before
35	Employee 33	4/1/2023	6.00%	4.50%	Merit > Merit	R	before
36	Employee 34	4/1/2023	4.50%	4.50%	Merit > Merit	NR	before
37	Employee 35	4/1/2023	17.00%	10.00%	Merit > Merit	R	before
38	Employee 36	4/1/2023	8.00%	10.00%	Merit > Merit	NR	before
39	Employee 37	4/1/2023	3.70%	10.00%	Merit > Merit	NR	before
40	Employee 38	1/1/2023	3.70%	10.00%	Promotion	NR	before
41	Employee 39	4/1/2023	3.00%	4.50%	Merit > Merit	NR	before
42	Employee 40	4/1/2023	4.00%	4.50%	Merit > Merit	NR	before
43	Employee 41	4/1/2023	4.55%	4.50%	Merit > Merit	R	before
44	Employee 42	4/1/2023	5.00%	4.50%	Merit > Merit	R	before
45	Employee 43	4/1/2023	4.50%	4.50%	Merit > Merit	NR	before
46	Employee 44	4/1/2023	10.00%	5.00%	Merit > Merit	R	before
47	Employee 45	4/1/2023	4.50%	4.50%	Merit > Merit	NR	before
48	Employee 46	4/1/2023	3.50%	4.50%	Merit > Merit	NR	before
49	Employee 47	4/1/2023	4.75%	4.50%	Merit > Merit	R	before
50	Employee 1	1/1/2023	4.75%	4.50%	Promotion	R	before
51	Employee 1	2/1/2023	3.00%	4.50%	Change	NR	before
52	Employee 10	4/1/2023	20.00%	30.00%	Promotion	NR	before
53	Employee 14	1/1/2023	15.00%	10.00%	Promotion	R	before
54	Employee 17	7/1/2023	2.50%	4.50%	Promotion	R	after
55	Employee 28	7/1/2023	15.00%	30.00%	Promotion	R	after
Sheet1

Cell Formulas
Range	Formula
F3:F55	F3	=IF(OR(C3>D3,B3>$C$1),"R", "NR")
G3:G55	G3	=IF(B3>$C$1,"after","before")

 

[johnny51981]

For F3
=IF(OR(AND($E3="Merit > Merit",$B3<$C$1),AND($E3="Promotion",$B3<=$C$1),$C3>$D3),"R","NR")

For G3
=IF(B3>=$C$1,"after","before")

 

[zinah]

Hi,

I have below list and need help with adjusting the current formulas to meet the logic below:

"if any employee received merit or promotion before official date, or received promotion on official date, or merit transaction and increase % is more than budget %, then R, otherwise NR".
Another issue I noticed, with Column G formula, the current formula shows "before" when it's = 4/1/2023 and what I need is = show after if it's after 4/1/2023 and before if it's 4/1/2023, otherwise "official date".

Can anyone help please?

If logic with specific criteria.xlsx
	A	B	C	D	E	F	G
1		Official Date	4/1/2023
2	EEID	Effective Date	Increase Percent	Budget %	Transaction Type	Designation	before or after official date
3	Employee 1	4/1/2023	5.00%	4.50%	Merit > Merit	R	before
4	Employee 2	4/1/2023	4.60%	4.50%	Merit > Merit	R	before
5	Employee 3	4/1/2023	4.50%	4.50%	Merit > Merit	NR	before
6	Employee 4	4/1/2023	4.00%	4.50%	Merit > Merit	NR	before
7	Employee 5	4/1/2023	6.00%	4.50%	Merit > Merit	R	before
8	Employee 6	4/1/2023	11.00%	4.50%	Promotion	R	before
9	Employee 7	1/1/2023	20.00%	30.00%	Change	NR	before
10	Employee 7	7/1/2023	20.00%	30.00%	Promotion	R	after
11	Employee 9	4/1/2023	20.00%	30.00%	Merit > Merit	NR	before
12	Employee 10	1/1/2023	20.00%	30.00%	Change	NR	before
13	Employee 11	7/1/2023	20.00%	30.00%	Change	R	after
14	Employee 12	4/1/2023	20.00%	30.00%	Merit > Merit	NR	before
15	Employee 13	4/1/2023	10.00%	10.00%	Merit > Merit	NR	before
16	Employee 14	4/1/2023	10.00%	10.00%	Merit > Merit	NR	before
17	Employee 15	4/1/2023	7.50%	4.50%	Merit > Merit	R	before
18	Employee 16	4/1/2023	15.00%	10.00%	Merit > Merit	R	before
19	Employee 17	4/1/2023	3.00%	4.50%	Merit > Merit	NR	before
20	Employee 18	4/1/2023	4.60%	4.50%	Merit > Merit	R	before
21	Employee 19	1/1/2023	20.00%	30.00%	Change	NR	before
22	Employee 20	1/1/2023	20.00%	30.00%	Change	NR	before
23	Employee 21	7/1/2023	20.00%	30.00%	Change	R	after
24	Employee 7	4/1/2023	20.00%	30.00%	Merit > Merit	NR	before
25	Employee 23	1/1/2023	20.00%	30.00%	Change	NR	before
26	Employee 24	4/1/2023	4.50%	4.50%	Merit > Merit	NR	before
27	Employee 25	1/1/2023	20.00%	30.00%	Change	NR	before
28	Employee 26	7/1/2023	20.00%	30.00%	Change	R	after
29	Employee 27	4/1/2023	20.00%	30.00%	Merit > Merit	NR	before
30	Employee 28	1/1/2023	20.00%	30.00%	Change	NR	before
31	Employee 29	7/1/2023	20.00%	30.00%	Change	R	after
32	Employee 30	4/1/2023	20.00%	30.00%	Merit > Merit	NR	before
33	Employee 31	4/1/2023	10.00%	10.00%	Merit > Merit	NR	before
34	Employee 32	4/1/2023	4.70%	4.50%	Merit > Merit	R	before
35	Employee 33	4/1/2023	6.00%	4.50%	Merit > Merit	R	before
36	Employee 34	4/1/2023	4.50%	4.50%	Merit > Merit	NR	before
37	Employee 35	4/1/2023	17.00%	10.00%	Merit > Merit	R	before
38	Employee 36	4/1/2023	8.00%	10.00%	Merit > Merit	NR	before
39	Employee 37	4/1/2023	3.70%	10.00%	Merit > Merit	NR	before
40	Employee 38	1/1/2023	3.70%	10.00%	Promotion	NR	before
41	Employee 39	4/1/2023	3.00%	4.50%	Merit > Merit	NR	before
42	Employee 40	4/1/2023	4.00%	4.50%	Merit > Merit	NR	before
43	Employee 41	4/1/2023	4.55%	4.50%	Merit > Merit	R	before
44	Employee 42	4/1/2023	5.00%	4.50%	Merit > Merit	R	before
45	Employee 43	4/1/2023	4.50%	4.50%	Merit > Merit	NR	before
46	Employee 44	4/1/2023	10.00%	5.00%	Merit > Merit	R	before
47	Employee 45	4/1/2023	4.50%	4.50%	Merit > Merit	NR	before
48	Employee 46	4/1/2023	3.50%	4.50%	Merit > Merit	NR	before
49	Employee 47	4/1/2023	4.75%	4.50%	Merit > Merit	R	before
50	Employee 1	1/1/2023	4.75%	4.50%	Promotion	R	before
51	Employee 1	2/1/2023	3.00%	4.50%	Change	NR	before
52	Employee 10	4/1/2023	20.00%	30.00%	Promotion	NR	before
53	Employee 14	1/1/2023	15.00%	10.00%	Promotion	R	before
54	Employee 17	7/1/2023	2.50%	4.50%	Promotion	R	after
55	Employee 28	7/1/2023	15.00%	30.00%	Promotion	R	after
Sheet1

Cell Formulas
Range	Formula
F3:F55	F3	=IF(OR(C3>D3,B3>$C$1),"R", "NR")
G3:G55	G3	=IF(B3>$C$1,"after","before")

Hi, thanks for your suggestion. How about if I change the logic to be : if any employee received merit or promotion before official date, or received promotion on official date, or received merit on official date that is higher than budget %, or merit transaction and increase % is more than budget %, or promotion that is higher than budget % then R, otherwise NR".

As for suggested formula below, it gave me "after" for any effective date that is = 4/1/2023, which should not be the result, what I need is "after" for any date after this date, and before for any date before this date, maybe "blank" if it's = official date. any suggestion?

For G3
=IF(B3>=$C$1,"after","before")

 

[zinah]

Hi, can someone help please?

 

[zinah]

@Fluff would you be able to help "as usual"