:xL-tips

Pindahan

2008-06-03T16:00:00.002+07:00

Assalaamu'alaykum

Blog ini sudah dipindahkan sama pemiliknya ke Wordpress di excelquotient.wordpress.com. Jadi, jangan lagi-lagi mengunjungi blog ini secara ga bakalan dapet apdetannya. Di blog sana lebih rame, after all.

Yu ah, sore ja!

Yearfrac

2007-07-19T19:14:00.000+07:00

Kategori: Formula
Level: Gampang

Yearfrac adalah formula di bidang financial untuk menghitung usia sesuatu (surat berharga, misalnya) dalam satuan tahun. Rumusnya seperti ini:

=yearfrac(tanggal awal, tanggal akhir , [basis hari])

Masukkan tanggal awal dan tanggal akhir pada formula ini (boleh juga kebalik: tanggal akhir dulu terus tanggal awal). Untuk basis hari, ini optional, namun secara default akan dipilih 0. Adapun penjelasan kode basis hari adalah seperti berikut:

0 untuk US NASD 30/360
1 untuk Actual/Actual
2 untuk Actual/365
3 untuk Actual/360
4 untuk European 30/360

Berikut penjelasan basis hari:
- Basis 0 menggunakan 30 hari sebulan dari total 360 hari dalam setahun.
- Untuk basis 1, banyak hari aktual dihitung sehingga terjadi perbedaan hari ketika memasuki tahun kabisat. Ini digunakan untuk perhitungan bunga berjalan (accrued interest) untuk SUN sejak FR021 sampai sekarang dan juga untuk ORI.
- Basis 2 sama dengan basis 1 tapi setahun selalu memiliki 365 hari.
- Basis 3 sama dengan basis 1 tapi setahun hanya memiliki 360 hari.
- Basis 4 adalah menggunakan metode gaya Eropah and hari terakhir yang terjadi pada tanggal 31 maka sama dengan 30 di bulan yang sama. Ini digunakan untuk perhitungan bunga berjalan (accrued interest) SUN recap (FR001-FR020), VRxx, dan obligasi korporasi.

Contohnya:
Ira membeli obligasi ORI03 pada tanggal 23-Nov-07 dan menjualnya pada 3-Mar-08. Berapa lamakah Ira memegang ORI03?

=YEARFRAC("23-Nov-7","3-Mar-8",1)
Ira memegang ORI03 selama 0,276 tahun

Original F-Xtudent writing.
Feel free to copy and distribute.

Bedah Kuesioner di Excel

2007-07-02T19:08:00.000+07:00

Kategori: Studi kasus
Level: GUI sedang lah....

Long time no see... Tak terasa sudah satu semester tidak posting di blog ini. Secara blogspot sudah diblok sama admin di kantor. Tapi masih bisa sih ngakses blogspot di jam-jam tertentu: sebelum jam 9 pagi, antara jam 12-13 siang, dan setelah jam 5 sore. Nah ini juga gw nulis pasca office hour, jam 7:11 waktu GMT+7 (WIB maksudnya).

Microsoft Excel memang sudah dewasa, terbukti apa yang kita inginkan sekarang sudah terfikirkan oleh para pegawai Microsoft yg bertanggung jawab thd Excel. Salah satunya adalah membuat kuesioner di atas garis (on-line, maksudnya) menggunakan Excel. Memang, kuesioner di Excel masih banyak kelemahannya, tapi lumayan lah segitu mah. Asalkan kuesionernya jangan yg susah-susah, seperti yang ada alurnya (jump ke sana-kemari) dan banyak logic check-nya.

Postingan kali ini akan membahas bagaimana memanfaatkan User Form dalam worksheet Excel, salah satunya adalah tombol pilihan (option button), dan drop-down list.

Option Button

Option button adalah control yang mengizinkan user hanya memilih satu pilihan dari berbagai pilihan yang disodorkan. Dalam dunia sampling, disebut pertanyaan dengan single answer. Sebagai contoh dalam pertanyaan screening:

S1. Apakah Anda atau salah satu anggota keluarga Anda bekerja di bidang marketing research?
Ya ..........1
Tidak ....2

Caranya:

1. Tuliskan pertanyaan "Apakah An..... research?" di B3
2. Munculkan Toolbar Forms dengan mengeklik kanan tetikus di menu atau toolbar manasaja, lalu klik Forms. Atau klik menu View > Toolbars > Forms. Maka akan muncul toolbar seperti ini:

3. Klik tombol Group Box lalu klik mouse di atas worksheet. Atur agar group box melingkupi pertanyaan tadi. Group Box akan mengelompokkan option button - option button yang memang merupakan satu kelompok. Sehingga ketika Anda memilih (checked) salah satu Option Button maka Option Button lainnya tidak akan terpilih (unchecked)

4. Ganti tulisan Group Box 1 dengan nama field misalnya S1 atau apalah terserah...

5. Klik tombol Option Button pada Toolbar Forms lalu klik mouse di dalam group box S1 tadi. Ganti tulisan Option Button 1 dengan Ya.

6. Lakukan langkah ke 5 sekali lagi dan ganti tulisan Option Button 2 dengan Tidak.

TIP: Untuk memilih dan menggeser-geser (edit mode: on) control Option Button, klik kanan terlebih dahulu pada Option Button tersebut, setelah terpilih Anda bisa menggeser (mengedit) dengan klik kiri. Sedangkan untuk menjalankan (mencoba) Option Button lakukan klik kiri.

7. Klik kanan pada salah satu Option Button, misal Option Button Ya, lalu pilih Format Control... Pada tab Control, Anda bisa mengeset value dari Option Button Ya apakah unchecked atau checked. Untuk kasus ini pilih unchecked saja. Lalu tentukan Cell link ke msialnya Sheet2!$B$18 di Sheet 2, sheet yg berbeda dengan pertanyaan S1 ini. (BTW jangan ikutin screenshot gambar di bawah ini untuk cell link, harusnya Cell link berisi Sheet2!$B$18 bukannya $B$7).

Cell link menyimpan nilai pilihan option button, jika terpilih Ya maka cell link akan berisi nilai 1 dan jika terpilih Tidak maka cell link akan berisi nilai 2. Kalau semuanya unchecked maka cell link bernilai 0. Cell link ini bisa (harus) Anda sembunyikan di sheet lain.

8. Di C7 tuliskan formula di bawah untuk menghentikan responden yang tidak sesuai dengan kategori Anda.
=IF(Sheet2!B18=2,"Maaf Anda bukan termasuk dalam katefori responden kami","")

9. Selesai dan hasilnya seperti ini.

Jelek ya, klik aja gambarnya biar lebih jelas

Drop-down List

Berikut adalah contoh penggunaan drop-down list dalam kuesioner dengan single answer.

P1. Manakah manakan yang paling Anda sukai? (S)

Pisang Goreng ............1
Comro ..........................2
Comhu .........................3

... (terus sampai) ...

Karoket .......................8

Ccaranya:

1. Tuliskan pertanyaan di cell B2

2. Ketik ke-delapan pilihan jawaban di H2:H9

3. Pilih cell B3 lalu klik menu Data > Validation

Pada tab Setting pilih List untuk Allow:.
Pada Source, pilih H2:H9

4. Selesai. Hasilnya seperti ini:

klik gambar ini biar gede

Berikut adalah contoh penggunaan drop-down list untuk multiple answers.

P2. Pilihlah tiga makanan yang paling Anda sukai? (M)

Pisang Goreng ............1
Comro ..........................2
Comhu .........................3

... (terus sampai) ...

Karoket .......................8

Caranya:

1. Kerjaan yang tadi di atas kita modif aja. Copy-kan cell B2 ke B3 dan B4. Sehingga pilihannya ada tiga. OK. Ya... ya...! Tapi sampai sini, kita masih punya masalah karena, jawaban yang sudah terpilih seharusnya tidak muncul di pilihan jawaban di tempat lain. Maka silakan ikuti langkah selanjutnya...

2. Copy-kan H2:H9 ke G2:G9

3. Di G2, masukkan formula berikut =IF(OR(G2=$B$3,G2=$B$4,G2=$B$5),"",G2)
Maksudnya adalah mengecek apakah Pisang Goreng (G2) sudah pilih di jawaban B3:B5? Kalau ada maka Pisang Goreng dikeluarkan dari list, kalau ga ada maka Pisang Goreng jangan dikeluarkan dari list atau dibiarkan ada.

4. Copy-kan G2 ke G3:G9

5. Hasilnya seperti ini. Silakan coba sendiri....

klik gambarnya biar lebih gede

Begitulah membuat kuesioner di excel, tapi masih ada yg harus dikerjakan, yakni si responden tak boleh bisa mengedit control. Misalnya control option button di pertanyaan S1 masih bisa diedit oleh responden. Makanya kita harus menguncinya dengan mengeklik Tool > Protection > Protect Sheet. OK. OK.

Selesai....

Original F-Xtudent writing.
Feel free to copy and distribute.

Download Tabel dari Internet Langsung

2006-11-09T17:32:00.000+07:00

Kategori: GUI
Level: Gampang

Ini adalah salah satu kerjaan gw di kantor. Mengupdate data dari internet. Tips ini sangat membantu, jika Anda harus mengupdate data yang dipublish di halaman website secara berkala. Namun ingat halaman website-nya harus tetap.

Seperti yang saya lakukan dalam contoh ini, adalah mengupdate data perdagangan obligasi dari situs BES. Secara on spot, data trading secara otomatis terupdate pada halaman tsb. Sayangnya BES tidak menyediakan data historisnya. Jadinya, gw mesti download dr situs BES tsb tiap hari, jangan samapai kelewat. Karena ga mau capek, jadinya gw bikin tip ini.

Tips di bawah ini merupakan cara yang mudah dan murah. Mudah karena kita tinggal klik sekali tombol saja buat update. Murah karena menghemat pita lebar (bandwidth) internet. Dengan cara manual, Anda terpaksa harus mendownload tulisan2, iklan2, banner2, atau gambar2 yang ga penting pada browser. Coba hitung berapa KB ukuran files yang ga penting itu. Coba bandingkan dengan cara yang akan dibahas ini.

Langkah2 Download tabel dari internet langsung di Sheet Excel:

1. Buka Excel dan tempatkan sel aktif sesuai keinginan, misal di A1

2. Klik menu Data > Import External Data > new Web Query... Maka akan muncul New Web Query, sejenis browser seperti ini.

3. Masukkan alamat website ke Address, misalanya di http://www.bes.co.id/market/TransAll.asp

4. Di dalam browser akan muncul halaman yang dituju. Di tiap ujung kanan atas tabel dlm halaman itu akan ada tombol kuning bergambar panah. Untuk memilih tabel yang ingin didownload, centang gambar tsb. Anda juga bisa memilih lebih dari satu table.

5. Setelah selesai, klik tombol Import. Maka Excel akan langsung mendownload data yang Anda inginkan secara otomatis.

6. Untuk meng-update data tsb di kemudian hari. Cukup klik tombol tanda seru merah pada panel External Data untuk mengupdate data dalam Sheet tsb. Atau klik tombol tanda seru dengan logo Excel untuk mengupdate keseluruhan data untuk seluruh Sheets dalam file Workbook Excel tsb.

Jika Anda ingin memutuskan hubungan dengan internet untuk menghentikan update (ditandai dengan tidak aktive-nya atau tidak munculnya panel External Data. Karena data yg hari itu ingin disimpan (bukan untuk file update). Lakukan klik kanan pada cell yg berisi data dr internet kemudian pilih Data Range Properties.... Akan muncul jendela seperti ini.

Uncheck pilihan Save query definition.

Mudah2an membantu kerjaan Anda.

Original F-Xtudent writing.
Feel free to copy and distribute.

Label Chart/Graphic: Contoh

2006-09-27T11:13:00.000+07:00

Kategori: Add-Ins
Level: Gampang pisan

Ini sebenernya gampang, soalnya bukan formula. Tinggal klik sini, klik sana, klik kanan, klik kiri, klik atas, klik bawah. Beres deh. Ya udah, gw bahas juga degh.

Tujuan kita pengen bikin chart yang seperti ini:

Chart tersebut dibikin dari data ini:

Caranya:
1. Bikin chart yang scatter plot; sumbu X-nya adalah range A2:A10, sumbu Y-nya adalah range C2:C10 dan nama serie-nya C1.

2. Klik menu Tools > XY Chart Labels > Add Chart Labels. Lalu pilih range B2:B10 dalam Select a Label Range. Hasilnya, sama dengan chart pertama di atas.

3. Untuk mengatur jarak label dengan titiknya, klik aja menu Tools > XY Chart Labels > Move Chart Labels. Tinggal klik aja tuh tombol panah atas bawah kiri kanan.

Oya gw punya tip untuk ngasih warna pada label. Kalo sel di kolom nama produknya sudah dikasih warna, misalnya merah untuk TH4174, maka di chart hasil, akan otomotis berwarna merah.

OK. That's it. Udah... cuman gitu kok. Ga percaya sih apa kata gw :D

Original F-Xtudent writing.
Feel free to copy and distribute.

Label Chart/Graphic

2006-09-04T10:10:00.000+07:00

Kategori: Add-Ins
Level: Gampang pisan

Masalah ini bukanlah masalah baru di dunia perworkbookan. Ada beberapa kekurangan dari aplikasi Ms Office Excel, walopun sebenernya solusinya udah ada di situs resmi mereka (Microsoft.com). Ya mau dikata apa, kita harus bikin sendiri atau mendownload add-ins untuk memberikan label pada titik2 dalam graph/chart Excel.

Alamatnya di:
Ms Windows: http://www.appspro.com/Downloads/XYChartLabeler.exe
Mac: http://www.appspro.com/Downloads/XYChartLabeler.zip
Linux: (belum tersedia, mungkin OOo Calc udah menyediakan or googling aja ndiri nya!)

Kalu di Windows, setelah di instal, bisa diakses dalam Excel di menu Tools > XY Chart Labels. Untuk menambah label klik Add Chart Labels.

Ga perlu dikasi pembahasan or conto kali ya... Try this at home/office!

Original F-Xtudent writing.
Feel free to copy and distribute.

Introduction to Solver

2006-09-01T13:47:00.000+07:00

Tingkat Kesulitan: Sulit
Kategori: GUI

Solver boleh dibilang hasil evolusi dari GoalSeek. Tidak seperti GoalSeek yang sangat simple, Solver membutuhkan input2 yg kompleks. Di dalam ranah statistika solver biasanya digunakan untuk memecahkan program linear yg ada dalam matakuliah Operation Research. Juga bisa digunakan untuk mendapatkan model dengan error yang kecil (eg. Yield Curve Modelling: ENSS) atau mencari titik maksimum dari sekumpulan data (eg. Efficient Frontier)

Solver bisa diakses di Tools > Solver. Adapun input2 bagi Solver adalah:

1. Fungsi Tujuan (Set Target Cell)
Dalam excel, fungsi tujuan berada dalam satu cell saja. Dimana di dalam cell ini terdapat formula excel dari cell lainnnya. Selain itu, kita harus menentukan tujuan kita itu apa. Apa mau mencari fungsi minimum (meminimumkan Target Cell), fungsi maksimum (memaksimumkan Target Cell), atau membuat fungsi sama dengan nilai tertentu (Value of)

2. Variables yg Diubah2 (By Changing Cells)
Masukkan variable (cell) yg diubah2 sehinga akan menghasilkan tujuan yg diinginkan.

3. Batasan2 (Subject to The Constraints)
Batasan2 dari variable2 yg berhubungan dengan model. Misalnya harus positif, atau sama dengan angka tertentu, dll.

Contoh kasus dalam GoalSeek bisa diselesaikan dengan Solver. Coba aja sendiri ya...

Original F-Xtudent writing.
Feel free to copy and distribute.

Excel yang Read-Only

2006-09-01T09:50:00.000+07:00

Tingkat Kesulitan: Gampang Bok!
Kategori: Trouble Shooting

Ketika sedang asyik2nya bekerja dengan excel, kadang2 excel mengalami error yang mengakibatkan aplikasi excel berhenti. Dan untuk memulainya lagi, harus membuka file excel tadi. Nah, biasanya suka read-only, dan ketika disimpan harus menggunakan filename yg baru.

Kejadian seperti ini biasanya terjadi pada Excel versi 2000 atau premuim. Kalo pernah mengalami hal ini dan belum tau solusinya. silakan ikuti tips berikut

1. Pastikan aplikasi excel tertutup.

2. Buka Task Manager dengan cara mengklik kanan Startbar > Task Manager, atau Ctrl+Alt+Del > Task Manager.

3. Pada tab Processes kolom Image Name, sorot EXCEL.EXE lalu tekan tombol End Process.

4. Tutup Task Manager dan coba buka lagi file excel tadi. Insya Allah ga bakal read-only lagi.

Semoga masalahnya terselesaikan.

Original F-Xtudent writing.
Feel free to copy and distribute.

Pivot Table

2006-08-01T12:26:00.001+07:00

Tingkat Kesulitan: Sedang
Kategori: GUI

Aplikasi Pivot Table menghasilkan keluaran dalam bentuk table atau chart. Table output ini merupakan ringkasan atau hanya perubahan tampilan table dari table sumber/asal. Dalam ranah ilmu statistik lebih dikenal dengan cross tab. Daripada banyak bachot, langsung aja kita liat contonya

Gw punya data sumber (source data) spt di bwah ini.

Dari data tsb, gw pengen mencari jumlah (sum) yang dikelompokkan berdasarkan Maturity Year-nya.

Caranya:
1. Klik menu Data > Pivot Table and Pivot Chart Report... maka akan muncul kotak dialog spt brikut.

2. Letak data yang mau dianalisis, pilih Microsoft Excel list or database, secara gw mau ngambil data dr Excel. Kalo gw mau ngambil data dr database e.g. Ms Access, maka pilih External data source. Jenis reportnya pan kita mau table bukan chart, ya pilih PivotTable. Next

3. Isikan range dg alamat table sumber kita yg tadi yaitu B2:E25. Next

4. Tempat nyimpen output table-nya terserah mau di worksheet baru atau di worksheet yg udah ada aja. Terus klik tombol Layout... Maka akan muncul kotak dialog berikut.

5. Caranya, kita drag (geser) tombol2 yang baris di sebelah kanan ke layout table. Dalam kasus ini, kita geser Maturity Year ke ROW, dan Outstanding ke DATA. Klik OK.

6. Finish deh.

Original F-Xtudent writing.
Feel free to copy and distribute.

Goal Seek

2006-06-07T19:21:00.000+07:00

Tingkat Kesulitan: Sedang
Kategori: GUI

Goal Seek adalah cara untuk mendapatkan solusi dengan cara merubah nilai dari variable/sel lain yang terkait perhitungan dengan nilai solusi tsb. Sebenernya Goal Seek itu bisa dibilang dipake untuk orang-orang pemalas aja. Kalaupun ngga, biasanya Goal Seek dipake untuk menyelesaikan persamaan yang sangat kompleks.

Pemalas

Gw lagi jalan-jalan ke pasar selasa. Disuruh mamih beliin kelapa terserah berapa kg, yg penting duit yg di rekening Bank Indonesia cukup. Gw inged-inged rekening di Bank Indonesia ada saldo Rp 3M tanggal 29 February 1945. Brarti kira-kira sekarang sisanya sekitar Rp 102 ribu lebih 75 perak. Gw liat di brosur, harga kelapa sekarang Rp 2.300,-/kg. Kalo gw ngambil Rp 100 rebu, berapa kg kelapa yg bsia gw dapet?

Karena kebetulan waktu itu gw bawa laptop, jadi gw buka aja Ms Excel. Dan ngetik2in apaan gitu jadi seperti ini:

di mana D2 ada formulanya:
=D3*D4

Terus ke menu Tools>Goal Seek gw isiin:

Hasilnya dg duit Rp 100 rebu, ternyata gw bisa bawa pulang 43.5 kg kelapa.

Susah-susah pake Goal Seek, padahal bisa kan kalo kg kelapa itu adalah uang di dompet dibagi harga kelapa/kg. Tapi karena gw males mikir lagi formulanya, jadi ya udah gw pake formula lama (uang di dompet = harga kelapa/kg x kg kelapa) terus pake Goal Seek deh.

Persamaan Kompleks

Gw punya persamaan kaya gini:
y = a + b * log ( c + 2 ^ d )

di mana nilai-nilai variable a, b, c, dan d, seperti di excel ini:

terus di C8 (nilai y) diisikan rumus:
=C4+C5*LOG(C6+2^C7)

Nah, kalo gw pengen dapetin y nya sebesar 350, berapakah nilai d, sementara nilai variable yg lain tetap.

Kalo gw bikin rumus di mana di sebelah kiri persamaan adalah d dan di kanannya selainnya, pusing gw.
d = #$%#$ y #$$# a #$ b Log* c &+_)!@##

Ya udah persamaannya ga usah diubah (di sebelah kiri persamaan adalah y) tapi gw pake Goal Seek aja.

Ternyata hasilnya nilai d adalah sebesar 24.91

Dayname (Non-English Version)

2006-05-22T14:30:00.000+07:00

Category: Multiple Formulas
Level: Intermediate

Unfortunately, Ms Excel just support English in date format compared with OpenOffice.org (OOo) Calc. So, you can’t perform the previous tips of Dayname (English Version) if you want to use another language. This weakness makes us more creative to create multiple formulas.

We will take the advantages of CHOOSE and WEEKDAY formulas.

CHOOSE(index_num, value1, value2, ...)
Index_num specifies which value argument is selected. Index_num must be a number between 1 and 29, or a formula or reference to a cell containing a number between 1 and 29.
If index_num is 1, CHOOSE returns value1; if it is 2, CHOOSE returns value2; and so on.

WEEKDAY(date, type)
type 1 or omitted return integer 1-7, where 1 for Sunday and 7 for Saturday
type 2 return integer 1-7, where 1 for Monday and 7 for Sunday
type 3 return integer 0-6, where 0 for Monday and 6 for Sunday

This example shows how to get dayname in Bahasa.

Using same case with previous posting, Dayname (English Version), use this formula in cell of C3:
=CHOOSE(WEEKDAY(B3),"Ahad","Senin","Selasa",
"Rabu","Kamis","Jum'at","Sabtu")

Dayname (English Version)

2006-05-18T15:17:00.000+07:00

Criteria: Technical, Multiple Formulas
Level: Intermediate

I myself often face this problem. That is, what is the dayname of certain date. Before found this multiple formulas from my partner job, Michael HTj, I always use macro. What a stupid way! Thx Mike! Now, I want to share how to get dayname of certain date.

There are two ways to have it:

.: Using format cell

1 Right click on selected range
2 Chose Format Cells...
3 In Number tab, chose Custom
4 Type ddd or dddd in Type textbox
5 To see the result, see in Sample box
6 OK

.: Using TEXT formula

TEXT(value, format_text)
Value is a numeric value, a formula that evaluates to a numeric value, or a reference to a cell containing a numeric value.
Format_text is a number format in text form from in the Category box on the Number tab in the Format Cells dialog box. Format_text cannot contain an asterisk (*) and cannot be the General number format.

For this case, set format_text to ddd for simple day (e.g. Sun) or dddd for complete day (e.g. Sunday)

Here are the formulas in cells of C3 and D3:
=TEXT(B3,"ddd")
=TEXT(B3,"dddd")
respectively.

SUMIF Formula

2006-05-17T10:38:00.000+07:00

Catogory: Bulit-in Formula
Level: Beginner

This is request formula from Miss Sri. How to use SUMIF formula?

SUMIF is a built-in formula in Excel. It means, that u don't have to make it by yourself. U just use it and u have to know how to use it. Actually, you can read it in Ms Excel's Help? And I think the Help is helpfull enough. But less application to the case.

SUMIF(range, criteria, sum_range)
Range is the range of cells you want evaluated.
Criteria is the criteria in the form of a number, expression, or text that defines which cells will be added. For example, criteria can be expressed as 32, "32", ">32", "apples".
Sum_range are the actual cells to sum. The cells in sum_range are summed only if their corresponding cells in range match the criteria. If sum_range is omitted, the cells in range are summed.

OK. I will use the data from the previous posting, AVERAGEIF Formula. The goal is to find the sum of each subject.

U just put these formulas in cell of D16, D17, and D18:
=SUMIF($C$3:$C$13,C16,$D$3:$D$13)
=SUMIF($C$3:$C$13,C17,$D$3:$D$13)
=SUMIF($C$3:$C$13,C18,$D$3:$D$13)
respectively.

There is other ...IF formula: COUNTIF

Generating Normal Distribution Data

2006-05-16T13:59:00.000+07:00

Category: Built-in Formula
Level: Intermediate

If u are a statistician or a researcher, this tip must be very important. Generating normal distribution data in Excel is very easy. Using formula NORMINV, u can get the data following normal distribution.

NORMINV(probability, mean, standard deviation)
Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation.
Probability is a probability corresponding to the normal distribution.
Mean is the arithmetic mean of the distribution.
Standard deviation is the standard deviation of the distribution.

For example, I want to generate 50 data following normal distribution with mean 100 and Standard Deviation 5.

Put the parameters in E5 for mean and E6 for standard deviation
Write this formula in range B8:F17
=NORMINV(RAND(),$E$5,$E$6)

And tada…. U can check the accuracy of mean and standard deviation using AVERAGE and STDEV formula. See cell of E21 and E22.

AVERAGEIF Formula

2006-05-16T11:56:00.000+07:00

Category: Multiple Formulas
Level: Intermediate

Excel has a formula to count sum with any condition, SUMIF. But there isn't built-in formula to count average with any condition, e.g. said AVERAGEIF. Then u yourself must write function with macro. But any other solution without macro?

The anwser is absolutly.... yes! *Horrayy*
U can use the advantage of array function, which is by pressing Ctrl+Shift+Enter after u write the formula. Then u can see that the formula shows in the bracket {..}.

The formula is
=AVERAGE(IF(array of condition = condition, array of average))
and followed by pressing Ctrl+Shift+Enter

For example. This table below shows the rate of subject by student. Our mission is find the average of each subject.

U just write the formula in D16, D17, and D18, respectively:
=AVERAGE(IF($C$3:$C$13=C16,$D$3:$D$13))
=AVERAGE(IF($C$3:$C$13=C17,$D$3:$D$13))
=AVERAGE(IF($C$3:$C$13=C18,$D$3:$D$13))
and don't forget to press Ctrl+Shift+Enter.

2D lookup

2006-05-16T11:21:00.000+07:00

Category: Multiple Formulas
Level: Intermediate

Are u ever use vlookup or hlookup formula?
These formulas just can search a cell with a criteria. Then what should we do if we have 2 criterias?

It's simple u can use this formula
=INDEX(table, MATCH(column criteria, first column of table, 0),MATCH(row criteria, first row of table, 0))

For example, I have a table. First row is subject name and first column is student name. I want to know what is Udin's rate in Basic Mathematic subject.

The formula in C11 is:
=INDEX(B2:E8,MATCH(B11,B2:B8,0),MATCH(C10,B2:E2,0))

Welcome

2006-05-16T11:13:00.000+07:00

Assalaamu'alaykum.

Begin from this day, I'll post about some tips in using Microsoft Excel.
I hope, this blog usefull for all Excel users.
If any questions then u can write it in shoutbox.

Best Regards.