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.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related searches
- asha phonological processes
- asha phonological processes norms
- asha speech sound acquisition chart
- asha speech sound development
- asha speech development chart
- asha speech language development chart
- asha milestones pdf
- asha speech intelligibility chart
- asha speech intelligibility rating scale
- asha speech intelligibility norms
- asha language milestones 1 2
- asha 1 2 age language norms