IF statment from Excel to Airtable

[CPMSM]

Hello Formula Gurus- 

I am trying to convert some excel formulas to airtable and struggling a bit.  This is the formula in excel:

=IF(J2>=8.5,"A",IF(J2>=7.5,"A-",IF(J2>=6.5,"B+",IF(J2>=5.5,"B",IF(J2>=4.5,"B-",IF(J2>=3.5,"C+",IF(J2>=2.5,"C",IF(J2>=1.5,"D",IF(J2>=1,"F")))))))))

Any help is appreciated!  thank you

[Brian_Swanson]

Your formula is fine for airtable.  You just need to relace the cell identitation 'J2' with the name of the field that has the information for your formula.  For instance if your field was named 'Points' it would read:

IF({Points}>=8.5,'A',
  IF({Points}>=7.5,'A-',
     IF({Points}>=6.5,'B+',
        IF({Points}>=5.5,'B',
           IF({Points}>=4.5,'B-',
             IF({Points}>=3.5,'C+',
               IF({Points}>=2.5,'C',
                 IF({Points}>=1.5,'D',
                   IF({Points}>=1,'F'
                  )
                )
              )
            )
          )
        )
      )
    )   
  )

   

[CPMSM]

I must had been off a parenthesis or somehting, because I got an error earlier. THank you!

BUT...now I am being asked for something slightly different.

We want to grade one department under the rubic above and all others under a different rubic

If deparment = {piano}  (rubic above)

departments not equal to Piano (slighlty different rubic)

[Brian_Swanson]

I would likly do this by adding in the switch function.  For this I woudl create a field call 'Rubric' which Swtich() evalauted as being one or another condition.  In the example below I use the term 'Piano' or 'Instrumemental' as single select options in the 'Rubric' field.  The nice thing about switch is that if they keep adding new ideas you can just add the new condition rather easily with a new points/grade breakdown.

SWITCH({Rubric}, 'Piano',
  IF({Points}>=8.5,'A',
    IF({Points}>=7.5,'A-',
      IF({Points}>=6.5,'B+',
        IF({Points}>=5.5,'B',
          IF({Points}>=4.5,'B-',
            IF({Points}>=3.5,'C+',
              IF({Points}>=2.5,'C',
                IF({Points}>=1.5,'D',
                  IF({Points}>=1,'F'
                  )
                )
              )
            )
          )
        )
      )
    )   
  )
  , 'Instrumental',
    IF({Points}>=8,'A',
      IF({Points}>=7,'A-',
        IF({Points}>=6,'B+',
          IF({Points}>=5,'B',
            IF({Points}>=4,'B-',
              IF({Points}>=3,'C+',
                IF({Points}>=2,'C',
                  IF({Points}>=1,'D',
                    IF({Points}>=.5,'F'
                  )
                )
              )
            )
          )
        )
      )
    )   
  )
)

[CPMSM]

This is amazing, but it is not working. It comes back with errors.  Any thoughts? I reached out to someone at airtable that I worked with, but haven't heard back.  ???

[Brian_Swanson]

Sorry you are having trouble @CPMSM 

I'm not really sure why you might be getting an error.  I tested the formula in my test base before I sent it to you.  Here is a link to it working.  Perhaps that might help.   I remember getting a error at one point when I first wrote it and but the fix seemed simple at the time and I can't replicate it anymore.  https://airtable.com/shrmgwhgXo1GAgAxT

[CPMSM]

I started from scratch (new cell and all) and it now works! Thank you so much for introducing me to the new "switch" formula.
This is outside my wheelhouse, so I appreciate all your help. 🙂

[Brian_Swanson]

Anytime!!