XL 03 Int

Presented byJason WongEmail : jasonwcc_my@hotmail.comContact : +6012-273-6143Microsoft Excel 2003Intermediate Level

Section 1 : Working with Functions and Formulas1.1 Using Formulas1.2 Exploring Excel Functions1.3 Using Functions1.4 Working with Names and Ranges1.5 Working with Array FormulasMicrosoft Excel 2003 Intermediate Level

2

Using FormulasMicrosoft Excel 2003 Intermediate LevelBasic Mathematical Operators

OperatorFunctionExample=Equivalence=1+2*3()Parentheses(1+2)*3=9^Exponential2^3=8*Multiplication3*6=18/Division12/3=4+Addition10+2=12>Greater Than2>1=TRUE2=10-9>2=2+2*3 Toolbars > Tick on Formula Auditing

Evaluate formulaClick on cell containing formulaFormulas > Evaluate formula

Error CheckingSearch entire sheet for errorneous formulasFormulas > Error CheckingTrace Error

Show Formulas on sheetsOptions > Advanced > Display options > Show Formulas in cells instead of their calculated resultsOr Formulas > Show formula

Trace Dependents and PrecedentsDependents -> look for the formulaPrecedents -> look for the data

5

Fixing Formula ErrorsMicrosoft Excel 2003 Intermediate LevelError CodeMeaning#NAME?You entered something in your formula that Excel interprets as incorrect cell reference, range or function name#REF!A referenced cells has been deleted or accidentally relocated#VALUE!Used text in a formula#DIV/0!Division by zero or an empty cell#NUM!Incorrectly pass in an incorrect argument like text to function that is expecting numerical value#########Means that a number is too wide to be displayed in the cell. To solve this, auto fit the column width

The Formula Bar View > Formula Bar- F2 to edit

ErrorCode#NAME? =sum(a1:b3) chg to =sam(a1:b3)#REF! A1=4B1=2C1=A1+B1Delete entire column of B1#VALUE! =A1+B1+Data#DIV/0! =5/0#NUM! limited range to due the design of ExcelUse mod()A1=10, A2=5, A3=mod(a1,a2)Mod(number, dividsor)If the number=> dividsor*134,217,728 will get #num!Note: due to only 27 bits assign to the 134,217,728Cell Reference- Shows how to use =A1+B2/3

Absolute referencingPress F4

Evaluate formulaClick on cell containing formulaFormulas > Evaluate formula

Error CheckingSearch entire sheet for errorneous formulasFormulas > Error CheckingTrace Error

Show Formulas on sheetsOptions > Advanced > Display options > Show Formulas in cells instead of their calculated resultsOr Formulas > Show formula

Watch WindowActively monitor the changes on the specific formula across spreadsheetDemo :Sheet1 A1=20Sheet2 A1=40Sheet3 A1=Sheet1!A1+Sheet2!A1/2Formula > Watch Window > Add Watch > Sheet3!A1

Trace Dependents and PrecedentsDependents -> look for the formulaPrecedents -> look for the data

6

Excel FunctionsBasic FunctionsDate and Time FunctionsMicrosoft Excel 2003 Intermediate Level=sum()=average()=min()=max()=count()=sumif()=countif()=now()=day()=month()=year()=date()=hour()=minute()

Count() count range of numbersCountif() count both numbers and text7

Microsoft Excel 2003 Intermediate LevelGiven A1=24th May 2008Renew car license the following year, same day same monthFormula is=date(year(A1)+1,month(A1),day(A1))Given License date A1=30th August 2009Find out the renewal dates which falls on the following year but first day of the month=date(year(a1)+1,month(a1),1)Given current road tax on A1=30th August 2009Find 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 monthlater=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)-1DATE Function Scenario

Q&A 1. Given A1=24th May 2008Renew car license the following year, same day same monthAnswer : =date(year(A1)+1,month(A1),day(A1)2. Renew car license the following year but first day of the monthAnswer : =DATE(YEAR(A1)+1,MONTH(A1),1)3. Renew car road tax half a year later, but must be on the 10th day of the month Answer :=date(year(A1), month(A1)+6, 10)4. Return book at the end of the following monthAnswer := date(year(date), month(date) +2, 1)-1

8

Financial FunctionsMicrosoft Excel 2003 Intermediate LevelPMTFVPMT Function Arguments=pmt( rate , nper , pv ,fv , type)

To find Monthly Payment =PMT( )Rate : interest rateNper : Number of Payment Pv : Current | Principal | Present ValueFv : Future Value | Expected ROIType : 1 beginning or 0 end (default)Example : next slide

To find Return Of Investment | Future Value

9

PMT Example 1Microsoft Excel 2003 Intermediate Level

1st col => 6.4%/12 bcos over a year2nd col => 12 payments3rd col => pv present loan amount value 4th col => fv, amount left outstanding after full repayment default 05th col => type of payments, 0 due at end of payment period, 1 due at the beginning of payment period

10

PMT Example 2 : Saving AccountHow much a student need to save up every month in order to have saving amount of 50,000 at the end of 18 years. Assuming the annual saving interest rate fixed at 1.5%=PMT(1.5%/12,18*12,0,50000)Microsoft Excel 2003 Intermediate Level

1.5% / 12 due to the fact interest pay out on monthly basis and by increment onto the principal18* 12 - number of payments (18 years * 12 months(0 - no starting value11

PMT Example 3: EPF saving Microsoft Excel 2003 Intermediate LevelYou want to achieve a total saving of 1Million at your retire year (55 yrs old). How much do you need to bank in every month, assuming the following facts The annual saving interest rate fixed at 5.7%.You are currently 30 yrs old.=PMT(5.7%/12,(55-30)*12,0,1000000)

1.5% / 12 due to the fact interest pay out on monthly basis and by increment onto the principal18* 12 - number of payments (18 years * 12 months(0 - no starting value12

Financial FunctionsMicrosoft Excel 2003 Intermediate LevelPMT FVFV Function Arguments=fv( rate , nper , pmt , pv , type)

To find Return Of Investment | Future Value=FV()

rate : interest rate (if over number a year , must be divided by /12 )Nper : number of paymentPmt : what is the monthly installmentPv : present value | upfront sum | principalType : payment type : the end 0, the beginning 1, default to 0)

13

FV Example 1 : Fixed DepositCalculate the returns of an investment where you deposit $5,000 into a fixed deposit account that earns 3.9% annually. You are going to deposit $250 at the beginning of the month, each month, for 2 years.Interest earn on a monthly basis=FV(3.9%/12, 2*12, -250, -5000, 1)Microsoft Excel 2003 Intermediate Level

This first example returns the future value of an investment where you deposit $5,000 into a savings account that earns 3.9% annually. You are going to deposit $250 at the beginning of the month, each month, for 2 years.=FV(3.9%/12, 2*12, -250, -5000, 1)

5th col : payment due at the beginning of the month

14

FV Example 2 Mutual FundReturns the future value of an investment where you deposit $8,000 into a Mutual Fund account that earns 5% annually. You are going to deposit $50 at the end of the week, each week, for 4 years.Interest earn on a weekly basis=FV(5%/52, 4*52, -50, -8000, 0)Microsoft Excel 2003 Intermediate Level

This next example returns the future value of an investment where you deposit $8,000 into a savings account that earns 6% annually. You are going to deposit $50 at the end of the week, each week, for 4 years.=FV(6%/52, 4*52, -50, -8000, 0)

15

FV Example 3 Fixed DepositReturns the future value of an investment where you deposit $6,500 into a fixed deposit account that earns 1.95% annually. You are going to deposit $100 at the end of the year, each year, for 10 years.Interest earn on a yearly basis=FV(1.95%,10,-100,-6500,0)Microsoft Excel 2003 Intermediate Level

16

IF FunctionMicrosoft Excel 2003 Intermediate LevelBasic IFNested IFCombined conditions with AND | OR operator=if(200*2>300, Answer is Higher, Answer is Lower)A1=James=if(A1=James,Is The Boss, Is a Staff)=if(Condition, True, False)

Working with Array FormulasMicrosoft Excel 2003 Intermediate Level

Using IF Function in Array FormulasUsing Basic Array Formulas

ArrayProtection: Whenever there is a change the whole array need to change simultaneously, no single cell changes alone.

Basic Array FormulaDemo 1:Type in random numbers in A2 to C7 (3 columns)Now highlight D2 to F7 (must be same block size)Create a formula, say to increase all numbers by 3% =A2:C7*103/100 Make sure to Ctrl+Shft+Enter

Using IF in ArrayDemo 2:From the table in demo 1, highlight G2 to G7 =if(A2:A7=B2:B7,A2:A7,Not Same) Ctrl+shft+enterNext, sum up the similar numbers G8 = sum(if(A2:A7=B2:B7,A2:A7,Not Same))Ctrl+shft+enter

Demo 3: Using table 2 : Score BoardHighlight D1 to D4 =IF(D1:D4>F1:F4,C1:C4,E1:E4) , ctrl+shft+enter

18

Section 2: Managing ListsCreating ListsSorting a ListUsing AutoFilterUsing Advanced FilterUsing Excel As DatabaseMicrosoft Excel 2003 Intermediate Level

19

Creating ListsMicrosoft Excel 2003 Intermediate LevelFields and RowsTransposing ListsSorting Lists

Transposing Horizontal ListMicrosoft Excel 2003 Intermediate Level

Using AutoFilterMicrosoft Excel 2003 Intermediate Level

Data > Filter > AutoFilter The top 10Custom

22

Advanced FilterMicrosoft Excel 2003 Intermediate LevelCreate the Criteria RangeClick any part of the source tableData Ribbon > AdvancedSelect Copy to Another LocationList Range : automatically selectedCriteria Range : I2:O3Copy To : Extract Range => I9

The StepsCreate the Criteria RangeCopy A1:G1 to I2Type west under the regionClick in tableData Ribbon > AdvancedTick on Copy to another Criteria Range : I2:O3

Now try with another criteriaMultiple regionRange of sales >500 and ValidationApply validation to:Price Create From Selection, top and right column

Autocalculate allows you to view result of some basic calculation without having to enter a formula or functionRight click any part at the status bar. Tick those function you want to see, untick not to view them

26

Using Excel with other ProgramsUsing Excel in WordUsing Excel with AccessImport data from another programMicrosoft Excel 2003 Intermediate Level

Import data from another program such as txt file

27

Using Excel in WordMicrosoft Excel 2003 Intermediate Level

Copy and Paste from Excel to Word has limited options such as formatting

Linking from WordOpen Word

Insert Excel Chart into Word DocExcelSelect any table > F11From the chart sheetJust click copy buttonOpen Word > Paste

28

Using Excel with AccessMicrosoft Excel 2003 Intermediate Level

Click and drag

29

Import from another ProgramMicrosoft Excel 2003 Intermediate Level

Click and drag

30

Exercise : Creating Formulas and Function Microsoft Excel 2003 Intermediate LevelOpen excel intermediate exercise file, goto Function Exercise sheetMonth column should extract the numerical value of the month in Purchase DateYear column should extract the numerical 4 digits value of the year in Purchase DateConcatenate content from both Month and Year into Month, Year columnCreate name range for the Month, Year, Quantity, Price and Sales column using their respective column header nameFill up the yellow boxes with appropriate formula. The Expected Answer column is the correct formulas result.

Passwod : canlogin on the Function Exercise sheet

31

Jason WongEmail : jasonwcc_my@hotmail.comContact : +6012-273-6143Microsoft Excel 2003Intermediate Level The End

Sheet1ABC1SalesExpensesTax233345355556422232523253625255719182.5

Sheet1City1ScoresCity2ScoresWinnerLiverpool4ManU0Arsenal3Liverpool4ManCity2ManU1ManU4Arsenal9Arsenal2ManCity5