CHAPTER (20)
DATA ANALYSIS OR SPREEDSHEET
WHAT IS A DATA MODEL?
• Spreadsheet model is used to explore different possible answers
• It is called "what-if" scenario or "what-if" modelling
• It lets you change data in the spreadsheet to see what will happen to the
results.
CELLS
• The content of spreadsheet cells include:
• Number
• Text
• Formula
• Function
FORMULA
• Formula contains both absolute referencing and relative referencing.
• The reference to a Cell with the $ symbol is absolute referencing
• The reference to a Cell without the $ symbol is relative referencing
DATA ACCURACY
• When you type data into a spreadsheet, you must make sure that the data that
you have entered is identical to the original source document.
USE FORMULA
• Mathematical operators can be used to add, subtract, multiply, divide and calculate indices (powers) of a number.
• For addition use the + symbol.
• For subtraction use the - symbol.
• For multiplication use the * symbol.
• For division use the / symbol.
• For Indices use the ^ symbol
The precedence of calculation in Excel:
1. Bracket ()
2. indices ^
3. Multiplications * and Division /
4. Addition + and Subtraction -
NAMED CELLS AND RANGES
• When an individual cell or an area of a spreadsheet is going to be
used a number of times within the formulae of a spreadsheet, it is often a good
idea to give it a name.
FORMULA AND FUNCTION
• Formula
• A formula starts with an = sign.
• It could be a simple formula using mathematical operators
• Functions
• A function has a predefined name such as SUM, AVERAGE, MIN, MAX, INT, ROUND,
COUNT, COUNTA, COUNTIF, IF, to perform a particular calculation
COUNT AND COUNTA
• COUNT
• It is possible to count the number of numeric (number) values in a list using the
COUNT function.
• It will not count any blank cells within the range.
• COUNTA
• It is possible to counts the number of numeric or text values displayed in the cells.
• It will not count any blank cells within the range.
COUNTIF, IF, SUMIF
• COUNTIF
• which looks at the cells within a given range and counts the number of cells in that
range that meet a given condition.
• IF
• The first part is a condition, the second parts is what to do if the condition is met,
and the third parts is what to do if the condition is not met.
• 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.
LOOKUP, HLOOKUP, VLOOKUP
• 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.
• 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.
• 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.
TEST THE DATA MODEL
• Normal data that you would expect to work with your formulae
• Extreme data to test the boundaries
• Abnormal data that you would not expect to be accepted
SEARCH USING WILDCARDS
• The* (asterisk) character is often used to show a number of characters
(including 0)
• The ? (question mark) is often used to show a single character.
CONDITIONAL FORMATTING
• Conditional formatting is used to change the display format (usually the font or
background color within a cell), depending on the contents of the cell.
LISTS
• Number List, which is an ordered list
• Bulleted List, which is an unordered list
