IS102 Computer as an Analysis Tool PDF

Preview

Summary

EXCEL 2011 Features Functions Saturday, 25 April 2015 19:20 Spreadsheet Window Excel spreadsheet file is designed to be like a book and is thus a Each page in a workbook is a CTRL view all formulas in cell OR change back to results view Computation Cell Referencing Absolute referencing: same value i...

Description

EXCEL 2011 Features & Functions Saturday, 25 April 2015

19:20

Spreadsheet Window - Excel spreadsheet file is designed to be like a book and is thus a 'workbook' - Each page in a workbook is a 'worksheet' - CTRL + ' → view all formulas in cell OR change back to results view Computation - Average(___), Max(___), Min(___) Cell Referencing - Absolute referencing: same value in designated cell will be applied after a Fill operation $A$1 - Mixed referencing: only column/row is fixed $A1, A$1 - Use formula auditing to see precedents or dependents of a formula Formula → Trace precedents/dependents - To hyperlink a cell, right-click and select cell reference to hyperlink to - To search cells that are referenced externally

Formatting - Simple conditional formatting → Home → Styles/Cells - Complex conditional formatting requires use of logical formulas → Home → Styles → Conditional Formatting → New Rule ○ To highlight entire row based on one cell

▪ Rule → $D3="Sedan" means that this rule applies from D3:D… onwards □ ALWAYS SELECT THE CORRECT RANGE

▪ Applies to → $B$3:$E$18 means that the format applies to all these cells if the rule is fulfilled ▪ To apply multiple formulas, use AND()

○ To highlight one cell in every row

Computer as an Analysis Tool Page 1

○ To highlight duplicate rows with multiple columns, use COUNTIFS($C$14:$C$63,$C14,$D$14:$D$63,$D14,$E$14:$E$63,$E14,$F$14:$F$63,$F14,$G$14:$G$63,$G14)>1

▪ To ensure that not the entire table gets highlighted, remove $ from the row number (as bolded above) ○ To highlight minimum cells in every row, use =G35=MIN($G35:$P35)

○ To highlight cells that contain a text =ISNUMBER(SEARCH(substring,A1))

- To hide numbers when creating a Heat Map, change the formatting

Data Validation & Lookup - Data validation ○ Applied to check inputs as they are entered as input cells can be subject to entry mistakes ○ Checks variable type and value range for applied cells ○ Data → Data Validation

Computer as an Analysis Tool Page 2

○ Can also simply type items directly into the textbox when making a list

OR to reference to a dynamic list/range, use INDIRECT()

- Table lookup: searching for value(s) in a table ○ LOOKUP(lookupValue, lookupRange, valueRange) OR LOOKUP(lookupValue, array) ▪ Returns value in valueRange that has same relative position that lookupValue has in lookupRange ▪ Values in lookupRange must be in ascending order ▪ If lookupValue is not in lookupRange, Excel matches the largest value in lookupRange that is less than lookupValue ▪ To generate random values from a given distribution frequency table, replace lookupValue with RAND() ▪ LOOKUP is best applied when lookupRange and valueRange are not residing in the same table or are not of the same (row/column) orientation ▪ 2^15 may be used as the lookupValue =LOOKUP(2^15,SEARCH(A2:A995,K2),B2:B995) □ SEARCH searches for the value K2 in all of the cells in the range A2:A995  It returns the position number of the start of that value (if found) and the largest number of characters allowed in a cell is 32767 or 2^15-1  Having a lookup value of 2^15 thus guarantees that that value will be larger than anything that SEARCH returns ◊ This means that you get a match with the last cell that contains K2 ○ VLOOKUP(lookupValue, tableArray, columnIndexNum, rangeLookup) ▪ Searches for lookupValue in left-most column of tableArray, and returns value in same relative row position in column identified by columnIndexNum □ Searches VERTICALLY and returns value in the same row under the specified column ▪ ColumnIndexNum is a positive integer and rangeLookup is a logical value □ tableArray = table range  Values in tableArray must be in ascending order □ rangeLookup = True/False  If TRUE (approximate match), will lookup for largest value ≤ lookupValue  If omitted, rangeLookup is TRUE □ columnIndexNum = column number from the left ○ HLOOKUP(lookupValue, tableArray, rowIndexNum, rangeLookup) ▪ Same as VLOOKUP except that HLOOKUP returns the value in the specified row instead ▪ Searches HORIZONTALLY and returns value in same column under the specified row ▪ Values in tableArray must be in ascending order ○ MATCH(lookupValue, lookupRange, matchType) ▪ Returns relative position that lookupValue is in lookupRange ▪ If matchType is 0, MATCH looks for exact value of lookupValue in lookupRange □ Returns #N/A if none is found □ First matched position is returned if there are multiple possible matches □ LookupRange can be placed in ANY ORDER ▪ If matchType is 1, MATCH looks for largest value ≤ lookupValue □ LookupRange must be in ASCENDING ORDER ▪ If matchType is -1, MATCH looks for smallest value ≥ lookupValue □ LookupRange must be in DESCENDING ORDER □ Only MATCH can deal with approximate matches of a descending sorted lookupRange ○ INDEX(tableArray, rowIndex, [columnIndex]) ▪ Returns value found in the relative rowIndex and columnIndex positions in tableArray □ When tableArray is a single column/row of values, columnIndex is not needed □ But if tableArray is a multiple-row and multiple-column matrix, both rowIndex and columnIndex are needed to identify the value ▪ [] indicates that columnIndex is an optimal input ▪ To INDEX-MATCH with multiple criteria 1. http://www.exceltactics.com/vlookup-multiple-criteria-using-index-match/4/#Using-INDEX-and-MATCH-with-Two-Criteria

Computer as an Analysis Tool Page 3

 {=INDEX(C4:H1159,MATCH(1,(C4:C1159=A2)*(D4:D1159=B2),0),6)} ◊ IF C4:C1159=A2 is TRUE and D4:D1159=B2 is TRUE then 1 is returned  Thus, match with 1 2. https://www.deskbright.com/excel/index-match-multiple-criteria/

When searching for criteria that is not exact

Random Numbers & Resampling - Random numbers ○ RAND() returns with equal probability a random value in [0,1] (between 0 and 1, inclusive of 0 but not 1] ▪ Uniform probability density ○ RANDBETWEEN(loNum, hiNum) returns with equal probability a random integer between loNum and hiNum ○ To randomize among selected numbers, use CHOOSE(RANDBETWEEN(1,4),0,1,3,9) ▪ This means to choose randomly index 1 to 4, where index 1 = 0, index 2 = 1, index 3 = 3, index 4 = 9 ○ Calculation option ▪ When set to 'automatic', any recomputation or data entry into worksheet will cause random function to generate a new random number ▪ To restrict this, set calculation option to 'manual', and key 'fn + F9' to regenerate a new random value - Resampling data

○ Important when we want to generate random data points from sample data points ○ SMALL(valueRange, k) ▪ Returns kth smallest value, with k being an integer, in data set specified by valueRange ▪ SMALL(valueRange, RANDBETWEEN(1, n)) resamples with equal probability the n sample data values stored in valueRange ▪ To find a minimum value excluding 0 ○ LARGE(valueRange, k) ▪ Returns kth largest value, with k being an integer, in data set specified by valueRange ▪ LARGE(valueRange, RANDBETWEEN(1, n)) resamples with equal probability the n sample data values stored in valueRange ○ PERCENTILE(valueRange, k) ▪ Returns kth fractile, with k being a real number between 0 and 1 (inclusive), from among data in valueRange ▪ PERCENTILE(valueRange, RAND()) resamples with equal probability the n sample data values stored in valueRange □ And also interpolates when fractile required does not coincide with any given sample data points and it returns a continuous value ▪ May end up having a resulting data point which is not one of the given sample data points due to interpolation □ When used with INT function, it is even applicable for resampling discrete data ○ PERCENTRANK(Array, X, [Significance]) ▪ Returns percentile rank of X value in the array ▪ Significance is an optional value that identifies the number of significant digits for the returned percentage, 3 digits if omitted (0.xxx%) ○ QUARTILE(Array, Quart) ▪ Returns the quartile of a data set ▪ Quart has values 1 to 5 1. Minimum value 2. First quartile (25th percentile) 3. Second quartile (50th percentile) 4. Third quartile (75th percentile) 5. Maximum value Automatic Tabulation - DataTable: automatically generates table of results of a spreadsheet model by replacing up to two of its variables with many given sets of input values for them ○ Data → Data Tools → What-If Analysis ○ Single input variable (in one-dimensional DataTable) with one or more output variables 1. Select entire table A2:C6 and click on What-If Analysis 2. Keep Row Input Cell empty and choose A1 for Column Input Cell

○ Two input variables (in two-dimensional DataTable) with only one output variable

Computer as an Analysis Tool Page 4

1. Select entire table A4:F13 and click on What-If Analysis 2. Row Input Cell is X2 □ Select cell which is INPUT to the cells in the ROW 4 3. Column Input Cell is X1 □ Select cell which is INPUT to the cells in the COLUMN A

○ Monti-Carlo Simulation → use DataTable to simulate result any number of times 1. Create a TempInputCell 2. Set Column Input Cell to that cell

○ DataTable will not work if it is referenced to another DataTable ○ 3-variable DataTable: https://www.amttraining.com/knowledgebank/excel/building-of-three-variable-data-tables-expanding-the-limits-of-excel/ ○ Data references must always be hard-coded, especially if the "0% change" does not start at the top left hand corner

○ Data Table Input Cell Reference Is Not Valid error occurs when your input cells refer to ANOTHER SHEET ▪ They must reference to cells in the current sheet - TableSolve will allow multiple operation computations that concurrently involve more than two input variables than more than one output variable ○ Also TableSolve subroutines can be set to call any subroutine of your choice with every change in data values in its operations ▪ Particularly useful when computation involves any form of search or optimisation Chart Insert → Charts - When the x-axis is the timing, Excel has the bounds from 0 to 1 (represents 24 hours)

- PivotTable Charts Insert → Charts → PivotTable Chart

Computer as an Analysis Tool Page 5

- To sort from smallest to largest in a stacked bar chart, sort the data source

- To make a Histogram

- Graphing highly skewed data

a. Use a secondary axis: break the data into two columns (“small” numbers in one and “large” numbers in the other) and plot them on separate axes

b. Take the logarithm

Dot plot using a logarithmic scale

c. Use a scale break

Computer as an Analysis Tool Page 6

▪ When the logarithm don't work, use a full-panel scale break

d. Use small multiples

Date, Time, & Text - Date ○ Two date systems in Excel Option → Advanced → Under This Workbook… ▪ 1900 date system (default) ▪ 1904 date system □ Used as 1900 date system erroneously assumes 29 February 1900 exists □ Thus, all dates after 29 February 1900 are assigned an integer one larger than they should be ○ TODAY() → returns current date ○ YEAR(serialNumber) → returns year corresponsding to the serialNumber ▪ Wrong: YEAR(14-Jan-05) ▪ OK: YEAR("14-Jan-05") ▪ OK: YEAR(B15) where B15 has value 14-Jan-05 ▪ OK: YEAR (39014) = 2006 ○ MONTH(serialNumber) → returns month ▪ MONTH(39014) = 10 □ Since 39014 is 24 October 2006 in Excel ▪ Get month name from date

□ CHOOSE(index_num, value1, [value2], …) → chooses a value or action to perform from a list of values, based on an index number  This works as MONTH returns a number 1-12 that corresponds to the month name ▪ Get month number from month name □ MONTH(A1&1) □ If A1 contains month name, then concatenating 1 like A1&1 produces January1 which MONTH function evaluates as date January 1 of the current year (January 1 2016) ○ DAY(serialNumber) → returns day ▪ DAY(39014) = 24 ▪ To obtain day name from date

○ DAYS(end_date, start_date) → returns the number of days in between dates ▪ To count all days including end_date, +1 to the result ○ DATE(year, month, day) → returns the serial number ▪ Can be flexible DATE(2015,1,200), returns 200th day of 2015 ▪ To find number of days after, add number of days behind DATE(2006,12,23)+90 ▪ Get quarter from date ROUNDUP(MONTH(date)/3, 0) ○ NETWORKDAYS(start_date, end_date, [holidays_range]) → returns the number of workdays between two dates ▪ Automatically excludes weekends, and can optionally exclude a custom list of holidays as well □ [holidays] → list dates that should be considered non-work days ▪ To set what days of the week are considered weekends, use NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays_range]) ○ WORKDAY (start_date, days, [holidays]) → returns the nearest working day in the future/past ▪ To set what days of the week are considered weekends, use WORKDAY.INTL(start_date, end_date, [weekend], [holidays_range]) ○ EOMONTH(start_date, months) → returns the serial number for the last day of the month that is the indicated number of months before or after start_date - Time ○ Also managed in a serial number format, except time is stored as the fractional part of the serial number (digits to the right of the decimal point)

Computer as an Analysis Tool Page 7

○ NOW() → returns current date and time ▪ To find number of hours before/after current time/date, NOW()-1000/24 (convert 1000 hours into days) ○ TIME(hour, minute, second) → returns serial number to the right of the decimal point in 0.####### format ▪ To calculate time before or after, TIME(8,46,0)+TIME(2,27,0) OR TIME(8+2,46+27,0) ○ HOUR(serialNumber) → returns hour of serial number ▪ To find difference in hours, HOUR(C9)-HOUR(B9) OR HOUR(C9-B9) ○ MINUTE(serialNumber) → returns minute of serial number ○ SECOND(serialNumber) → returns second of serial number - Text management ○ Functions to remove extra characters ▪ CLEAN(text) □ Removes all non-printable characters from a supplied text string

▪ TRIM(text) □ Removes all spaces from a text string except for single spaces between words ○ Functions to convert between upper and lower case ▪ PROPER(text) □ Converts a text string to proper case; first letter in each word in uppercase, and all other letters to lowercase ▪ UPPER(text) □ Converts all characters in a supplied text string to upper case ▪ LOWER(text) □ Converts all characters in a supplied text string to lower case ○ Functions to convert excel data types ▪ BAHTTEXT(text) □ Converts a number, plus the suffix "Baht" into Thai text

▪ DOLLAR(number, [decimals]) □ Converts a supplied number into text, using a currency format

▪ FIXED(number, [decimals], [no_commas]) □ Rounds a supplied number to a specified number of decimal places, and then converts this into text

▪ TEXT(value, format_text) □ Converts a supplied value into text, using a user-specified format

Computer as an Analysis Tool Page 8

□ When text converts a date, it looks only at the date □ To display numbers with a comma, TEXT(value, "#,000") □ To display the current month, TEXT(TODAY(),"mmm") ▪ VALUE(text) □ Converts a text string into a numeric value

▪ NUMBERVALUE(text, [decimal_separator], [group_separator]) □ Converts text to a number, in a locale-independent way

○ Converting between characters and numeric codes ○ Cutting up and piecing together text strings ▪ CONCATENATE(string1, string2, …) □ Combines end-to-end text strings string1, string2, … □ Alternative approach is to use concatenation operator & → "ABC" & "DEF" = "ABCDEF" □ Useful for specifying criteria in functions such as COUNTIF, COUNTIFS, SUMIF, SUMIFS ▪ LEFT(string, [numChar]) □ Returns left-most numChar characters in text string  Integer numChar is assumed to be 1 if unspecified ▪ RIGHT(string, [numChar]) □ Returns right-most numChar characters in text string  Integer numChar is assumed to be 1 if unspecified ▪ MID(string, startNum, [numChar]) □ Returns the characters from the middle of a text string, given a starting position and length  Integer numChar is assumed to be 1 if unspecified Information functions ○ ▪ LEN(string) → returns number of characters in text string

▪ FIND(findString, string, [startNumChar])

□ Searches for text findString in text string, starting from the startNumChar position in string  Integer startNumChar is assumed to be 1 if unspecified □ Returns position of first character of findString in text string, counting from the left  If there are multiple occurrences of findString in string, then the first occurrence will be the one found ▪ SEARCH(findString, string, [startNumChar]) □ Similar to FIND except that SEARCH is case-insensitive

▪ EXACT(text1, text2) □ Tests if two supplied text strings or values are exactly equal and if so, returns TRUE; Otherwise, the function returns FALSE

▪ T(value) □ Tests if a supplied value is text and if so, returns the supplied text; Otherwise, the function returns an empty text string

○ Replacing/substituting parts of a text string ▪ SUBSTITUTE(text, old_text, new_text, [instance_num])

Computer as an Analysis Tool Page 9

□ Replaces existing text with new text in a text spring; case-sensitive □ [instance_num] specifies which occurrence of old_text to be replaced  If omitted, every instance of old_text is replaced ▪ REPLACE(old_text, start_num, num_chars, new_text) □ Replaces part of a text string with a different text string □ old_text is text in which you want to replace some characters □ start_num is the position of the character in old_text to replace with new_text □ num_chars is the number of characters in old_text to replace □ new_text is text that will replace characters in old_text Logical Formula & Function - Logical formulas return a TRUE or FALSE value ○ Operators used: = , < , > , =< , >= , , AND , OR , NOT , IF ▪ AND and OR can combine the results of more than one logical test to depict more complex evaluations

□ AND returns a TRUE, if and only if all logical tests within it are true □ OR returns a TRUE, when at least one logical test it evaluates is true □ NOT transforms a FALSE to a TRUE, and TRUE to a FALSE ○ Logical formulas can be used in conjunction with arithmetic computations ▪ In such cases, TRUE will be evaluated as a 1 and FALSE as a 0 ○ IF(logicalTest, resultIfTrue, resultIfFalse) ▪ logicalTest condition would be a logical formula ▪ Both resultIfTrue and resultIfFalse can be values, cell references, or formulas ▪ A nested IF function is an IF function with another IF function as its resultIfTrue or resultIfFalse □ Excel 2011 allows up to 64 levels of nesting ○ IFERROR(value, valueIfError) ▪ Checks value against error types #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, #NULL! ▪ If value is not in error, then value is returned, else valueIfError is returned ○ ISERROR(value) ▪ Returns the logical value TRUE if value refers to any error value #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, #NULL!; otherwise, it returns FALSE ○ ISERR(value) ▪ Returns the logical value TRUE if value refers to any error value except #N/A; otherwise, it returns FALSE ○ ISNUMBER(value) ▪ Returns the logical value TRUE if value refers to a number; otherwise it returns a FALSE ▪ In the case where value refers to a string, as formulas such as LEFT, MID, RIGHT returns a string (it also returns number as a string), ISNUMBER will return a FALSE □ Thus, use VALUE to convert string to number □ VALUE(text)  Converts a text string that represents a number to a number - Logical count and sum ○ COUNT function counts number of cells in specified cell range that contains numerical values ○ COUNTA counts number of cells that contains numerical or text values ○ COUNTIF(range, criteria) ▪ Counts number of values in the specified cell range that satisfies the criteria ▪ Criteria is a simple logical expression expressed (in quotes) as a text □ To reference criteria to another cell use "&"  =COUNTIF(B13:B13,">="&S13) □ To apply a partial match to the text in the cells, use "*text*" in the criteria =COUNTIF(B13:B13,"*three*")  An asterisk (*) is used to find cells ...