Offset with If function

Cell C4 contains the formula,

{=IF(AVERAGE(N(OFFSET($A4,0,LARGE(COLUMN(A:BC)*ISNUMBER(A4:BC4)-1,{1,2}))))>=9.5,"Review 4 Wks","Review 2 Wks")}


The last score is 8.0, in cell AD4. C4 returns "review 2 wks" as it should. AE4, I have set as "exempt" as every other week will be exempt from scoring no matter what.

I would like AF4 to say "Review", as this is 2 weeks from the last score. If the score entered in AF4 is still below the threshold, AH4 should say review, but if it is above it, AH4 should say exempt, and AJ4 should say "review." (This is 4 weeks from a good score in AF4)

Thanks so much. I hope this makes sense.

Attached a sample as well.

example.xlsx

Hi there,

Thanks for the response. The last two scores were entered on 3/8 and then again on 4/5. The requirements state that if the average of the last two scores fall below 9.5, the employee is required to be scored again in two weeks (4/19). If they score an average above 9.5, they will need to be scored again in 4 weeks (5/3)

Thanks for your help! Although I'm not nearly as advanced as some members of the board, I hope to be able to help others in simpler threads as well!

This works wonderfully! I have one other iteration of this that I am not sure how to edit, if you would not mind...

<9 Review the next week
9.0 - 9.4, Review in 4 weeks
9.5 - 9.9, Review in 8 weeks
10 - Review in 12 weeks.

Thanks again, you have been an invaluable resource!

Gave it the college try, attempting to follow your pattern:
{=IF(AND(AVERAGE(OFFSET($A4,0,LARGE(IF(ISNUMBER($Y4:AD4),COLUMN($Y4:AD4)-1,""),{1,2})))<9.0,MAX(IF(ISNUMBER($Y4:AD4),COLUMN($Y4:AD4),""))+1=COLUMN()),"Review",IF(AND(AVERAGE(OFFSET($A4,0,LARGE(IF(ISNUMBER($Y4:AD4),COLUMN($Y4:AD4)-1,""),{1,2})))>9.0,<9.49,MAX(IF(ISNUMBER($Y4:AD4),COLUMN($Y4:AD4),""))+4=COLUMN()),"Review",IF(AND(AVERAGE(OFFSET($A4,0,LARGE(IF(ISNUMBER($Y4:AD4),COLUMN($Y4:AD4)-1,""),{1,2})))>9.5,<9.99,MAX(IF(ISNUMBER($Y4:AD4),COLUMN($Y4:AD4),""))+8=COLUMN()),"Review",IF(AND(AVERAGE(OFFSET($A4,0,LARGE(IF(ISNUMBER($Y4:AD4),COLUMN($Y4:AD4)-1,""),{1,2})))=10,MAX(IF(ISNUMBER($Y4:AD4),COLUMN($Y4:AD4),""))+12=COLUMN()),"Review","Exempt"))))}

Excel is then nice enough to tell me I have an error in my formula... :-)