Creating A Graduation Spreadsheet

[Chef_Teacher_ExcelWannabe]

I am hoping to build a spreadsheet that helps sort students that are considered for credentialing and graduating high school.

What is easier...the spreadsheet reading colours or values?



This is where I am starting:

1) If the cell has an X or a Y I already have it turning green or red

Then I need some help with formulas :

2) If N6:U6 is Blank and M6>99 then K6 needs to read awarded and I would like W6:Z6 to be blank ( they have already graduated but are upgrading)

3) If the student is currently enrolled in a class and N6:U6 contains a Y then Pathway changes to the word diploma and under Forcast it reads Yes

4) If the student is currently failing a class and N6:U6 contains a X then Pathway remains diploma but forecast reads No

5) If Forecast is Yes then W6, Y6 would change to a Yes

6) If Forecast, Religion and School Fees are all Yes, Banquet changes to Yes.



Religion and School Fees are a manual entry of yes of no

 

[WaywardWes]

Can you post a higher res photo?

Most of this can be done using IF() or IFS(). For example, for #2, in K6 place:

=IF(AND(ISBLANK(N6:U6),M6>99), "AWARDED", "")

Now, there are multiple things you want to do in K6 so you can upgrade that to an IFS() like

=IFS(AND(ISBLANK(N6:U6),M6>99), "AWARDED", COUNTIF(N6:U6, "Y"), "DIPLOMA")

 

[Chef_Teacher_ExcelWannabe]

Can you post a higher res photo?

Most of this can be done using IF() or IFS(). For example, for #2, in K6 place:

=IF(AND(ISBLANK(N6:U6),M6>99), "AWARDED", "")

Now, there are multiple things you want to do in K6 so you can upgrade that to an IFS() like

=IFS(AND(ISBLANK(N6:U6),M6>99), "AWARDED", COUNTIF(N6:U6, "Y"), "DIPLOMA")

 

[Chef_Teacher_ExcelWannabe]

View attachment 93604

Thank you Wes,

When Pathway reads Awarded I need W6:Z6 to be blank

When Pathway Reads Diploma it gets more complicated... I need the following :
W6 (under composite) should check that Forecast reads Yes and then display yes, otherwise display no
Y6 (under commencement) should check that Forecast reads Yes and then display yes, otherwise display no
Z6 (under banquet) needs to check that L6 (Under forecast) reads yes, V6 (under religion) reads YES and School Fees Reads Yes to yield a yes, othersie it returns a no

can you "multilayer" formulas?

 

[WaywardWes]

You would do separate formulas for each of W6:Z6. Try these:

W6 & Y6

=IF($K6="AWARDED", "", IF(AND($K6="DIPLOMA", $L6="YES"), "YES", "NO")

Z6

=IF($K6="AWARDED", "", IF(AND($K6="DIPLOMA", $L6="YES", $V6="YES", $X6="YES"), "YES", "NO")

I locked the column but not the row so you can fill down. I'm assuming "School Fees" is manually filled.

 

[Chef_Teacher_ExcelWannabe]

You would do separate formulas for each of W6:Z6. Try these:

W6 & Y6

=IF($K6="AWARDED", "", IF(AND($K6="DIPLOMA", $L6="YES"), "YES", "NO")

Z6

=IF($K6="AWARDED", "", IF(AND($K6="DIPLOMA", $L6="YES", $V6="YES", $X6="YES"), "YES", "NO")

I locked the column but not the row so you can fill down. I'm assuming "School Fees" is manually filled.

These seem to be working great!!!! This is awesome!
Next though....

Under Forecast... this is where it gets more complex :

If N6:U6 contain a blank or a "Y" I need Forcast to read YES
If N6:U6 contains an X I need Forecast to read NO

I am trying this and it is not working : =IF(OR(ISNUMBER(SEARCH("Y",A2)),ISNUMBER(SEARCH("",A2))),"YES","NO")
If there are any values in n6:u6 I need Pathways to switch to Diploma

This is so much help!

 

[Chef_Teacher_ExcelWannabe]

These seem to be working great!!!! This is awesome!
Next though....

Under Forecast... this is where it gets more complex :

If N6:U6 contain a blank or a "Y" I need Forcast to read YES
If N6:U6 contains an X I need Forecast to read NO

I am trying this and it is not working : =IF(OR(ISNUMBER(SEARCH("Y",A2)),ISNUMBER(SEARCH("",A2))),"YES","NO")
If there are any values in n6:u6 I need Pathways to switch to Diploma

This is so much help!

and also, how do I have Forecast display as blank if Pathway reads Awarded

 

[WaywardWes]

and also, how do I have Forecast display as blank if Pathway reads Awarded

=IF($K6="AWARDED", "")

You would need to nestle the other options there too, or else it'll give you an error if it's not 'awarded.'

These seem to be working great!!!! This is awesome!
Next though....

Under Forecast... this is where it gets more complex :

If N6:U6 contain a blank or a "Y" I need Forcast to read YES
If N6:U6 contains an X I need Forecast to read NO

I am trying this and it is not working : =IF(OR(ISNUMBER(SEARCH("Y",A2)),ISNUMBER(SEARCH("",A2))),"YES","NO")
If there are any values in n6:u6 I need Pathways to switch to Diploma

This is so much help!

Use COUNTIF similar to my first post. Try this...

=IF(COUNTIF(N6:U6, "X"), "NO", "YES")

It sounds like X, Y, and blank are the only possible inputs, so this way if there's an X in there you get NO, otherwise you get a YES.