excel

KODE CD	JUDUL	JENIS	JML KEPING	HARGA SEWA	HARGA SEWA	TGL SEWA	JATUH TEMPO	TGL KEMBALI	DENDA	HARGA SEWA	1
F-01	LOVE IN PARIS	FILM	2	2000	2000	01/02/2011	02/02/2011	02/02/2011	0	4000	2
G-01	MORTAL KOMBAT	GAME	3	3000	3000	05/02/2012	10/02/2012	08/02/2012	0	9000	3
F-02	PERAHU KERTAS	FILM	2	2000	2000	24/01/2012	27/01/2012	29/01/2012	2	4000	4
I-01	WINDOWS 8	INSTALER	1	1500	1500	30/05/2012	02/06/2012	05/06/2012	3	1500	5
M-05	MP3-2012	MUSIK-LAGU	1	2500	2500	01/10/2012	02/01/2012	03/01/2012	1	2500	6
											7
											8
KETERANGAN:											9
F=FILM		2000									10
G=GAME		3000									11
I=INSTALLER		1500									12
M=MUSIK-LAGU		2500									13
											14
A	B	C	D	E	F	G	H	I	J	K	15
C2: IF(LEFT(A2;1)="F";"FILM";IF(LEFT(A2;1)="G";"GAME";IF(LEFT(A2;1)="I";"INSTALER";IF(LEFT(A2;1)="M";"MUSIK-LAGU"))))
E2: IF(LEFT(A2;1)="F";2000;IF(LEFT(A2;1)="G";3000;IF(LEFT(A2;1)="I";1500;IF(LEFT(A2;1)="M";2500))))
F2: IF(LEFT(A2;1)="F";2000;IF(LEFT(A2;1)="G";3000;IF(LEFT(A2;1)="I";1500;IF(LEFT(A2;1)="M";2500))))
J2: IF(I2>H2;I2-H2;0)
K2: IF(LEFT(A2;1)="F";2000;IF(LEFT(A2;1)="G";3000;IF(LEFT(A2;1)="I";1500;IF(LEFT(A2;1)="M";2500))))*D2

excel

NIM	NAMA	TAHUN MASUK	JURUSAN	NO URUT	NILAI	GRADE >	GRADE <	GRADE AND	1
94010786	ADI	94	informatika	0786	85	A	A	A	2
95110888	ITA	95	t.industri	0888	65	C	C	C	3
94107890	DODI	94	t.informatika	7890	45	D	D	D	4
97120001	AMANDA	97	t.elektro	0001	80	B	B	B	5
									6
A	B	C	D	E	F	G	H	I	7
C2 : LEFT(A2;2)
D2 : IF(MID(A2;3;2)="01";"informatika"; IF(MID(A2;3;2)="10";"t.informatika"; IF(MID(A2;3;2)="11";"t.industri"; "t.elektro")))
E2 : RIGHT(A2;4)
G2 : IF(F2 >= 85; "A"; IF(F2 >= 70; "B"; IF(F2 >= 60; "C";  IF(F2 >=  45; "D";  "E"))))
H2 : IF(F2 < 45; "E"; IF(F2 < 60; "D"; IF(F2 < 70; "C";  IF(F2 < 85; "B";  "A"))))
I2 :  IF(AND(F2>=0;F2<45);"E"; IF(AND(F2>=45;F2<60);"D"; IF(AND(F2>=60;F2<70);"C"; IF(AND(F2>=75;F2<85);"B"; "A"))))