Help with IF Statement

[minz_999]

Hi all, I have been really struggling with this question I was wondering if anybody could help.

1). A student receives a UCPD award if they have completed 3 modules successfully and a UCAPD if they have completed 6 modules successfully. In column N enter a nested IF formula which determines whether a student gets a UCPD, UCAPD or neither.

The main bit which is confusing me is what if the number is more than 3 but less than 6, it needs to display UCPD but its not working. I am not sure what to do at the moment.

I have tried multiple IF statements however nothing seems to be working, this is what I have at the moment:

=IF(M3<3,"Neither",IF(AND(M3=3,M3>=5,"UCPD"),IF(M3>=6,"UCAPD")))

the column which the data is on:

Number of modules Passed
5
1
2
6
7
2
7
3

 

[kweaver]

Is this what you mean?

Book4
	M	N
3	5	UCPD
4	1	Neither
5	2	Neither
6	6	UCAPD
7	7	UCAPD
8	2	Neither
9	7	UCAPD
10	3	UCPD
Sheet1

Cell Formulas
Range	Formula
N3:N10	N3	=IF(M3<3,"Neither",IF(M3>=6,"UCAPD",IF(AND(M3>=3,M3<6),"UCPD")))

 

[minz_999]

Is this what you mean?

Book4
	M	N
3	5	UCPD
4	1	Neither
5	2	Neither
6	6	UCAPD
7	7	UCAPD
8	2	Neither
9	7	UCAPD
10	3	UCPD
Sheet1

Cell Formulas
Range	Formula
N3:N10	N3	=IF(M3<3,"Neither",IF(M3>=6,"UCAPD",IF(AND(M3>=3,M3<6),"UCPD")))

Yes, could you explain so I understand better. Thank you.

 

[kweaver]

Similar to what you wrote, but tested for >=6 in the 2nd condition, then the "and" range in the last condition.

You could also write:

Book4
	M	N	O
3	5	UCPD	UCPD
4	1	Neither	Neither
5	2	Neither	Neither
6	6	UCAPD	UCAPD
7	7	UCAPD	UCAPD
8	2	Neither	Neither
9	7	UCAPD	UCAPD
10	3	UCPD	UCPD
Sheet1

Cell Formulas
Range	Formula
N3:N10	N3	=IF(M3<3,"Neither",IF(M3>=6,"UCAPD",IF(AND(M3>=3,M3<6),"UCPD")))
O3:O10	O3	=IF(M3<3,"Neither",IF(AND(M3>=3,M3<6),"UCPD",IF(M3>=6,"UCAPD")))