Giving a cell a certain text based on the grade of a student.

[timshel]

[FONT=&quot]Long time lurker, first time poster.

I have been struggling with this and hoping you all can help me out. I apologies if my explanations is really clear, it is kinda complex.[/FONT]
[FONT=&quot]https://imgur.com/a/N4lon[/FONT]
[FONT=&quot]So i have the spreadsheet above where there are the IDs in column F ( the are the family IDs), then the specifics students grade in column G. Some families have more than one student, but still the same ID for each child. In column K, i have a formula that goes through the IDs and counts how many times that occurs. So if the ID occurs once (meaning only one child, then it is "1", if the ID occurs twice, then "2", if three or more times, then "3" (anymore than three, the discount is the same as three).[/FONT]
[FONT=&quot]Here is my formula for column K: =IF(COUNTIF($F$20:$F$392,F20)=1,"1",IF(COUNTIF($F$20:$F$392,F20)=2,"2",IF(COUNTIF($F$20:$F$392,F20)>2,"3","")))[/FONT]
[FONT=&quot]What i have been trying to do: In column I, take the number of children data and have it changed to text says "1st child", "second sibling", "third sibling +". This is where it gets hard to explain. If the "Number of Children" is "1" then I want it to say "1st child" in column I and so fourth with the rest of them. But the tricky part is if it is "2 , 2" in the "Number of children" i want the "2" that lines up with the youngest child (grade wise) to label that the "second sibling" then the oldest "2" as the "first child". For the "3" , the youngest would be "third sibling", middle would be "second sibling" , oldest would be "first child.[/FONT]
[FONT=&quot]I was thinking of changing the "K" to zero, for simplicity purposes. Basically im trying to look at number of children (which comes from the IDs), if it is more than 1, compare it to the grades and categorize that based on the age of that specific child, under that family (ID field). [/FONT]
[FONT=&quot]Any help on this would be greatly appreciated. Open to suggestions. Probably the most complex thing ive worked on in excel. Thanks![/FONT]

 

[Special-K99]

You can change this entire formula

=IF(COUNTIF($F$20:$F$392,F20)=1,"1",IF(COUNTIF($F$20:$F$392,F20)=2,"2",IF(COUNTIF($F$20:$F$392,F20)>2,"3","")))

to this

=MIN(COUNTIF($F$20:$F$392,F20),3)

and custom format the cell with a type of

0;-0;;@

to suppress zeroes

 

[inactiveuserps07]

@timshel, I sent you a message re: your file upload.

Keep in mind that there are other factors you'll need to account for, such as the use of "K" or "EPS," and the situation where twins or siblings share the same grade at any level. And then, of course, there is retrieving the correct text label, once the numbers have been correctly assigned based on all factors. I can account for these, but I'll need to see the actual file.

@Special-K99, I think your MIN formula is just going to count the number of students per family, not order them top-down by grade (i.e., it will label all four children in the same family as "3," when it appears the OP wants the oldest student marked as "1," the next-oldest as "2," the next oldest as "3" and the youngest as "3").

 

[Special-K99]

@timshel, I sent you a message re: your file upload.

Keep in mind that there are other factors you'll need to account for, such as the use of "K" or "EPS," and the situation where twins or siblings share the same grade at any level. And then, of course, there is retrieving the correct text label, once the numbers have been correctly assigned based on all factors. I can account for these, but I'll need to see the actual file.

@Special-K99, I think your MIN formula is just going to count the number of students per family, not order them top-down by grade (i.e., it will label all four children in the same family as "3," when it appears the OP wants the oldest student marked as "1," the next-oldest as "2," the next oldest as "3" and the youngest as "3").

Yep, I was only simplifying the original formula.