{"text_format":"1","workbook":"xlsx","n_sheets":1,"sheets":["Sheet1"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":0,"comments":1,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Sheet1","index":0,"n_rows":37,"n_cols":6,"rows_kept":37,"cut":false,"hidden":false,"merged":[]}
["NESTED IF & IFS FUNCTIONS"]
["In the following table we convert a numeric grade to a letter grade"]
["with a nested IF. Build the function in two steps, column C then D."]
["Anything below 80 is a \"C\". Important note: there are only THREE"]
["possible answers to the letter grade: A, B, or C."]
[]
[null,"Grade","1st IF","2nd IF"]
[null,75,{"$f":"IF(B8>=90,\"A\",\"Not A\")","v":"Not A"},{"$f":"IF(B8>=90,\"A\",IF(B8>=80,\"B\",\"C\"))","v":"C"},{"$f":"getformula(D8)","v":{"$error":"#NAME?"}}]
[null,80,{"$f":"IF(B9>=90,\"A\",\"Not A\")","v":"Not A"},{"$f":"IF(B9>=90,\"A\",IF(B9>=80,\"B\",\"C\"))","v":"B"},{"$f":"getformula(D9)","v":{"$error":"#NAME?"}}]
[null,85,"Not A","B",{"$f":"getformula(D10)","v":{"$error":"#NAME?"}}]
[null,90,{"$f":"IF(B11>=90,\"A\",\"Not A\")","v":"A"},"A",{"$f":"getformula(D11)","v":{"$error":"#NAME?"}}]
[null,99,"A","A",{"$f":"getformula(D12)","v":{"$error":"#NAME?"}}]
[null,100,"A","A",{"$f":"getformula(D13)","v":{"$error":"#NAME?"}}]
[]
["IF there are two possible \"answers\" to the cell value then"]
["only one IF is needed. Each additional possible answer"]
["requires another nested IF as in column D above."]
[]
["Suppose we add one new letter grade, A+, for a score of 100."]
["Column E adds another IF to formulas in Column D. "]
[null,"Grade","1st IF","2nd IF","With A+"]
[null,75,{"$f":"IF(B22>=90,\"A\",\"Not A\")","v":"Not A"},{"$f":"IF(B22>=90,\"A\",IF(B22>=80,\"B\",\"C\"))","v":"C"},{"$f":"IF(B22=100,\"A+\",IF(B22>=90,\"A\",IF(B22>=80,\"B\",\"C\")))","v":"C"},{"$f":"getformula(E22)","v":{"$error":"#NAME?"}}]
[null,80,{"$f":"IF(B23>=90,\"A\",\"Not A\")","v":"Not A"},"B","B",{"$f":"getformula(E23)","v":{"$error":"#NAME?"}}]
[null,85,"Not A","B","B",{"$f":"getformula(E24)","v":{"$error":"#NAME?"}}]
[null,90,"A","A","A",{"$f":"getformula(E25)","v":{"$error":"#NAME?"}}]
[null,99,"A","A","A",{"$f":"getformula(E26)","v":{"$error":"#NAME?"}}]
[null,100,"A","A","A+",{"$f":"getformula(E27)","v":{"$error":"#NAME?"}}]
[]
["Another way to accomplish this is to use one IFS function."]
[null,"Grade","IFS"]
[null,75,{"$f":"_xlfn.IFS(B31>=100,\"A+\",B31>=90,\"A\",B31>=80,\"B\",B31<80,\"C\")","v":"C"},{"$f":"getformula(C31)","v":{"$error":"#NAME?"}}]
[null,80,{"$f":"_xlfn.IFS(B32>=100,\"A+\",B32>=90,\"A\",B32>=80,\"B\",B32<80,\"C\")","v":"B"},{"$f":"getformula(C32)","v":{"$error":"#NAME?"}}]
[null,85,{"$f":"_xlfn.IFS(B33>=100,\"A+\",B33>=90,\"A\",B33>=80,\"B\",B33<80,\"C\")","v":"B"},{"$f":"getformula(C33)","v":{"$error":"#NAME?"}}]
[null,90,{"$f":"_xlfn.IFS(B34>=100,\"A+\",B34>=90,\"A\",B34>=80,\"B\",B34<80,\"C\")","v":"A"},{"$f":"getformula(C34)","v":{"$error":"#NAME?"}}]
[null,99,{"$f":"_xlfn.IFS(B35>=100,\"A+\",B35>=90,\"A\",B35>=80,\"B\",B35<80,\"C\")","v":"A"},{"$f":"getformula(C35)","v":{"$error":"#NAME?"}}]
[null,100,{"$f":"_xlfn.IFS(B36>=100,\"A+\",B36>=90,\"A\",B36>=80,\"B\",B36<80,\"C\")","v":"A+"},{"$f":"getformula(C36)","v":{"$error":"#NAME?"}}]
["The IFS function stops and applies the \"answer\" to the first TRUE condition."]