#15 Finished Up Course

Data Analysis for Spreadsheets

Conditional Logic

• IF(expression,true_val,false_val) and be used with AND(), OR()

• WEEKDAY(date) returns 1 for sunday, 2 for monday and so on.

• COUNTIF(range,criterion) counts cells that meet a certain condition

• COUNTIFS(range,criterion1,[criterion2,criterion3,...]) when multiple conditions are to be checked

• SUMIF(range,criterion,[sum_range]) returns the sum over a range if the condition is met.
• sum_range will let us specify a different range to get sum

• Similarly we can use SUMIFS(sum_range,criteria_range1,criterion1,[critera_range2,criterion2,...])

• Another ones include AVERAGEIF() & AVERAGEIFS()

Vertical Lookup

• VLOOKUP() used to search information in a table based on search keys
• Main table where the VLOOKUP() function is being used
• Lookup table which contains all the data to be searched for

• VLOOKUP(search_key,range,index,[is_sorted])
• search_key : The value in main to search for in lookup table
• range : range containing the lookup table, not including the headers
• index : column index in lookup table to return
• is_sorted : if the lookup table is sorted or not

Course Summary

• Chapter 1 : Formulas & Calculations

• Chapter 2 : Cleaning data (date time, text)

• Chapter 3 : Conditionals & Vlookup

Intermediate Spreadsheets

• Types of data in a spreadsheet
• Numbers
• Text
• Dates
• Boolean

• Check for data types
• To check for the type, ISNUMBER() can be used to check if a cell is a number.
• Similarly ISTEXT(), ISDATE(), ISLOGICAL().
• ISBLANK() does what is says
• ISFORMULA() also exist
• ISURL() checks for all sorts of url like https:// and ftp:// , mailto: and also domain.com etc

• Copy a formula across cells
• Use the bottom right corner and drag
• Select cells and press cntrl+r to copy right & cntrl+d to copy down

• To data conversions
• Create HYPERLINK(url,[display_text]) second argument is optional
• TO_DOLLARS() &
• TO_PERCENT(1.02) will result in 102%
• N() converts to a number. N('1.23') is 1.23. Using on True will return 1.
• T() similarly converts to text.

• For unit conversions CONVERT(cell,from_unit,to_unit)
• CONVERT(1000,"ft^2","m^2") converts 1000 sq. ft. to 92.9 sq. m.

Working with Numbers

LOGs and EXPs

• LOG(value,[base]) defaults base to 10 which is same as LOG10()

• LN() & EXP() are based of is natural logarithm and exponent, with euler’s number e.
• LN(2.718) = 1 & EXP(1) = 2.718

• SQRT(64) = 8

Rounding Numbers

• ROUND(x,n) rounds x to nearest n decimal places
• n defaults to zero, giving a integer
• ROUND(1234,-2) will result in 1200 as -ve values can be used to round to nearest multiple of 10 with it

• CEIL & FLOOR
• CEILING(x,y) rounds x up to nearest multiple of y
• FLOOR(x,y) rounds x down to nearest multiple of y
• FLOOR.MATH(), CEILING.MATH() behaves same as FLOOR() and CEILING() when one or two arguments are passed.
• Passing a third argument determines the direction of the rounding
• FLOOR.MATH(x, , 1) is equivalent to SIGN(x) * FLOOR(ABS(x))
• CEILING.MATH(x, , 1) is equivalent to SIGN(x) * CEILING(ABS(x)).

• Random Numbers
• RAND() returns a fraction between 0 & 1. This generates a bell curve distribution
• RANDBETWEEN(lower,upper) returns integers rather than fractions.
• Above both generates from a continuous uniform distribution.
• To generate numbers from normal distribution/Gaussian distribution we use Normal Inverse Cumulative distribution function i.e., NORMIV()
• NORMINV(RAND(),mean,std_deviation) can similarly be used for other distributions
• LOGINV()
• TINV()
• FISHERINV()
• FINV()
• BETAINV()