MS Excel

Introduction

This is an attempt to understand data and analytics using MS Excel. MS Excel V16.4

Name Range

Name ranges could be used as variables to put into formulas. Makes the workflow easy and convenient to search and manage.

Functions

Aggregation

• = SUM(<range>)
• = SUMIF(<range>, "<500")
• = SUMIF(<criteria_range>, "<criteria>", <SUM_RANGE>)
• = SUMIFS( <sum_range>, <criteria_range_1>, "<criteria_1>", [<criteria_range_2>, "<criteria_2>"])
• = SUMPRODUCT(<array_1>, <array_2>)

Count

• = COUNT(<range>)
• = COUNTA(<range>)
• = COUNTBLANK(<range>)
• = COUNTIF(<criteria_range>, "<criteria>", <count_range>)
• = COUNTIFS(<count_range>, <criteria_range_1>, "<criteria_1>", [<criteria_range_2>, "<criteria_2>"])

COUNT -- Considers only numbers. COUNTA -- Considers text. Average functions

• = AVERAGE(<range>)
• = AVERAGEA()
• = AVERAGEIF(<criteria_range>, "<criteria>")
• = COUNTIF(<criteria_range>, "<criteria>", <average_range>)
• = AVERAGEIFS(<average_range>, <criteria_range_1>, "<criteria_1>", [<criteria_range_2>, "<criteria_2>"])

AVERAGE -- Considers only numbers ; Omits text from counting. AVERAGEA -- Considers text in the counting. Rand

• = RAND()
• = RAND()*100
• = RANDBETWEEN(<bottom>, <top>)

Round

• = ROUND(<number>,<decimal_position>)
• = ROUNDUP(<number>,<decimal_position>)
• = ROUNDDOWN(<number>,<decimal_position>)

Decimal position +2 is hundredth decimal place. Similarly -3 is thousands place.

• = ROUND(23.557, 2) = 23.560
• = ROUND(2555, -3) = 3000

ROUNDUP and ROUNDDOWN are like Ceil() and Floor().

• = ROUNDUP(100.001,0) = 101
• = ROUNDDOWN(100.999,0) = 100

Subtotals :

• Data -> Subtotal

Grouping and totalling data wrt changes in columns. Does data folding as well.

Date and Time functions

• = TODAY() , YEAR() , MONTH() , DAY()
• = WORKDAY(), WEEKDAY(), WEEKNUM(), EOMONTH()
• = EDATE(<date>,<months>)
• = NETWORKDAYS(start_date,end_date,[holidays])
• = NOW() , HOUR() , MINUTE() , SECOND() , TIME(hour,minute,second)
• = DATEDIF(start_date,end_date,"<format>")
• Where format could be : "Y" , "M" , "D" , "YM" ,"MD"

Text Functions

• = TRIM(), LOWER(), UPPER(), PROPER()
• = LEFT(text,[< num_chars >]) , RIGHT(...)
• = MID(text,start_num, [< num_chars >])

• = FIND(find_text,within_text)
• = SEARCH() , REPLACE(), SUBSTITUE()

• = CONCATENATE(), TEXT(), LEN(), CHAR(), CODE()

CHAR and CODE convert from and to ASCII

Statistical functions

• = MIN(), MINA(), MAX(), MAXA(), MEDIANS()

Lookup

• Lookup Table : The table data used to lookup
• LookUp Value : The value against which we search.

• = VLOOKUP(lookup_value,table_array,col_index_num,match_type)

Vlookup could be used along with Data -> Data Validation -> Allow "List" to create a dropdown for the possible values. Lookup value should be unique and should belong to the first column of the array.

• = HLOOKUP(lookup_value,table_array,row_index_num,match_type)
• = LOOKUP(lookup_value,lookup_vector,result_vector)

Lookup vector should be sorted in ascending order and should have unique values

Pivot tables

Pivot tables are used to aggregate data and group them w.r.t criteria. Here's a reference video to understand the expected behaviour.

Slicers

Slicers are visual filters in the form of interactive buttons. They are used to filter the data present in the pivot table. Pivot table timelines

Pivot charts

Ordered pair functions

• = SLOPE(y_array,x_array)
• = INTERCEPT(y_array,x_array)
• = CORREL(array_1,array_2)
• = RSQ(y_array,x_array)

Correlation: CORREL R-Squared:

Solver

The solver add-in could be used to solve optimisation problems.

Last updated: 21 Jun 2021, 12 Sept 2020