Yes, no if a retest passed.

[rookzie]

Hello,

I have been Scratching my head with this one for a bit. I have a Microsoft forums quiz that feeds a spread sheet. if the student scores below 90% they need to retake this quiz with in 60 days.

I have added the % formula to get what there score is and a simply If statement to indicate weather or not a retest is required (Yes/No) and another Column that adds 60 days to the date they failed with highlight red if its past due.

next piece is a y/n if the retest has been done and passed. below is a table of how it looks. looing for a formula for the red Column.

Students name	Date	Score Formula	Follow-up required	Date follow up required	Follow up done (Y/N)
John Blitz	2/3/23	60%	Yes	4/4/23	Y
John Blitz	4/6/23	95%	No	-	-
Sam Bell	4/17/23	100%	Yes	-	-

 

[jdellasala]

I STRONGLY suggest you turn the data into a Table.

Book1
	A	B	C	D	E	F
1	Students name	Date	Score Formula	Follow-up required	Date follow up required	Follow up done (Y/N)
2	John Blitz	02/03/2023	60%	Yes	04/04/2023	Y
3	John Blitz	04/06/2023	95%	No	-	-
4	Sam Bell	04/17/2023	100%	No	-	-
Sheet1

Cell Formulas
Range	Formula
D2:D4	D2	=IF([@[Score Formula]]<90%,"Yes","No")
E2:E4	E2	=IF([@[Follow-up required]]="Yes",B2+60,"-")
F2:F4	F2	=IFS(AND([@[Follow-up required]]="Yes",XLOOKUP([@[Students name]],[Students name],[Follow-up required],,0,-1)="No"),"Y",[@[Follow-up required]]="Yes","N",TRUE,"-")

The reason being so that the formulas automatically flow down to the next Student.
Also, I provided 365/2021 formulas. If you're using older versions it will be a bit trickier, but not much. I suggest using XL2BB to post data, and update your profile to show what version of Excel you're using and on what OS.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Another option

=IF(D2="Yes",IF(COUNTIFS(A:A,A2,D:D,"No"),"y","n"),"-")

 

[rookzie]

Thank you, I do in fact have it set up as a table.

When i copy this formula it doesnt return correctly. Show all "N" when some have had a follow up.

here is the formal i got out of what you provided.

=IFS(AND([@[Follow up required? ]]="yes",XLOOKUP([@[Manager''s Name]],[@[Manager''s Name]],[@[Follow up required? ]],,0,-1)="No"),"N",[@[Follow up required? ]]="Yes","Y",TRUE,"-")

 

[rookzie]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Another option

=IF(D2="Yes",IF(COUNTIFS(A:A,A2,D:D,"No"),"y","n"),"-")

This worked, Thank you!

 

[jdellasala]

Thank you, I do in fact have it set up as a table.

When i copy this formula it doesnt return correctly. Show all "N" when some have had a follow up.

here is the formal i got out of what you provided.

=IFS(AND([@[Follow up required? ]]="yes",XLOOKUP([@[Manager''s Name]],[@[Manager''s Name]],[@[Follow up required? ]],,0,-1)="No"),"N",[@[Follow up required? ]]="Yes","Y",TRUE,"-")

Bummer. Works for me! I just copied this from my post and it appears to be working:

=IFS(AND([@[Follow-up required]]="Yes",XLOOKUP([@[Students name]],[Students name],[Follow-up required],,0,-1)="No"),"Y",[@[Follow-up required]]="Yes","N",TRUE,"-")

That formula works. If you pasted an exact copy of what you have, this may be the problem:
=IFS(AND([@[Follow up required? ]]="yes",XLOOKUP([@[Manager''s Name]],[@[Manager''s Name]],[@[Follow up required? ]],,0,-1)="No"),"N",[@[Follow up required? ]]="Yes","Y",TRUE,"-")
The "yes" in the AND function is case sensitive.

 

[rookzie]

Bummer. Works for me! I just copied this from my post and it appears to be working:

=IFS(AND([@[Follow-up required]]="Yes",XLOOKUP([@[Students name]],[Students name],[Follow-up required],,0,-1)="No"),"Y",[@[Follow-up required]]="Yes","N",TRUE,"-")

That formula works. If you pasted an exact copy of what you have, this may be the problem:
=IFS(AND([@[Follow up required? ]]="yes",XLOOKUP([@[Manager''s Name]],[@[Manager''s Name]],[@[Follow up required? ]],,0,-1)="No"),"N",[@[Follow up required? ]]="Yes","Y",TRUE,"-")
The "yes" in the AND function is case sensitive.

Thank you for taking the time to help. I got it to work now!

 

[Fluff]

This worked, Thank you!

You're welcome & thanks for the feedback.

 

[Fluff]

The "yes" in the AND function is case sensitive.

Actually it isn't.

 

[jdellasala]

Actually it isn't.

Thought that was the problem, guess not!