Spreadsheet Tips
SPREADSHEET NOTES
E
xcel is a spreadsheet program. Spreadsheet programs are model-building tools. You set relations between cells by writing formulas, and see what happens when you change values of variables. If, for example, you enter a formula like
=A1
in cell C1, you ask the program to display in C1 the value that is in A1[1].
When entering formulas, it is easy to run into trouble if you write a cell address manually (you may mistype the address). It is safer to let the program write the address by pointing to it, using either the keyboard or the mouse. This method is especially handy when entering an address in another sheet. Another common mistake to watch out: When entering an address in another worksheet, do not unnecessarily move from one sheet to the other. For example, you are entering a formula in a cell in Sheet1 and you refer to a cell in Sheet2, like =Sheet2!A1. Now, if you move back to Sheet1 before completing the formula, the formula bar will change to =Sheet1!A1, which is incorrect.
Arithmetic operations and their operators:
Operation / Sign / NameAddition / + / Plus
Subtraction / - / Minus
Multiplication / * / Asterisk
Division / / / Slash
Exponentiation / ^ / Caret
When you need to enter a value (numeric or alphanumeric) more than once, make sure that they are interrelated with a formula. Otherwise, if one of them is changed, the others will remain unchanged.
Below are some common arithmetic operators:
Conventional notation /Excel notation
A1 + B1 / =A1+B1A1 – B1 / =A1-B1
A1 x B1 / =A1*B1
A1
B1 / =A1/B1
A1B1 / =A1^B1
/ =A1^(1/B1)
You can test a condition, like whether values of two cells are equal, with a formula like
=A1=B1
in cell C1. It reads like “is the value in cell A1 equal to the value in cell B1?” The formula will return either TRUE or FALSE, as illustrated below:
A / B / C / Formulas in column C1 / 25 / 50 / FALSE / =A1=B1
2 / 25 / 50 / TRUE / =A2<B2
E
xcel has predefined formulas, which are called functions. Their general syntax is =function(arguments). For example, if you want to add the values in A1 through A4, instead of entering the following formula,
=A1+A2+A3+A4
you can use the SUM() function:
=SUM(A1:A4) (Remember that you don't need to write addresses: after typing "=SUM(", you select the cells to be added, so that the program supplies the address entry.). Likewise, if the cells to be added are A1 through A4 and C1 through C5, the formula will be =SUM(A1:A4,C1:C5). There are two arguments here, two ranges of cells to be summed; the arguments are separated with commas.
o COUNT() function will return the number of cells that contain numeric values. For example (remember that dates are numeric data):
A / Formula in A41 / 25.12.2006
2 / 15
3 / abc
4 / 2 / =COUNT(A2:A4)
Extensions of SUM() and COUNT() functions are SUMIF() and COUNTIF() functions. Suppose we have the following table:
A / B / C1 / Name / Gender / Reading capacity
2 / Ece / Female / 6
3 / Emre / Male / 2
4 / Merve / Female / 7
5 / Yalçın / Male / 1
Based upon this, the following table displays the number of each gender (COUNTIF()) and the total reading capacity (whatever that means) of each gender:
E / F / G1 / Male / 2 / 3
2 / Female / 2 / 13
The formula in F1 is
=COUNTIF($B$2:$B$5,E1)
which can be read as: “how many cells in the range B2-B5 have the value of E1 (male) in them?”
The formula in G1 is
=SUMIF($B$2:$B$5,E1,$C$2:$C$5)
which can be read as: “what is the sum of the range C2-C5 which meet the criteria of E1 (male) in the range B2-B5?”
RAND() is another function. It generates a random value between 0 and 1. If you want a random value between 0 and 10, the formula to enter is: =RAND()*10.
AVERAGE() is another useful function.
See the following example:
A / B / Formulas in column B1 / Value 1 / Maximum
2 / 2 / 100 / =MAX(A2,100)
3 / 500 / 500 / =MAX(A3,100)
4 / 100 / =MAX(A4,100)
Here we want the formula to return either 100 or the value in column A (if there is one), whichever is greater. In the first 2 rows, the formula works, but in the third we have 100 although there is no value in A. Of the many IS functions Excel provides, see how ISBLANK function works:
A / B / Formulas in column B2 / 2 / FALSE / =ISBLANK(A1)
3 / 500 / FALSE / =ISBLANK(A2)
4 / TRUE / =ISBLANK(A3)
You could read the formula in B1 as “is the cell A1 blank?” It returns TRUE if it is empty, FALSE if the cell has either a numeric or alphanumeric value. You can use this function within an IF function, like:
A / B / Formulas in column B1 / Value 1 / Maximum
2 / 2 / 100 / =IF(ISBLANK(A2),"",MAX(A2,100))
3 / 500 / 500 / =IF(ISBLANK(A3),"",MAX(A3,100))
4 / =IF(ISBLANK(A4),"",MAX(A4,100))
The formula in B2 can be read as: “If there is no value in A2, that is, if it is empty, then return an empty cell, otherwise (if it is not blank), return 100 or the value in A2, whichever is greater.”
By inserting this condition-testing into an IF function, you can let the program take one action if the condition holds (TRUE) and another action if it fails (FALSE). In Excel’s help, syntax of IF is given as:
IF(logical_test,value_if_true,value_if_false)
which could be stated as
IF(condition,then,else)
Below are a few examples:
A / B / C / Formulas in column C1 / 25 / 50 / not equal / =IF(A1=B1,"equal","not equal")
2 / Ali / Ali / equal / =IF(A2=B2,"equal","not equal")
3 / 25 / 25 / 625 / =IF(A3<B3,"",A4*B4)
4 / Ali / Veli / =IF(A4<B4,"",A5*B5)
Note that Excel applies left-alignment for alphanumaric values and right-alignment for numeric values.
If you need to take actions for more conditions, you can embed IF functions within other IF fınctions, replacing either the 2nd or 3rd parameters. See the following examples:
A / B / Formula in column B1 / Annual Income / Tax Rate
2 / 15000000000 / 0.1 / =IF(A2<10000000000,0,IF(A2<20000000000,0.1,0.2))
Formula in B2 can be read as: If A2 is less than 10 million, then return 0 in B2; otherwise (A2 is equal to or more than 10 million), if A2 is less than 20 million, then return 0.1 in B2; otherwise (A2 is equal to or greater than 20 million, which is the only remaining case), put 0.2 in B2.
Excel allows up to seven nested IFs, but a formula will become very long and complicated before you reach that limit. Instead of nesting too many IFs, it may be a better idea to use a vertical or horizontal lookup table along with VLOOKUP or HLOOKUP function. To be able to use VLOOKUP function, we first need to construct a vertical table, where the first column has the values we are looking for. For example, the lookup table in A5:B9 below can be read as follows: Annual incomes between 0 and 10 billion (not included) is subject to 0% tax rate; incomes between 10B and 20B (not included) are subject to 10% rate; incomes between 20B and 40B (not included) are subject to 20% rate, those between 40B and 80B (not included) are subject to 30% rate, and incomes of 80B and above are subject to 40% tax rate.
Compare the formulas in B2 and B3, which do the same thing in the following example, where the range A5:B9 has the (vertical) lookup table
A / B / Formulas in column B1 / Annual Income / Tax Rate
2 / 15,000,000,000 / %10 / =IF(A3<10000000000,0,IF(A2<20000000000,0.1,IF(A2<40000000000,0.2,IF(A2<80000000000,0.3,0.4))))
3 / 15,000,000,000 / %10 / =VLOOKUP(A3,A5:B9,2,TRUE)
4
5 / 0 / %0 / The greyed range has the vertical lookup table.
6 / 10,000,000,000 / %10
7 / 20,000,000,000 / %20
8 / 40,000,000,000 / %30
9 / 80,000,000,000 / %40
No matter how many more conditions you add to be tested, the VLOOKUP function will remain the same compact size; you only need to change the range (address) of the table. The syntax of VLOOKUP function is
VLOOKUP(P1,P2,P3[,P4])
Where P1-P4 are the four parameters. They are
· P1 = what value to look up for in the first column of the lookup table
· P2 = the address of the lookup table
· P3 = the column number we want to be returned
· P4 = whether we want ranges (TRUE) or exact values (FALSE). The forth parameter is optional, in the sense that, if you want ranges, you don’t need to enter it. Thus, the last formula above, =VLOOKUP(A3,A5:B9,2,TRUE) is equivalent to =VLOOKUP(A3,A5:B9,2). In the above example, we do want ranges, because the annual income figure can be any non-negative value.
If/when we want ranges, we must make sure that the first column of the vertical lookup table (which has the searched values)
Þ starts with the lowest possible value, and
Þ is sorted in ascending order.
If we want exact matches (range is FALSE), then the above two rules do not apply; the program is going to search each value in the first column anyway.
O
ne can get logical results (TRUE/FALSE) like:
A / B / C / Formulas in column A1 / TRUE / 1 / 1 / =B1=C1
2 / FALSE / 1 / 2 / =B2=C2
This is especially useful with what Excel calls “information functions” like ISBLANK() and ISERROR(). Take the following example:
A / B / C / Formulas in column C1 / 12 / 2 / 6 / =A1/B1
2 / #DIV/0! / =A2/B2
3 / Total / #DIV/0! / =C1+C2
Here, since there is no variable entered in C2, the program returns a “division by zero” error. It, in turn, causes the sum formula in C3 to have an error. In such cases, we can use ISBLANK function to test whether there is a value in a cell:
A / B / C / Formulas in column C1 / 12 / 2 / 6 / =IF(ISBLANK(B1),"", A1/B1)
2 / =IF(ISBLANK(B2),"", A2/B2)
3 / Total / 6 / =C1+C2
The formula in C1 can be read as: “If there is no value in B1, then leave it empty, otherwise divide the value in A1 by it.” (This is just an example; you could, of course, test whether B1 is zero or not.)
ISBLANK() is classified as an information function. Another useful information function is ISERROR(), which refers to any error value. For example, if we have a lookup function whereby we want an exact value (4th argument is FALSE), then an entry that does not exist in the first column of a vertical lookup value will return #N/A error. Our user may not understand what this error message means. To replace it with a more meaningful message, we may want to use the ISERROR() function: Next to the column we have the lookup function, we can enter the following formulas:
=IF(ISERROR(previous column), “Entry not found”, previous column)
Now, these two columns will return the same, correct value if the entry is correct, and, if it is incorrect, then the first column will return #N/A and the second one will return “Entry not found”. Since the first column has an intermediary formula, we may hide it, so that the user will not be confused by it (to hide a column: Home / Format / Hide & Unhide..)
B
y default, an address entered in a formula is a relative address. Thus, if you enter =A1+B1 in cell C1, A1 and B1 actually do not mean A1 and B1! To verify this, copy the formula in C1 to C2: you will see that A1 becomes A2 and B1 becomes B2. If A1 and B1 had meant A1 and B1, they should remain the same when copied.
A / B / C / Formulas in column C1 / 0 / =A1+B1
2 / 0 / =A2+B2
They instead mean a location relative to the location of the cell they are entered in. Thus, since the formula is in C1, A1 means the cell in the same row and 2 columns to the left; B1 means the cell in the same row and 1 column to the left. The same relation holds when they are copied to C2.
Although relative addressing serves our purposes most of the time, there are cases when it won’t work.
A / B / C / Formulas in column B1 / Year / Price / Rate of
increase /
2 / 2000 / 1,000 / 10% /
3 / 2001 / 1,100 /
=B2*(1+C2)
4 / 2002 / 1,210 /In a formula in B3, B2 means “the same column, one row above” and C2 means “one column to the right and one row above”. The same relative relation will be maintained when the formula is copied to C4 => =B3*(1+C3). Here, we do not want C2 to change when copied, so we need to make the address absolute. On the formula bar, while the pointer is on C2, we press F4 key. C2 becomes $C$2. This means both the column (C) and the row (2) are absolute, that is, they don’t depend on the location of the formula they are in (C3). Wherever we copy that formula, $C$2 will remain the same.
We have seen that, while the pointer is on an address like C2, pressing F4 will change it to $C$2. Pressing F4 once more will cycle the address to C$2, then to $C2, and back to C2. The $ sign in front of a column letter or a row number will make that part of the address absolute.
In the following example, values for year 2000 are variable (1,000). Prices of 2 goods, G1 and G2 increase by 5% and 10%, respectively. To find the values for 2001 and 2002, entering one formula in B4 is sufficient =B3*(1+B$2); it is then copied to B4:C5.