Gazi Asha - Home



Microsoft Excel

Fantastic Formulas

Student guide book

Contents

Formulas 3

Commonly Used Formula 3

Sum 3

Average 3

Min 3

MAX 4

COUNT 4

COUNTIF 4

COUNTA 4

PROPER, UPPER AND LOWER 5

TRIM 5

CONCATENATE 6

LEFT 6

RIGHT 6

TODAY 7

NOW 7

VLOOKUP 7

HLOOKUP 8

IF 8

Evaluate A Nested Formula 10

Formulas

Formulas are equations that perform calculations on values in your worksheet. A formula starts with an equal sign =.

Excel has also a number of pre-written formulas that carry out common and more complicated calculations. They simplify the process of entering formulas and are known as functions.

Commonly Used Formula

Sum

Adds all neighbouring numbers in a row or column.

In this example, to calculate the total in cell B9 use:

=SUM(b4:b8)

Average

Calculates the average (mean) figure.

In this example, to calculate the average price of all the tuck shop items, use:

=AVERAGE(b4:b8)

Min

Identifies the minimum (lowest) number in a range of data.

In this example, to identify the lowest price, use:

=MIN(b4:b8)

MAX

Identifies the maximum (highest) figure in a range of data.

In this example, to identify the most expensive item use:

=MAX(b4:b8)

COUNT

Counts the number of cells that contain numbers.

In this example, to count the number of items in the tuck shop use:

=COUNT(b4:b8)

COUNTIF

Counts the number of cells within a range that meet the given criteria.

In this example, to count the items that cost £0.30 use:

=COUNTIF(B4:B8,"=0.30")

COUNTA

Counts the number of cells that contain data (not empty) in a range or array.

In this example, to count the cells containing data use:

=COUNTA(B4:B8)

Text Formulas

Text formulas can be a great time saver, particularly if you have copied information from another application, or imported information that is displayed incorrectly.

PROPER, UPPER AND LOWER

Capitalises the first letter in a text string and any other letters in text that follow any character other than a letter. It converts all other letters to lowercase letters.

|To change… |Use… |

|amy to Amy |=PROPER(A4) |

|amy Franks to all upper case |=UPPER(A4) |

|Amy Franks to all lower case |=LOWER(A4) |

Replicate these and you will change all the values to the required case.

TRIM

Removes all spaces from text except for single spaces between words. You can use TRIM on text that you have received from another application that may have irregular spacing.

To remove the space before amy Franks use:

=TRIM(A4)

To remove the space and change the case at the same time use:

=PROPER(TRIM(A4))

You can replicate these formulas to amend the other cells, removing spaces and altering the case as required.

CONCATENATE

Joins several text strings into one text string. For example if you have the first name in one cell and the surname in an adjoining cell, this function will join the two together:

To join Amy and Franks together, use:

=CONCATENATE(A4," ",B4)

The inverted commas denote a space.

LEFT

Separates characters on the left.

In this example, to separate the ID number from the name use

=LEFT(A4,5)

This tells Excel to start on the left and take the first five characters.

RIGHT

Separates characters on the right.

In this example, to separate the Class from the name use:

=RIGHT(A4,2)

TODAY

Returns the serial number of the current date. The serial number is the date-time code used by Microsoft Excel for date and time calculations. (If the cell format was General before the function was entered, the result is formatted as a date.)

To input the date in cell B3 use:

=TODAY()

NOW

Returns the serial number of the current date and time. (If the cell format was General before the function was entered, the result is formatted as a date.)

To input the date in cell B3 use:

=NOW()

VLOOKUP

Searches for a value from your spreadsheet in a separate table of data (where data runs vertically in columns) and returns a value in from that separate table.

To add the supplier (currently held in a separate table of data) use:

=VLOOKUP(A6,$A$17:$B$21,2,FALSE)

HLOOKUP

Searches for a value from your spreadsheet in a separate table of data (where data runs horizontally in rows) and returns a value in from that separate table.

To add the Fat Content (currently held in a separate horizontal table of data) use:

=HLOOKUP(A6,$A$24:$E$25,2,FALSE)

IF

Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE. Use IF to conduct conditional tests on values and formulas.

Use IF function to determine the healthy category for each item – if the Fat Content is less than 18 then enter Green. If not, enter Red:

=IF(C6 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download