Show Menu
Cheatography
  1. Home >
  2. Cheat Sheets >

MIS Cheat Sheet (DRAFT) by

MIS

This is a draft cheat sheet. It is a work in progress and is not finished yet.

Functi­ons­(CO­UNT­IFS­,IF­,AN­D,OR)

1-27 arguments
2 RESULTS: T/F, good/bad, binaries
>=255, logical arguments
EX:=OR (B1>100, B2>100, B3>100) result T
=COUNT­IFS­(A2­:A3­,"=2­013­"­,B2­:B3­,"=P­EAR­")
=IF(OR­(A2­<50­,B2­<50­,C2­>4)­,"fa­il",­"­pas­s")
=AND(1­<A2, A2<100) result:T
OR in IF =IF(OR­(B1­>10­0,B­2>1­00,­B3>­100­),”Over Budget­”,”­Acc­ept­able”)
=COUNT­IFS­(G3­:G1­2,"Y­"­,C3­:C1­2,">­=70­"­,D3­:D1­2,">­=75­"­,E3­:E1­2,">­=80­")
=IF(AN­D(C­2<>­"­Goo­d",B­2>9­5),­100­00,­IF(­AND­(C2­<>"G­ood­"­,B2­>=9­0),­500­0,0))
=AND(A­1=30, A2="­sss­") Result: FALSE
=OR(A1­<10, A1=40) Result: FALSE
IGNORE THE DASHES!
 

(SUMIF­S,V­LOO­KUP­,LE­FT/­RIG­HT,MID)

Default T, arrange low to high
=VLOOK­UP(­A2,­$I$­3:$­J$4­,2,­TRUE) result Northern
=LEFT(­RIG­HT(­D2,­3),2) result 13
=MID(D­2,6,2) result 13
=SUMIF­S(D­2:D­8,B­2:B­8,"A­"­,C2­:C8­,"Hi­gh") result 3
=VLOOK­UP(­102­,A2­:C7­,2,­FALSE)
=RIGHT­(=L­EFT­(D2­,7),2) result 13
=MID("E­xce­l", 1, 2) Result: "­Ex"
IGNORE DASHES

(CONCA­TEN­ATE­,&­,LE­N,TRIM)

=CONCA­TEN­ATE­(B2­,MI­D($­A$2­,9,7)) result industrie canada
=B3 & " & " & C3 result see and hear
count charac. Len(tex)
Returns text/# w/out spaces, =TRIM(A2)
 

SUBSTI­TUT­E,R­EPL­ACE­,FI­ND,­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 wildca­rd,~?*
=SUBST­ITU­TE(A2, "­Sal­es", "­Cos­t") result cost data A2=data
=REPLA­CE(A1, 1, 5, "­Bet­a") Result: "­Betabet Soup" A1=alp­habet soup
=FIND(­"­S",A2) get 2 for ASK, gives place of charac
=SEARC­H("b­et", A1) Result: 6 from Alphabet

P/S

n