I use Airtable to help visualize and share data for our high school. Currently, I have a view that is set up with the following columns:
Student (Text Strong) 1st Block Grade (Number) 2nd Block Grade (Number) 3rd Block Grade (Number) 4th Block Grade (Number) 5th Block Grade (Number)
I added another column, a formula column, that was intended to display the number of classes each student was failing. The formula works at each level, but the formula itself is too long. Airtable truncates it. I am self-taught with regard to this sort of stuff, so there is probably a more elegant way to do this that fits within Airtable’s capabilities. I anticipate that someone will ask why I do not create a view and use the filter to achieve what I want, and my answer is that I would have to redo the filter every 9-weeks when new grades are being uploaded. With this formula, I can update the column names using Ctrl+F in Google Docs, then copy/paste it into the formula field. Any suggestions? Thank you!
I have pasted the formula I wrote below, with spaces to separate out each IF logical statement.
IF(AND({2nd 9 Grd 1}<70,{2nd 9 Grd 2}<70,{2nd 9 Grd 3}<70,{2nd 9 Grd 4}<70,{2nd 9 Grd 5}<70),“5”,
IF(OR(AND({2nd 9 Grd 1}<70,{2nd 9 Grd 2}<70,{2nd 9 Grd 3}<70,{2nd 9 Grd 4}<70),AND({2nd 9 Grd 1}<70,{2nd 9 Grd 2}<70,{2nd 9 Grd 3}<70,{2nd 9 Grd 5}<70),AND({2nd 9 Grd 1}<70,{2nd 9 Grd 3}<70,{2nd 9 Grd 4}<70,{2nd 9 Grd 5}<70),AND({2nd 9 Grd 2}<70,{2nd 9 Grd 3}<70,{2nd 9 Grd 4}<70,{2nd 9 Grd 5}<70)),“4”,
IF(OR(AND({2nd 9 Grd 1}<70,{2nd 9 Grd 2}<70,{2nd 9 Grd 3}<70),AND({2nd 9 Grd 1}<70,{2nd 9 Grd 3}<70,{2nd 9 Grd 4}<70),AND({2nd 9 Grd 1}<70,{2nd 9 Grd 4}<70,{2nd 9 Grd 5}<70),AND({2nd 9 Grd 2}<70,{2nd 9 Grd 3}<70,{2nd 9 Grd 4}<70),AND({2nd 9 Grd 2}<70,{2nd 9 Grd 4}<70,{2nd 9 Grd 5}<70),AND({2nd 9 Grd 3}<70,{2nd 9 Grd 4}<70,{2nd 9 Grd 5}<70)),“3”,
IF(OR(AND({2nd 9 Grd 1}<70,{2nd 9 Grd 2}<70),AND({2nd 9 Grd 1}<70,{2nd 9 Grd 3}<70),AND({2nd 9 Grd 1}<70,{2nd 9 Grd 4}<70),AND({2nd 9 Grd 1}<70,{2nd 9 Grd 5}<70),AND({2nd 9 Grd 2}<70,{2nd 9 Grd 3}<70),AND({2nd 9 Grd 2}<70,{2nd 9 Grd 4}<70),AND({2nd 9 Grd 2}<70,{2nd 9 Grd 5}<70),AND({2nd 9 Grd 3}<70,{2nd 9 Grd 4}<70),AND({2nd 9 Grd 3}<70,{2nd 9 Grd 5}<70),AND({2nd 9 Grd 4}<70,{2nd 9 Grd 5}<70)),“2”,
IF(OR({2nd 9 Grd 1}<70,{2nd 9 Grd 2}<70,{2nd 9 Grd 3}<70,{2nd 9 Grd 4}<70),{2nd 9 Grd 5}<70),“1”,“0”))))