Quality Dynamics Consultancy Sdn Bhd presents

Mar 21 - 22, 2017 (9am - 5pm) at Holiday Inn Kuala Lumpur Glenmarie

Excel Functions & Formulas

(by Mr. Chin Chee Chong )

objectives

Upon successful completion of this course, participants will be able to:

1.  Understand the Excel Tools to assist in building formulas
2.  Create basic formulas and functions
3.  Manipulate text with text functions
4.  Calculate dates and numbers in creative ways
5.  Add intelligence to your spreadsheets using logical functions
6.  Find data easier with Lookup functions
7.  Use advanced function techniques to save your time

program content

MODULE 1 :  WORKING WITH NAMES
1.    What’s in a Name?
2.    Creating Names Using the Define Name Dialog Box
3.    Creating Names Using the Name Box
4.    Rules for Naming Names
5.    Creating Names Automatically
6.    Naming Entire Rows and Columns
7.    Creating Multi-sheet Names
8.    Creating a List of Names
9.    Using Names in Formulas
10.  Applying Names to Existing Formulas
11.  Deleting Names
12.  Deleting Named Cells or Ranges
13.  Redefining Names
14.  Changing Names
15.  How Excel maintains Cell and Range Names
16.  Name Problems when Deleting Sheets
17.  The Secret to Understanding Names
18.  Naming Constants
19.  Naming Text Constant

MODULE 2 :  DATE & TIME CATEGORY FUNCTIONS
1.    TODAY
2.    NOW
3.    DATE
4.    DATEDIF
5.    DAY
6.    MONTH
7.    YEAR
8.    YEARFRAC
9.    EDATE
10.   EOMONTH
11.   WEEKDAY
12.   WEEKNUM
13.   NETWORKDAYSINTL (only in Excel 2010/2013)
14.   WORKDAYINTL (Only in Excel 2010/2013)

MODULE 3 :  LOGICAL CATEGORY FUNCTIONS
1.   AND   
2.   OR       
3.   IF
4.   Nested IF
5.   IFERROR (Only in 2007/2010/2013)       

MODULE 4 :  FINANCIAL FUNCTIONS
1.   SYD
2.   FV
3.   PMT
4.   RATE

MODULE 5 :  INFORMATION CATEGORY FUNCTIONS
1.   CELL       
2.   IS Functions
3.   ISEVEN
4.   ISODD
5.   TYPE

MODULE 6 : LOOKUP & REFERENCE CATEGORY FUNCTIONS
1.   CHOOSE
2.   LOOKUP
3.   VLOOKUP
4.   HLOOKUP   
5.   OFFSET
6.   INDEX
7.   MATCH
8.   INDEX & MATCH

MODULE 7  : TEXT CATEGORY FUNCTIONS
1.    CLEAN
2.    CONCATENATE
3.    EXACT   
4.    LEFT
5.    MID
6.    RIGHT
7.    LEN
8.    FIND   
9.    SEARCH
10.   REPLACE
11.   SUBSTITUTE
12.   LOWER
13.   PROPER
14.   UPPER
15.   REPT
16.   TEXT
17.   TRIM

MODULE 8 :  MATCH CATEGORY FUNCTIONS
1.    ABS   
2.    POWER
3.    SQRT
4.    EVEN
5.    ODD
6.    INT
7.    TRUNC
8.    MOD
9.    PRODUCT   
10.   CEILING
11.   FLOOR
12.   ROUND
13.   ROUNDDOWN
14.   ROUNDUP
15.   SUBTOTAL
16.   SUMPRODUCT
17.   SUMIF
18.   SUMIFS (Only in Excel 2007/2010/2013)

MODULE 9 :  STATISTICAL CATEGORY FUNCTIONS
1.    COUNT
2.    COUNTA
3.    COUNTIF
4.    COUNTIFS (Only in Excel 2007/2010/13
5.    COUNTBLANK
6.    AVERAGEA (Only in Excel 2007/2010/13)           
7.    AVERAGEIF (Only in Excel 2007/2010/13)
8.    AVERAGEIFS (Only in Excel 2007/2010/13)
9.    MODE
10.   RANK
11.   CORREL 

methodology

Lecture, discussion, individual/group exercise, Question and Answer

who must attend

This course is designed for participants who desire to build advanced formulas in their spreadsheets.  Participants shall have basic understanding of Excel formulas and functions.

(KINDLY TAKE NOTE:  PARTICIPANTS ARE REQUESTED TO BRING THEIR LAPTOP AND EQUIPPED WITH MICROSOFT EXCEL 2010 AND ABOVE VERSION)

investment fee / early bird by 07/03/2017

Standard: RM 1,495.00 Nett / RM1,395.00 Nett per participant

Group discount: RM1,395.00 Nett / RM 1,295.00 Nett per participant for a minimum of 2 participants from the same company

(Fee inclusive of Refreshment, Buffet Lunch, Training Bag, Handouts,  & Certificate of Completion)

registration & payment


PROGRAMS ARE CLAIMABLE UNDER SBL SCHEME

Please register online through www.qdc.com.my or call 03-78054587 / 03-78044196. Our phone line operation hours are from 8:30am to 6:00pm from Monday to Friday.

All registration MUST be accompanied with PAYMENT.

Completed registration form with CHEQUES should be made in favor of "Quality Dynamics Consultancy Sdn Bhd" and sent to:

(FEE PAID IS NOT REFUNDABLE but replacement may be made at no additional cost. Quality Dynamics Consultancy Sdn Bhd reserves the right to cancel or reschedule the above course and shall inform participants of the changes)

COURSE FACILITATOR - MR. CHIN CHEE CHONG

Mr. Chin Chee Chong has been in the Information Technology industry for the past 19 years starting with PC hardware where he is well-versed in troubleshooting and assembling of PCs. Thereafter, he ventured into IT software applications specializing in IT training and development for Microsoft Office users.

He is one of the certified training pioneers in Malaysia having attained his Microsoft Office Specialist Master Instructor (MOS) as well as a list of certifications stating that he has sound knowledge in Microsoft Word, Excel, PowerPoint, Access and Outlook. He also carries the title Microsoft Certified Trainer (MCT) by Microsoft USA. Having spent the last 9 years in this training field, his vast experience has equipped him with substantial knowledge to excel in the training field.

His extensive tutelage experiences are from conducting training courses for a number of corporate companies. Among his software training clients are American Insurance Group Berhad (AIG), Allianz General Insurance Malaysia Berhad, Citibank Berhad, Favelle Favco Berhad, EC-Council Academy, HSBC Bank Malaysia Berhad, Institute of Bankers Malaysia (IBBM), Jerneh Insurance Berhad, OCBC Bank Berhad, Proton Holding Berhad, Shell Information Technology International, Sime Darby Plantation Sdn Bhd, SP Setia Berhad, Wong Engineering Electronics Berhad and many more.

Besides the corporate sector, Mr Chin also trains the government sector such as Jabatan Pengangkutan Jalan (JPJ Academy Malacca), Keretapi Tanah Melayu Berhad (KTMB), Ministry of Health (MOH), Multimedia Development Corporation Sdn Bhd (MDec), Tentera Laut DiRaja Malaysia (TLDM) as well as local universities like UITM Shah Alam, University Tenaga Nasional (UNITEN) and Multimedia University (Cyberjaya & Melaka Campus).