Things we need to know about Microsoft Excel for this Class

Formulas

   A cell can contain a number or string, but it can also contain a formula.  Formulas are equations, and start with the '=' sign.  A simple formula could be "=10+2/6".

References

   References appear in formulas.  They point to a cell or a range of cells.  The reference is the column letter followed by the row number, like 'A2', 'C5', or 'D4'.  You can use them in formulas to make one cell a calculation based on the contents of another cell, like '=A2+B2'.

Ranges

   A Range is a rectangle of cells in the spreadsheet.  The ranges we have been using have only been one column wide or one row high.  In excel, a Range is defined by two references separated by a colon character, like 'A2:A8', which references cells A2, A3, A4, A5, A6, A7, and A8.

Functions

   Many functions that can be used in formulas are built into Excel.  Here are some of the important ones we know about in class.

• SUM(range) adds the numbers in 'range'.  So if I wanted to add up cells A1,A2, A3, A4, A5, A6, A7, and A8, I would specify =SUM(A1:A8).  Note that I could also specify =SUM( A1,A2, A3, A4, A5, A6, A7, A8 ).
• MATCH(thing_to_look_for, range_to_look_for_it_in, 0) finds the offset of the FIRST cell in the range specified that is equal to the thing_to_look_for.  Example:

MATCH() is used along with INDEX() to lookup things across columns.  Note that there is a LOOKUP() function that does this in one shot, but it requires the lookup list to be alphabetical.

• INDEX(range, offset_into_range) returns the VALUE within the range at offset_into_range.  So, in the example below, we use MATCH() to get the offset of the item that matches 'Item 3' and then use that offset to get its corresponding value in column B.