# MS Excel

## Introduction

This is an attempt to understanding 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 -- Consideres 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.

## 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 optimization problems.