Management Productivity Tools

MICROSOFT EXCEL 2010 FORMULA AND

FUNCTIONS

9/1/2014 Management Productivity Tools 1

9/1/2014 Management Productivity Tools 2

hyperlink

clipart

chart

Images and Background

Functions

Page layout

Database:

WORK SHEET- EXCEL

THIS IS EXCEL

WORK SHEET

CELLS IN WORK

SHEET

9/1/2014 Management Productivity Tools 3

AVERAGE

• AVERAGE function returns the average.

The syntax for the Microsoft Excel AVERAGE

function is

where number1, number2, ... number_n are numeric values -

they can be numbers

AVERAGE function is:

AVERAGE( number1, [number2], ... [number_n] )

9/1/2014 Management Productivity Tools 4

CONCATENATE

• CONCATENATE function allows you to

join 2 or more strings together

CONCATENATE function is:

CONCATENATE( text1, text2, ... text_n )

=CONCATENATE(B8," ",A8)9/1/2014 Management Productivity Tools 5

SUM

• SUM function adds all numbers in a range of cells and returns the result.

SUM function is:

SUM( number1, [number2, ... number_n] )

=SUM(C4:C12)

9/1/2014 Management Productivity Tools 6

SUMIF

• SUMIF function adds all numbers in a range of cells, based on a given criteria.

Where,

range is the range of cells that you want to apply the criteria against.

criteria is used to determine which cells to add.

sum_range is optional

SUMIF function is:

SUMIF( range, criteria, [sum_range] )

9/1/2014 Management Productivity Tools 7

IF

• IF function returns one value if a specified condition evaluates to TRUE, or another value if it evaluates to FALSE.

value_if_true is optional. It is the value that is returned if condition evaluates to TRUE.

value_if_false is optional. It is the value that is return if condition evaluates to FALSE.

IF function is:

IF( condition, [value_if_true], [value_if_false] )

9/1/2014 Management Productivity Tools 8

RANK

• Returns the rank of a number in a list of numbers. The rank of a number is its size relative to other values in a list.

• Number Required. The number whose rank you want to find.

• Ref Required. An array of, or a reference to, a list of numbers. Nonnumeric values in ref are ignored.

• Order Optional. A number specifying how to rank number.

RANK(number,ref,[order])

9/1/2014 Management Productivity Tools 9

ABSOLUTE AND RELATIVE CELL

REFERENCE

• By default, when you copy a formula that contains a cell reference, excel will automatically adjust the cell reference.

• You can stop Excel from automatically adjusting the cell reference by using one or more dollar signs ($) in the cell reference. These are called absolute cell references.

• A cell reference without a dollar sign is a relative cell reference

9/1/2014 Management Productivity Tools 10

MAX

• MAX function returns the largest value from the numbers provided.

• number1, number2, ... number_n are numeric values - they can be numbers, named ranges, arrays, or references to numbers.

MAX function is:

MAX( number1, [number2, ... number_n] )

9/1/2014 Management Productivity Tools 11

MIN

• MIN function returns the smallest value from the numbers provided

• number1, number2, ... number_n are numeric values - they can be numbers, named ranges, arrays, or references to numbers

MIN function is:MIN( number1, number2, ... number_n )

9/1/2014 Management Productivity Tools 12

DATE

• DATE function returns the serial number of a date

DATE function is:

DATE( year, month, day )

9/1/2014 Management Productivity Tools 13

TIME

• TIME function returns the decimal number for a particular time

TIME function is:

TIME( hour, minute, second )

9/1/2014 Management Productivity Tools 14

TODAY

• TODAY function returns the current system date. This function will refresh the date whenever the worksheet recalculates.

TODAY function is:

TODAY()

9/1/2014 Management Productivity Tools 15

NETWORK DAYS

• NETWORKDAYS function returns the number of "work days" between 2 dates, excluding weekends and holidays.

NETWORKDAYS function is:

NETWORKDAYS( start_date, end_date, [holidays] )

9/1/2014 Management Productivity Tools 16

LOOKUP

• LOOKUP function returns a value from a range (one row or one column) or from an array.

LOOKUP function is:

LOOKUP( value, lookup_range, [result_range]

9/1/2014 Management Productivity Tools 17

VLOOKUP

• VLOOKUP function performs a vertical lookup by searching for a value in the left-most column of table_array and returning the value in the same row in the index_numberposition.

VLOOKUP function is:VLOOKUP( value, table_array, index_number, [not_exact_match]

9/1/2014 Management Productivity Tools 18

COUNT

• COUNT function counts the number of cells that contain numbers as well as the number of arguments that contain numbers.

COUNT function is:

COUNT( argument1, [argument2,

... argument_n] )

9/1/2014 Management Productivity Tools 19

COUNTIF

• COUNTIF function counts the number of cells in a range, that meets a given criteria.

• range is the range of cells that you want to count based on the criteria.

• criteria is used to determine which cells to count.

COUNTIF function is:

COUNTIF( range, criteria )

9/1/2014 Management Productivity Tools 20

MICROSOFT WORD

2010

9/1/2014 Management Productivity Tools 21

9/1/2014 Management Productivity Tools 22

Create visually compelling documents more easily than ever

Save time and simplify your work

Work together more successfully

Access your information from more places

easily customize the improved Ribbon

MICROSOFT WORD 2010

• It's been a long time since the last Microsoft Office revision, and it can be hard to imagine what new capabilities Microsoft could possibly add to the 20-year-old suite. But Office 2010 does in fact have a lot of new capabilities to offer.

9/1/2014 Management Productivity Tools 23

FEATURES

• Universal ribbon : The ribbon interface is now a part of every single Office application. First released in Office 2007 in some of its applications, the ribbon interface was an artistic leap, and as we all know, only moments after the ribbon was born, its first "art critic" came along.

9/1/2014 Management Productivity Tools 24

• Backstage view: By clicking the new File button (which replaces the Office 2007 orb), you have access to all your normal Save, Save As, Open, and Close operations. In addition, you can see and customize the document properties, manage versions, check for compatibility issues, and scrub the document of hidden metadata for sharing purposes

9/1/2014 Management Productivity Tools 25

• Paste preview: Included in all suites is a new live preview paste feature that lets you hover your mouse over an option and see what your clipboard item will look like. From the Home ribbon, just click the Paste drop-down menu and hover over your options.

9/1/2014 Management Productivity Tools 26

• Office Web Apps: Office 2010 now includes a set of Web-based applications -- online versions of Word, Excel, PowerPoint, and OneNote -- that work through Windows Live and/or with SharePoint. (Check out the InfoWorld Test Center's review of Office Web Apps.) Outlook Web App (a new name but not a new feature) continues to be offered through Exchange 2010.

9/1/2014 Management Productivity Tools 27

• More themes: Themes can now be used across Word, Excel, and PowerPoint to ensure a consistent look for your various documents. Microsoft has also upped the number of built-in themes from 20 in Office 2007 to 40 in Office 2010

9/1/2014 Management Productivity Tools 28

• Insert a screenshot: Now you don't have to take a screenshot using a tool such as the Windows Snipping Tool and then paste it into your document. Instead, you can use the Insert ribbon and click Screenshot to grab an image you want to insert

9/1/2014 Management Productivity Tools 29

• Crop images to a shape: You can now select an image, go to the Format contextual ribbon, and under the Crop option, choose Crop to Shape.

• New photo-editing options: Word 2010 has a variety of new photo-editing tools that you can find by inserting a picture and selecting it, then going to the Format contextual ribbon and looking at its options, which include Remove Background and Artistic Effects.

9/1/2014 Management Productivity Tools 30

• Cleanup: Sometimes your mailbox is cluttered with redundant messages. Use the new Clean Up option (located on the Home ribbon under the Delete group) to tidy the folder or conversation.

• Quick steps: Sometimes you have an entire ritual when it comes to handling emails. Quick steps are like little macros that allow you to simplify the tasks. You can use the built-in steps located on the Home ribbon, or you can create your own. Configure these to make your common "rituals" that much easier to perform.

9/1/2014 Management Productivity Tools 31

9/1/2014 Management Productivity Tools 32