DAY 10:MICROSOFT EXCEL – 7,9 AND 12

Sravanthi Lakkimsettysalakkimsetty@mix.wvu.eduSept 21, 2015

REMINDER

• Homework # 3 is due on 9/25/2015 by 11.59pm.

NESTED IF FUNCTIONS

• Scenario:• If a representative was hired before 1/1/2015, the rep

receives 9% of his/her salary as bonus. If a representative was hired between 1/1/2005 and 1/1/2010, the rep earns 5% bonus. Anyone hired after 1/1/2010 receives a 3% bonus.

• How do we write a single formula to calculate the bonus?

DATA

NESTED IF FLOW CHART

Salary*9%

Salary*5%

Salary*3%

If hired before

1/1/2005

If hired on or before 1/1/2010

Value_if_false

Value_if_true

Value_if_true

Value_if_false

NESTED IF FORMULA

• Now try building the nested IF condition in the formula as:

AND FUNCTION

• AND function accepts two or more logical tests and displays TRUE if all conditions are true and FALSE if any of the conditions is false.

• Syntax is =AND(logical1,logical2)

• Lets try the nested AND function in the example where students earn a bonus of 10 points if they have full attendance and have not missed any assignments

OR FUNCTION

• OR function accepts two or more conditions and returns TRUE if any of the conditions are true and returns FALSE if all the conditions are false.

• Syntax is =OR(logical1,logical2)

• Lets try the nested OR function in the example where students earn a bonus of 10 points if they have either full attendance or have not missed any assignments

NOT FUNCTION

• The NOT function reverses the truth value of its argument.

• Syntax is =NOT(logical)

10

CHAPTER - 9

11

WORKSHEET REFERENCE

• Pointer to a cell in another worksheet is called a 3-D Reference.

• Syntax : ‘Worksheet_name’!Range_Of_Cells

12

3D FORMULA

• Formula or function that refers to the same range in multiple worksheets.

• Select cell B2 in Yearly Totals sheet• Type =SUM(• Click first worksheet tab, Qtr1.• Press and hold shift as you click the last worksheet tab

for adjacent sheets.• The formula becomes =SUM(Qtr1:Qtr4!B2)

13

INSERTING HYPERLINKS

• Hyperlink is an electronic marker to another location in a worksheet, workbook, file, web page or email.

• Select B1 Insert ribbon Hyperlink in the Links group• Type J4:J20 in the Type the cell reference box and click

OK.

LINKING WORKBOOKS

• Linking is the process of creating external cell references from worksheets in one workbook to cells on a worksheet in another workbook

• Before creating links, identify the source and destination files.

• Source file is the one that contains the original data that you need elsewhere.

• The destination file is the file containing a pointer to receive data from the source file.

CREATE AN EXTERNAL REFERENCE

• Syntax is =[WorkbookName]WorksheetName!RangeOfCells

• (But this works only when source and destination are in the same folder and workbook,worksheet names do not contain spaces)

• = ‘[WorkbookName]WorksheetName’!RangeOfCells• (But this works only when source and destination are in

the same folder and workbook,worksheet names contain spaces)

• =‘C:\.[path to the workbook]WorksheetName’!RangeOfCells

• (Source and destination are in different folders)

17

CHAPTER - 12

18

SELECTING A TEMPLATE

• Template is a special workbook file which is partially completed and is used as a model to create similarly structured workbooks

• File -> New• Search for template• Select one• Preview of selected template

19

APPLYING THEMES AND BACKGROUNDS

• A theme is a collection of formats that include colors, fonts, and special effects to provide a stylish appearance.

• Page Layout ribbon • Themes • Select one• A background is an image that appears behind the

worksheet data onscreen• Page Layout ribbon• Background -> select desired file

20

APPLYING CELL STYLES

• A cell style is a set of formatting options applied to worksheet cells

• Home ribbon• Styles -> Cell styles

Thank You!