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,SEARCH
new 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 |
|
|
|
