{"text_format":"1","workbook":"xlsx","n_sheets":1,"sheets":["Notes"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":1,"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":"Notes","index":0,"n_rows":107,"n_cols":10,"rows_kept":107,"cut":false,"hidden":false,"merged":[]}
["Supplemental Assignment Notes"]
["IF Function",null,"https://www.youtube.com/watch?v=KkTaQ5OjAGc"]
[null,null,"Use this function when the result for a cell is one of two"]
[null,null,"possible values:"]
[null,null,"=IF(condition,cell value if condition is true,cell value if condition is false)"]
[null,null,"Examples:"]
[null,null,1,{"$f":"IF(C7<>1,999,\"Error!\")","v":"Error!"},{"$f":"getformula(D7)","v":"<--  =IF(C7<>1,999,\"Error!\")"}]
[null,null,"Text",{"$f":"IF(C8<>1,999,\"OK\")","v":999},{"$f":"getformula(D8)","v":"<--  =IF(C8<>1,999,\"OK\")"}]
[null,null,2.44,{"$f":"IF(C9^2/(1+C9/100)>=1,1+C9/100,999)","v":1.0244},{"$f":"getformula(D9)","v":"<--  =IF(C9^2/(1+C9/100)>=1,1+C9/100,999)"}]
[null,null,2.44,{"$f":"IF(C10^2/(1+C10/100)>=1,ROUND(1+C10/100,2),999)","v":1.02},{"$f":"getformula(D10)","v":"<--  =IF(C10^2/(1+C10/100)>=1,ROUND(1+C10/100,2),999)"}]
[null,null,"Note that each of the 3 arguments in the IF function may be"]
[null,null,"elaborate \"formulas\" with other functions and cell references."]
[null,null,"It is not unusual to see an IF function as an argument to an"]
[null,null,"IF function."]
[null,"Order (low to high) in IF test:"]
[null,"empty cell -->"]
[null,"0 -->",0,{"$f":"C17=C16","v":true},{"$f":"getformula(D17)","v":"<--  =C17=C16"},"<-- Empty cell and zero considered Equal!"]
[null,"1 -->",1,{"$f":"C18>C17","v":true},{"$f":"getformula(D18)","v":"<--  =C18>C17"}]
[null,"2 -->",2,{"$f":"C19>C18","v":true},"<--  =C19>C18",null,"BUT, these 2 cells are equal!:"]
[null,"3 -->",3,true,"<--  =C20>C19",null,"2 double \" -->",{"$f":"\"\"","v":""},{"$f":"getformula(H20)","v":"<--  =\"\""}]
[null,"99 -->",99,true,"<--  =C21>C20",null,"empty cell -->",null,{"$f":"getformula(H21)","v":"<--  "}]
[null,"2 double \" -->",{"$f":"\"\"","v":""},true,"<--  =C22>C21",null,null,{"$f":"H20=H21","v":true},{"$f":"getformula(H22)","v":"<--  =H20=H21"}]
[null,"space -->"," ",true,"<--  =C23>C22"]
[null,"! -->","!",true,"<--  =C24>C23"]
[null,"# -->","#",true,"<--  =C25>C24"]
[null,"@ -->","@",true,"<--  =C26>C25"]
[null,"a -->","a",true,"<--  =C27>C26"]
[null,"z -->","z",true,"<--  =C28>C27"]
["Don’t use a math operator (+-*/^) on a text cell!",null,null,null,null,null,"But this works:"]
[null,"Text",5,{"$f":"B30*C30","v":{"$error":"#VALUE!"}},{"$f":"getformula(D30)","v":"<--  =B30*C30"},null,"Number as TEXT"]
[null,"Over",4,{"$f":"B31+1","v":{"$error":"#VALUE!"}},{"$f":"getformula(D31)","v":"<--  =B31+1"},null,{"$f":"\"222\"","v":"222"},4,{"$f":"G31+H31","v":226},{"$f":"getformula(I31)","v":"<--  =G31+H31"}]
[null,"Under",3,{"$f":"B32/C32","v":{"$error":"#VALUE!"}},"<--  =B32/C32",null,{"$f":"\"333\"","v":"333"},3,{"$f":"G32/H32","v":111},"<--  =G32/H32"]
["Use apersand (&) to join (concatenate) pieces of text!"]
[null,"day",2,{"$f":"B34&C34","v":"day2"},{"$f":"getformula(D34)","v":"<--  =B34&C34"}]
[null,"Jim","Bob",{"$f":"B35&C35","v":"JimBob"},{"$f":"getformula(D35)","v":"<--  =B35&C35"}]
[null,"Jim","Bob",{"$f":"B36&\" \"&C36","v":"Jim Bob"},{"$f":"getformula(D36)","v":"<--  =B36&\" \"&C36"}]
["Rounding Functions",null,null,null,null,null,"FIXED Function: Converts value to text"]
[null,123.456,{"$f":"ROUND(B38,0)","v":123},{"$f":"getformula(C38)","v":"<--  =ROUND(B38,0)"},null,null,{"$f":"FIXED(C38,1)","v":"123.0"},{"$f":"getformula(G38)","v":"<--  =FIXED(C38,1)"}]
[null,123.456,{"$f":"ROUND(B39,1)","v":123.5},{"$f":"getformula(C39)","v":"<--  =ROUND(B39,1)"},null,null,{"$f":"FIXED(C39*100,0)","v":"12,350"},"<--  =FIXED(C39*100,0)"]
[null,123.456,{"$f":"ROUND(B40,-2)","v":100},"<--  =ROUND(B40,-2)",null,null,{"$f":"\"Number = \"&FIXED(C40*100,1)","v":"Number = 10,000.0"},"<--  =\"Number = \"&FIXED(C40*100,1)"]
[null,123.456,{"$f":"ROUNDUP(B41,1)","v":123.5},"<--  =ROUNDUP(B41,1)",null,null,{"$f":"\"X = \"&FIXED(C41*100,2)&\"%\"","v":"X = 12,350.00%"},"<--  =\"X = \"&FIXED(C41*100,2)&\"%\""]
[null,123.456,{"$f":"ROUNDUP(B42,2)","v":123.46000000000001},"<--  =ROUNDUP(B42,2)",null,null,{"$f":"\"Max = \"&FIXED(SUM(C38:C43)-33,2)&\" Units\"","v":"Max = 683.91 Units"},"<--  =\"Max = \"&FIXED(SUM(C38:C43)-33,2)&\" Units\""]
[null,123.456,{"$f":"ROUNDDOWN(B43,2)","v":123.45},"<--  =ROUNDDOWN(B43,2)"]
[null,123.456,{"$f":"ROUNDDOWN(B44,-2)","v":100},"<--  =ROUNDDOWN(B44,-2)"]
[null,{"$f":"SUM(B38:B44)","v":864.19200000000001},{"$f":"SUM(C38:C44)","v":816.91000000000008},{"$f":"\"<--Totals \"&IF(C45<>B45,\"Differ!\",\"Same!\")","v":"<--Totals Differ!"},null,{"$f":"getformula(D45)","v":"<--  =\"<--Totals \"&IF(C45<>B45,\"Differ!\",\"Same!\")"}]
[]
["AND/OR Functions"]
[null,0,"AND-->",{"$f":"AND(B48>=0,B49>=0,B50>=0,B51>=0)","v":true},{"$f":"getformula(D48)","v":"<--  =AND(B48>=0,B49>=0,B50>=0,B51>=0)"}]
[null,1,null,"ALL 4 logical tests must be True to get a TRUE answer!"]
[null,2,"OR-->",{"$f":"OR(B48>=0,B49>=0,B50>=0,B51>=0)","v":true},{"$f":"getformula(D50)","v":"<--  =OR(B48>=0,B49>=0,B50>=0,B51>=0)"}]
[null,3,null,"ANY of the 4 logical tests may be True for a TRUE answer!"]
["Data Validation"]
[null,"Use Data/Data Validation/Data Validation to constrain input cell values",null,null,null,null,null,null,null,"Data Validation List:"]
[null,null,"Settings: Specify what may be entered into this cell",null,null,null,null,null,null,"Robert"]
[null,null,"Input Message: Show this message when cell is selected",null,null,null,null,null,null,"Elaine"]
[null,null,"Error Alert: Show this message when value entered is incorrect",null,null,null,null,null,null,"Susan"]
[null,{"$f":"\"See Cell \"&CELL(\"address\",I53)&\" Above\"","v":"See Cell $I$53 Above"},null,null,{"$f":"getformula(B57)","v":"<--  =\"See Cell \"&CELL(\"address\",I53)&\" Above\""},null,null,null,null,"Thomas"]
["Conditional Formatting: Formula Rule",null,null,null,null,null,null,null,null,"Amelia"]
[null,"Use this command (Home/Conditional Formatting) to set an"]
[null,"alternate format based on conditions you specify."]
[null,1,"Start by selecting the cell or cells for which will display one"]
[null,null,"of two formats based on a formula that is true or false."]
[null,2,"Select: Home/Conditional Formatting/New Rule/Use a formula…"]
[null,3,"Enter the appropriate formula as a condition"]
[null,4,"Specify the new format to be applied if the formula result is true"]
[null,"Examples:"]
[null,1,12345,{"$f":"\"<-- If \"&CELL(\"address\",B67)&\" is negative apply a red fill.\"","v":"<-- If $B$67 is negative apply a red fill."}]
[null,-1,12345,{"$f":"\"<-- If \"&CELL(\"address\",B68)&\" is negative apply a red fill.\"","v":"<-- If $B$68 is negative apply a red fill."}]
[null,"Be sure to use \"$\" correctly in conditional format rule formulas"]
[null,"if you wish to paste the cell format to other cells."]
["Data/What-If Analysis/Scenario Manager"]
[null,null,null,"Car"]
[null,null,"Loan Principal",33000]
[null,null,"Interest Rate (APR)",0.08]
[null,null,"Number of Monthly Payments",60]
[null,null,"Payment Amount",{"$f":"PMT(D74/12,D75,-D73)","v":669.12101151765148}]
["Protecting a Worksheet in a Workbook"]
[null,"By default all cells on a sheet are \"Locked\". You can see this by"]
[null,"selecting a cell range, right click, select \"Format Cells\" then the"]
[null,"Protection tab. Checking the locked box for a cell willhave no impact"]
[null,"until the worksheet is protected using the Review tab on the ribbon."]
[null,"Example:"]
[null,1,"The cells to the left are the only cell on this sheet which"]
[null,2,"were formatted with the \"Locked\" box unchecked using the"]
[null,3,"protection tab. To invoke the protection so that the only cells"]
[null,4,"able to be changed are the ones to the left, you must select"]
[null,5,"Review/Protect Sheet. Try it! Then undo the protection using"]
[null,6,"the Review tab."]
[null,"Remember, protecting the worksheet is a two step process. First, use"]
[null,"format protection to lock or unlock cells. Second, use the Review tab"]
[null,"on the ribbon to turn that protection on or off."]
[]
["Other Basic Excel Functions"]
[null,"Apples:",{"$f":"SUM(J95:J96,J99)","v":280},{"$f":"getformula(C94)","v":"<--  =SUM(J95:J96,J99)"},null,null,null,"Fruit","Year","Units"]
[null,"All but Pears",{"$f":"SUM(J95:J101)","v":1340},{"$f":"getformula(C95)","v":"<--  =SUM(J95:J101)"},null,null,null,"Apples",2018,50]
[null,"All but Pears",{"$f":"SUMIF(J95:J102,\"<400\")","v":1340},{"$f":"getformula(C96)","v":"<--  =SUMIF(J95:J102,\"<400\")"},null,null,null,"Apples",2019,100]
[null,"After 2019:",{"$f":"SUMIF(J95:J102,\"<>50\")","v":1690},{"$f":"getformula(C97)","v":"<--  =SUMIF(J95:J102,\"<>50\")"},null,null,null,"Oranges",2019,200]
[null,"Pears:",{"$f":"SUMIFS(J95:J102,H95:H102,\"PEARS\")","v":400},{"$f":"getformula(C98)","v":"<--  =SUMIFS(J95:J102,H95:H102,\"PEARS\")"},null,null,null,"Peaches",2019,300]
[null,"Apples 2018 & 2019",{"$f":"SUMIFS(J95:J102,H95:H102,\"Apples\",I95:I102,\"<2020\")","v":150},{"$f":"getformula(C99)","v":"<--  =SUMIFS(J95:J102,H95:H102,\"Apples\",I95:I102,\"<2020\")"},null,null,null,"Apples",2020,130]
[null,"Apples 2019:",{"$f":"SUMIFS(J95:J102,H95:H102,\"Apples\",I95:I102,2019)","v":100},{"$f":"getformula(C100)","v":"<--  =SUMIFS(J95:J102,H95:H102,\"Apples\",I95:I102,2019)"},null,null,null,"Oranges",2020,230]
[null,"Nonsence!:",{"$f":"SUMPRODUCT(I95:I102,J95:J102)","v":3514100},{"$f":"getformula(C101)","v":"<--  =SUMPRODUCT(I95:I102,J95:J102)"},null,null,null,"Peaches",2020,330]
[null,{"$f":"1/0","v":{"$error":"#DIV/0!"}},{"$f":"IFERROR(B102,\"Error!\")","v":"Error!"},{"$f":"getformula(C102)","v":"<--  =IFERROR(B102,\"Error!\")"},null,null,null,"Pears",2020,400]
[null,"Average all:",{"$f":"AVERAGE(J95:J102)","v":217.5},{"$f":"getformula(C103)","v":"<--  =AVERAGE(J95:J102)"}]
[null,"2019 Average:",{"$f":"AVERAGEIF(I95:I102,\"=2019\",J95:J102)","v":200},{"$f":"getformula(C104)","v":"<--  =AVERAGEIF(I95:I102,\"=2019\",J95:J102)"}]
[null,"Samallest",{"$f":"MIN(J95:J102)","v":50},{"$f":"getformula(C105)","v":"<--  =MIN(J95:J102)"}]
[null,"Largest",{"$f":"MAX(J95:J102)","v":400},{"$f":"getformula(C106)","v":"<--  =MAX(J95:J102)"}]
[null,"Number",{"$f":"COUNT(J95:J102)","v":8},{"$f":"getformula(C107)","v":"<--  =COUNT(J95:J102)"}]