CBSE Class 7 Computer Science Formulas and Functions in MS Excel Notes Set 01

What is a formula?

A formula is an instruction that you type into a cell so that Excel calculates a result for you. Every formula starts with the equals sign (=). After it you can use numbers, cell addresses such as B2, operators such as + − × ÷, and functions. The big advantage is that the answer updates automatically whenever the numbers in the referenced cells change.

The parts of a formula are:

1. References: the cell or range of cells used in the calculation, for example C2.
2. Operators: symbols that tell Excel what to do, such as + (add), - (subtract), * (multiply), / (divide) and ^ (power).
3. Constants: fixed numbers or text typed directly into the formula.
4. Functions: ready-made formulas such as SUM.

Creating a basic formula

Suppose the maths marks of seven students are in C2 to C8 and you want the total in C9. Click C9, type = and then C2+C3+C4+C5+C6+C7+C8, and press Enter. The total appears in C9. Excel follows the usual order of operations (brackets first, then powers, division and multiplication, then addition and subtraction), so use brackets when you want a different order.

Joining text (concatenation)

The ampersand (&) joins two or more pieces of text into one. If A1 holds the word Delhi and B1 holds the word Gate, the formula =A1&" "&B1 in C1 shows Delhi Gate. The quotation marks around the space add a gap between the two words. Joining text in this way is called concatenation.

Cell, range and cell reference

A cell address is made of a column letter and a row number, such as D5. A range is a rectangular block of cells, written with a colon, for example C1:C10 (a column) or A1:C3 (a block). The address used inside a formula is called a cell reference. There are three kinds.

1. Relative reference (A1): changes automatically when the formula is copied to another cell. This is the default in Excel.
2. Absolute reference ($A$1): never changes, because the $ sign locks both the column and the row.
3. Mixed reference ($A1 or A$1): locks only the column or only the row.

Tip: pressing the F4 key while the cursor is on a reference cycles through these types.

Formula errors

When Excel cannot calculate a formula, it shows an error code instead of an answer.

1. ####: the column is too narrow to show the value. Widen the column.
2. #NAME?: Excel does not recognise a function name or text in the formula, often because of a spelling mistake.
3. #VALUE!: the formula uses the wrong type of data, such as adding text to a number.
4. #REF!: the formula refers to a cell that has been deleted or does not exist.
5. #DIV/0!: a number is being divided by zero or by an empty cell.

Functions

A function is a built-in formula that does a calculation for you. It starts with =, then the function name, then arguments inside brackets: =FUNCTIONNAME(argument1, argument2). Arguments are the inputs the function works on and can be numbers, text or cell ranges. The formula tab has a Function Library that groups all functions by type, so you do not need to remember them all.

1. SUM(A1:A5): adds all the values in the range.
2. AVERAGE(A1:A5): gives the mean of the values.
3. MAX(A1:A5): gives the largest value.
4. MIN(A1:A5): gives the smallest value.
5. TODAY(): shows the current date and updates every day.

Managing worksheets

A new workbook opens with one or more sheets, and you can add as many as you need. To rename a sheet, right-click its tab, choose Rename, type the new name and press Enter. To delete a sheet, right-click its tab and choose Delete. Workbooks are saved with the .xlsx extension in current versions of Excel.

Worked examples

Example 1. Cells B2, B3 and B4 hold 40, 55 and 70. Write a formula for their average. Answer: =AVERAGE(B2:B4) gives (40 + 55 + 70) ÷ 3 = 55.

Example 2. The formula =A1+B1 is in C1 and is copied to C2. What does C2 contain? Because the references are relative, C2 contains =A2+B2.

Example 3. Cell B1 holds a tax rate and you want every price in column A multiplied by it. Write =A2*$B$1 in the first row and copy it down. The absolute reference keeps pointing at B1 while A2 changes to A3, A4 and so on.

Quick Check Questions