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

Count

COUNT -- Considers only numbers.

COUNTA -- Considers text

Average functions

AVERAGE -- Consideres only numbers ; Omits text from counting.

AVERAGEA -- Considers text in the counting.

Rand

Round

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

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

Subtotals :

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

Date and Time functions

Text Functions

CHAR and CODE convert from and to ASCII

Statistical functions

Lookup

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.

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

Correlation: CORREL

R-Squared:

Solver

The solver add in could be used to solve optimization problems.