Financial Modelling Module 2: Data management/manipulation

Card Set Information

 Author: jordan_hs ID: 286354 Filename: Financial Modelling Module 2: Data management/manipulation Updated: 2014-10-20 23:20:28 Tags: 125 250 M2 Data mgmt manipulation Folders: Description: Show Answers:

Home > Flashcards > Print Preview

The flashcards below were created by user jordan_hs on FreezingBlue Flashcards. What would you like to do?

1. SUM Function
The SUM function adds all the numbers that you specify as arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure).
2. SUMIF Function
• The SUMIF function sums the values in a range(range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) that meet criteria that you specify

Adds numbers in one column when a criteria in aconditional column is met
3. SUMIF particulars
a is the range of cells that you want evaluated by criteria.

b is the criteria in the form of a number, expression, a cellreference, text, or a function that defines which cells will be added.

c is the Sum‐Range, the range to be summed if the criterion is met in the Range column
4. AND Functions
• • AND
• – Returns TRUE if all conditions are correct
• – AND(A1 > 0, B1 > 0)

• • Logical1: The first condition that you want to test that can evaluate to either TRUE or FALSE.
• • logical2, ... : Additional conditions that you want to test that can evaluate to either TRUE or FALSE, up to a maximum of 255 conditions
5. OR Functions
• – Returns TRUE if at least one of the conditions are correct
• – OR (A1 > 0, B1 > 0)
6. Search Functions
·IF, VLOOKUP and HLOOKUP are three functions that allow you to put in conditional statements

• ·The syntax of the IF statement is:
• IF (condition, output of condition true, output if condition false)
7. IF Functions
• ·Returns TRUE outcome if the condition is correct, otherwise returns FALSE outcome
• ·IF(A1 > 0, B1, C1)
8. IF characteristics
• logical_test: Any value or expression that can be evaluated to TRUE or FALSE.

• value_if_true : The value that you want to be returned if the logical_test argument evaluates to TRUE.

• value_if_false: The value that you want to be returned if the logical_test argument evaluates to FALSE
9. VLOOKUP Function
• – Looks vertically for the lookup value in a table and returns the corresponding items from another colum
• – VLOOKUP (B10, A5:B8, 2, False)
10. VLOOKUP characteristics
– a is the value that is being looked for

– b is the table that is being looked in

– c is the number of columns to move across once the value being looked for is found

– d specifies whether you want an exact match or not. False finds exact. True finds closest that is notmore than
11. HLOOKUP Function
• VLOOKUP allows you to search a table that is set up vertically. That is, all of the data is set up in columns and each column is responsible for one kind of data.

HLOOKUP is the exact same function, but looks up data that has been formatted by rows instead of columns.
12. RANK Function
– Returns the rank of a number in a list of numbers

= RANK (2, A1:A50, 0)

• – a is the rank you are looking after
• – b is the list of numbers to look through
• – c specifies whether (a) is sorted by descending or ascending order. If 0 then descending. If non‐0 then ascending.
13. MATCH Function
• – Returns the relative position of an item in an array
• – Search the data range and tell me the relative row number where you find a match for (data)

= MATCH(A1, B1:B10, 0)

• – a is the value you want to find the position of
• – b is the table being looked in
14. MATCH
"c specifies..."
c specifies whether you want an exact match, or the smallest or largest value above or below (a)

• If c = 0 then the first value equal to the lookup value (a) is found
• If c = 1 then the largest value that is less than or equal to the lookup value (a) is found
• If c = ‐1 then the smallest value that is greater than or equal to the lookup value (a) is found
15. INDEX Function
• Returns a value or the reference to a value from within a table or range
• = INDEX(A1:B50, 25, 2)

• – a is the list of numbers to look through
• – b is the row number of the value to return
• – c is the column number of the value to return
16. OFFSET Function
• – Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells
• = OFFSET(B4, 2, 3)

• – a is the reference cell
• – b is the number of rows away from (a)
• – c is the number of columns away from (a)
17. CHOOSE
• – Returns a value from the list of value arguments
• = CHOOSE(A1, B1:B5, B2:B5)

• – a specifies which argument (b) or (c) should be used
• – can be more than two choices
18. Date and Time Functions
- Date, Time
• = DATE (2006, 7, 23)
• – Generates a serial number from the year (a), month(b), and day (c)

• = TIME ( 23, 32, 54)
• – Generates a decimal number representing the fractionof a day that has passed at hour (a), minute (b) andsecond (c)
19. Date and Time Functions
- DATEVALUE & TIMEVALUE
• = DATEVALUE (“17‐Aug‐2006”)
• – Returns the serial number of a date in a text string

• = TIMEVALUE(“23:32:54”)
• – Returns a decimal number from a text stringrepresenting the fraction of a day that has passed
20. Date and Time Functions
- YEARFRAC
YEARFRAC (1/3/2006, 21/7/2007)

– Returns the fraction of a year between (a) and (b)
21. Date and Time Functions
- WORKDAYS, NETWORKDAYS
• = WORKDAYS (1/3/2014, 20)
• – Returns the date that is (b) working days after (a)

• = NETWORKDAYS (1/3/2014, 1/3/2014)
• – Returns the number of working days between (a) and(b)
22. D functions
D functions manipulate data subject to some criteria. Forexample:

=DAVERAGE (A3 : L13, 3, A17:L18)

• Where:
• - a is the range of cells that makes up the list or database
• - b is the column used in the function
• - c is the range of cells that contain the conditions youspecify
23. D Functions
- Database and List Management functions
- DMAX, DMIN, DPRODUCT, DSTDEV
DAVERAGE Returns the average of selected database entries

DCOUNT Counts the cells that contain numbers in a database

DCOUNTA Counts non-blank cells in a database

DGET Extracts from a database a single record thatmatches the specified criteria
24. D functions
- Database and List Management functions
- DSTDEVP, DSUM, DVAR, DVARP
DSTDEVP Calculates the standard deviation based on the entire population of selected database entries

DSUM Adds the numbers in the field column of records in the database that match the criteria

DVAR Estimates variance based on a sample from selected database entries

• DVARP Calculates variance based on the entire
• population of selected database entries
25. Arrays
• Array formulae enable the calculation of answers using fewer steps than traditional formulae.

• Array formulae must be entered by typing Ctrl Shift Enter.
26. ARRAYS formula
• Array formulae creates the same formula over multiple cells.For example:

=TRANSPOSE(A1:A10)

• ·a is the range of cells to convert (from vertical to horizontal)
• ·Select multiple cells (10 horizontal cells in this case)
• ·Enter formula as above
• ·Press Ctrl + Shirt + Enter to create array formula
27. Goal Seek
• • Goal Seek calculates the precise solution to a problem that would otherwise require trial and error
• • To run Goal Seek select Data > What if analysis > Goal Seek

• • Fill in the dialogue box
• • Select the cell containing the formula that will return the result you're seeking
• • Select the To Value text box and enter the goal.
• • Select the By Changing Cell text box and select the cell that you want to change.

• Click OK

What would you like to do?

Home > Flashcards > Print Preview