Excel functions and formulas

Excel functions and formulas

• 1.
  By Jason Wong Email :jasonwcc_my@hotmail.com Contact : +6012-273-6143 Excel Functions and formulas Friday, August 21, 2015
• 2.
  1 - TheBasics Friday, August 21, 2015Excel Functions and Formulas  Formula Basics  Use Cell References  Copy Formula Without Changing Cell References  Transpose a Formula  Copy And Paste Values  Perform a Quick Calculation  Nesting Functions
• 3.
  2 - statisticaland logical functions Friday, August 21, 2015Excel Functions and Formulas  Use COUNTIF To Calculate Survey Results  Use SUMIF To Add Data That Satisfy Condition  Use AVERAGEA To Include All Cases  Use IF Function To Prevent Division By Zero  Use ISERROR Function To Avoid Error Display  Create Multiple Conditions Using Nested IF
• 4.
  3 – lookup& reference formulas Friday, August 21, 2015Excel Functions and Formulas  Use VLOOKUP To Find Specific Data  Use HLOOKUP To Find Values In Rows  Use INDEX & MATCH To Retrieve Data
• 5.
  4 – textformulas Friday, August 21, 2015Excel Functions and Formulas  Change Case Of Text  Combine Text And Formatted Numbers  Convert Imported Text Format Into Numbers  Break Imported Date Field Into Individual Columns  Extract A Name Field Into Three Columns
• 6.
  5 – date& time formulas Friday, August 21, 2015Excel Functions and Formulas  Add a Number To Current Date To Get New Dates  Calculate A Period Of Time Between Two Dates  Perform Calculation With Time Fields
• 7.
  7 – efficiencytips Friday, August 21, 2015Excel Functions and Formulas  Reduce Formula Recalculation Time  Enter Formulas Quickly By Shortening Sheet Names  Select & Protect Cells Containing Formulas  Reduce Data Entry Errors By Using Data Validation  Display & Print Formula Syntax  Use Auditing Tools To Check For Errors  Check Data By Using Trace Precedents  Use Comments To Annotate Your Worksheet
• 8.
  6 – array& database functions Friday, August 21, 2015Excel Functions and Formulas  Use Array Formulas  Calculate The Difference Between Maximum And Minimum Values In An Array  Find Value In List  Use FREQUENCY Function To Count Responses  Analyze Data In A Database
• 9.
  Exploring Excel Functions AggregationFunctions Date/Time Functions Friday, August 21, 2015Excel Functions and Formulas  =sum (range_of_numbers)  =average (range_of_numbers)  =max (range_of_numbers)  =min (range_of_numbers)  =count (range_of_data)  =counta (range_of_data)  Ctrl+;  =now()  =day(date)  =month (date)  =year (date)  =date(day,month,year)  =hour()  =minute()
• 10.
  DATE Function Scenario Friday,August 21, 2015Excel Functions and Formulas  Given A1=24th May 2008  Renew car license the following year, same day same month Formula is… =date(year(A1)+1,month(A1),day(A1 )) •Given License date A1=30th August 2009 •Find out the renewal dates which falls on the following year but first day of the month =date(year(a1)+1,month(a 1),1) •Given current road tax on A1=30th August 2009 •Find out the renewal date which falls half a year later, but must be on the 10th of the month =date(year(date), month(date)+6, 10) • Student A borrowed a book on the 15th Feb 2010 • The returning due date of the book is 1 month later =date(year(date), month(date)+1,day(date)) • Student A borrowed a book on the 15th Feb 2010 • Return book at the end of the following month =date(year(date), month(date) +2, 1)-1
• 11.
  Advanced Functions Friday, August21, 2015Excel Functions and Formulas  Nested Function  Using IF function  =SUMIF( range, criteria )  =AVERAGEIF( range,criteria )  =COUNTIF( range,criteria ) =if(Condition, True, False) =if(200*2>300, “Answer is Higher”, “Answer is Lower”) A1=“James” =if(A1=“James”,”Is The Boss”, “Is a Staff”) A1=500, A2=300, A3=800 A4=sumif(A1:A3,”>=500”) =sum(max(range_a),min(range_b),max(range_c))
• 12.
  Working with Namesand Ranges Friday, August 21, 2015Excel Functions and Formulas  What are Range Names?  Defining and Using Range Names  Create Name Range from Selection  Auto Calculate
• 13.
  Working with ArrayFormulas Friday, August 21, 2015Excel Functions and Formulas A B C 1 Sales Expenses Tax 2 33 34 5 3 55 55 6 4 22 23 2 5 23 25 3 6 25 25 5 7 19 18 2.5 Using IF Function in Array Formulas Using Basic Array Formulas Arsenal 3 Liverpool 4 ManCity 2 ManU 1 ManU 4 Arsenal 9 Arsenal 2 ManCity 5
• 14.
  Exercise Friday, August 21,2015Excel Functions and Formulas
• 15.
  Presented by Jason Wong Email: jasonwcc_my@hotmail.com Contact : +6012-273-6143 Friday, August 21, 2015 Excel functions and formulas the end ~ thank you!