Functions(COUNTIFS,IF,AND,OR)1-27 arguments | 2 RESULTS: T/F, good/bad, binaries | >=255, logical arguments | EX:=OR (B1>100, B2>100, B3>100) result T | =COUNTIFS(A2:A3,"=2013",B2:B3,"=PEAR") | =IF(OR(A2<50,B2<50,C2>4),"fail","pass") | =AND(1<A2, A2<100) result:T | OR in IF =IF(OR(B1>100,B2>100,B3>100),”Over Budget”,”Acceptable”) | =COUNTIFS(G3:G12,"Y",C3:C12,">=70",D3:D12,">=75",E3:E12,">=80") | =IF(AND(C2<>"Good",B2>95),10000,IF(AND(C2<>"Good",B2>=90),5000,0)) | =AND(A1=30, A2="sss") Result: FALSE | =OR(A1<10, A1=40) Result: FALSE |
| | (SUMIFS,VLOOKUP,LEFT/RIGHT,MID)Default T, arrange low to high | =VLOOKUP(A2,$I$3:$J$4,2,TRUE) result Northern | =LEFT(RIGHT(D2,3),2) result 13 | =MID(D2,6,2) result 13 | =SUMIFS(D2:D8,B2:B8,"A",C2:C8,"High") result 3 | =VLOOKUP(102,A2:C7,2,FALSE) | =RIGHT(=LEFT(D2,7),2) result 13 | =MID("Excel", 1, 2) Result: "Ex" |
(CONCATENATE,&,LEN,TRIM)=CONCATENATE(B2,MID($A$2,9,7)) result industrie canada | =B3 & " & " & C3 result see and hear | count charac. Len(tex) | Returns text/# w/out spaces, =TRIM(A2) |
| | SUBSTITUTE,REPLACE,FIND,SEARCHnew text for old,case senstive | Case sens, old text from specific location based on specific characters | Case sens, no wildcard, looks for string from 1 text to other | Allows wildcard,~?* | =SUBSTITUTE(A2, "Sales", "Cost") result cost data A2=data | =REPLACE(A1, 1, 5, "Beta") Result: "Betabet Soup" A1=alphabet soup | =FIND("S",A2) get 2 for ASK, gives place of charac | =SEARCH("bet", A1) Result: 6 from Alphabet |
| | |