Chapter 20:- Spreadsheet notes
1. Explian relative cell referencing and absolute cell referencing.
Relative cell referencing means that the values change when they are replicated.
Absolute cell referencing means the cell references to stay the same.To fix these cells
we add a dollar ($) sign between the part of the reference that is to be fixed.
2.What is formulae and function ?
Ans. A formula in Excel starts with an = sign. It could be a simple formula using
mathematical operators, such as =B1+B2, a complex formula using nested statements
or a formula including functions.
Ex:’s
=A4+B4 =A4-B4 =A4/B4 =A4*B4 =A$^B4
A function has a predefined name such as SUM or AVERAGE, to perform a particular
calculation. It is an operation built into the spreadsheet.
Different functions:-
SUM
The SUM function adds two or more numbers together. In earlier tasks you used the
mathematical operator + and formulae like =B1+B2 to add the contents of two cells
together.
AVERAGE
=AVERAGE(B4:B8)
MAX
=MAX(B4:B8)
MIN
=MIN(B4:B8)
INT
=INT(B10)
ROUND (round to 0 decimal place, 1 decimal place and 2 decimal place)
1. =ROUND(B10,0) 2. =ROUND(B10,1) 3. =ROUND(B10,2)
COUNT
It is possible to count the number of numeric (number) values in a list using the COUNT
function. =COUNT(A2:A19).
COUNTA
This function counts the number of numeric or text values displayed in the cells.
=COUNTA(A2:A19)
COUNTIF
The function needed for this task is COUNTIF, which looks at the cells within a given
range and counts the number of cells in that range that meet a given condition.
=COUNTIF($B$3:$B$21,"Director")
IF
An IF function contains a pair of brackets and, within the brackets, three parts, each
separated by a comma. An example of an IF function is:
=IF(A1=5,A2*0.05,"No discount")
SUMIF
It compares each value in a range of cells and, if the value matches the given condition,
it adds the value in another related cell to form a running total.
=SUMIF($B$3:$B$21,A35,$C$3:$C$21)
Use lookups
The term ‘look up’, as used in questions, means to look up from a list.
These are: LOOKUP, HLOOKUP and VLOOKUP.
LOOKUP
LOOKUP is used to look up a value using data in the first row or the first column of a
range of cells and returns a relative value. For our purposes, this is probably the least
useful of the three formulae.
=LOOKUP(B6,$B$2:$H$3,false)
HLOOKUP
HLOOKUP is a function that performs a horizontal look up of data. This should be used
when the values that you wish to compare your data with are stored in a single row. The
values to be looked up are stored in the rows below these cells.
=HLOOKUP(B6,$B$2:$H$3,2)
VLOOKUP
VLOOKUP is a function that performs a vertical look up of data. This should be used
when the values that you wish to compare your data with are stored in a single column.
The values to be looked up are stored in the columns to the right of these cells. The
look up data can be stored either in the same file or in a different file.
=VLOOKUP(B3,client.csv!$A$2:$B$8,2,FALSE)
XLOOKUP
This is similar to HLOOKUP and VLOOKUP, but is more powerful and flexible than
either of these. It will also reference data stored in rows/columns before the lookup
value. It therefore allows backward referencing within an array. The values to be looked
up can be stored to either the right or left or above or below the lookup array. The look
up data can be stored either in the same file or in a different file.
=XLOOKUP(B3,client1.csv!$B$2:$B$8,client1.csv!$A$2:$A$8,"Not found",0,1)
