Quality Dynamics Consultancy Sdn Bhd presents
Microsoft 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• What’s in a Name?
• Creating Names Using the Define Name Dialog Box
• Creating Names Using the Name Box
• Rules for Naming Names
• Creating Names Automatically
• Naming Entire Rows and Columns
• Creating Multi-sheet Names
• Creating a List of Names
• Using Names in Formulas
• Applying Names to Existing Formulas
• Deleting Names
• Deleting Named Cells or Ranges
• Redefining Names
• Changing Names
• How Excel maintains Cell and Range Names
• Name Problems when Deleting Sheets
• The Secret to Understanding Names
• Naming Constants
• Naming Text Constant
MODULE 2: DATE & TIME CATEGORY FUNCTIONS
• TODAY
• NOW
• DATE
• DATEDIF
• DAY
• MONTH
• YEAR
• YEARFRAC
• EDATE
• EOMONTH
• WEEKDAY
• WEEKNUM
• NETWORKDAYSINTL
(ONLY IN EXCEL 2010/2013)
• WORKDAYINTL
MODULE 3: LOGICAL CATEGORY FUNCTIONS
• AND
• OR
• IF
• Nested IF
• IFERROR (Only in 2007/2010/2013)
MODULE 4: FINANCIAL FUNCTIONS
• SYD
• FV
• PMT
• RATE
MODULE 5: INFORMATION CATEGORY FUNCTIONS
• CELL
• IS Functions
• ISEVEN
• ISODD
• TYPE
MODULE 6: LOOKUP & REFERENCE CATEGORY FUNCTIONS
• CHOOSE
• LOOKUP
• VLOOKUP
• HLOOKUP
• OFFSET
• INDEX
• MATCH
• INDEX & MATCH
MODULE 7: TEXT CATEGORY FUNCTIONS
• CLEAN
• CONCATENATE
• EXACT
• LEFT
• MID
• RIGHT
• LEN
• FIND
• SEARCH
• REPLACE
• SUBSTITUTE
• LOWER
• PROPER
• UPPER
• TEXT
• TRIM
MODULE 8: MATCH CATEGORY FUNCTIONS
• ABS
• POWER
• SQRT
• EVEN
• ODD
• INT
• TRUNC
• MOD
• PRODUCT
• CEILING
• FLOOR
• ROUND
• ROUNDDOWN
• ROUNDUP
• SUBTOTAL
• SUMPRODUCT
• SUMIF
• SUMIFS (only in Excel 2007/2010/2013)
MODULE 9: STATISTICAL CATEGORY FUNCTIONS
• COUNT
• COUNTA
• COUNTIF
• COUNTIFS (Only in Excel 2007/2010/13)
• COUNTBLANK
• AVERAGEA (Only in Excel 2007/2010/13)
• AVERAGEIF (Only in Excel 2007/2010/13)
• AVERAGEIFS (Only in Excel 2007/2010/13)
• MODE
• RANK
• CORREL
methodology
Lecture, discussion, individual/group exercise, Question and Answerwho 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)
LAPTOP IS AVAILABLE FOR RENTAL UPON REQUEST
investment fee / early bird by 15/04/2020
Standard: RM750.00 Nett PerDay / Not Available per participant
Group discount: Not Available / Not Available per participant for a minimum of 2 participants from the same company
(Fee inclusive of Buffet Lunch, Refreshment, Training Bag, Handouts, Berbuka Puasa coupon will be given to all Muslim participants |
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:
QUALITY DYNAMICS CONSULTANCY SDN BHD
No. 343, Block A, Kelana Centre Point, No.3 Jalan SS7/19,
Kelana Jaya, 47301 Petaling Jaya,
Selangor, Malaysia.
Tel : 03-78054587 / 03-78044196
Fax: 03-78054514
E-mail:
training@qdc.com.my
(
Thang )
PSMB Reg: 620717-P
(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)