# Manegerial Computing

- About
- What-If Analysis
- VHX LOOKUP
- Index and Match
- Selecting Spreadsheet Areas
- Filtering Data
- DCOUNT, DAVERAGE, DSUM
- Frequency
- Dates
- Pivot Table
- String
- Statistics
- Cell Referencing
- Financial Formulae

## About

MGRCMP is all about computations a decision maker needs to do on a daily basis to make efficient decisions. This course is taught via MS Excel.## What-If Analysis

What-if Analysis tools are powerful to automate computations using existing formula references and prevent redundant copy-pasta of entire computation workflow.Three what-if analysis tools are:

**Goal Seek**: Write down output/input constraints and Excel shall give you appropriate values that fit.**Data Table**: Find the outputs for given output references, by providing input values for input references.**Scenario Manager**: Similar to Data Table, but outputs fixed values in new sheets.

## VHX LOOKUP

**VLOOKUP**does a vertical search and gets you a

*value*for a given column offset.

**HLOOKUP**does a horizontal search and gets you a

*value*for a given row offset.

**LOOKUP**does relative search on a target array wrt given lookup value and input array.

## Index and Match

**Index**gives VALUE for given X and Y offset of a reference array.

**Match**gives OFFSET (index) of given lookup value in a given array. Match could do less, greater or exact matches.

## Selecting Spreadsheet Areas

- =INDEX(A1:C14,0,3)

- =OFFSET(A1,
*offset*,*offset*,*height*,*width*)

## Filtering Data

**Filters**could be used to selecting a subset of the given data.

**Advanced filtering**options help filter data. A common methodology is to write a boolean conditionals for data in first row of the given database (table) and use Advanced filters with them. This will iterate conditions over all rows of the the database.

Another method would be using Pivot Tables.

## DCOUNT, DAVERAGE, DSUM

All D- Functions to use functions on a DB and for*given criteria*.

## Frequency

- =FREQUENCY(
*data array*,*bins array*)

## Dates

Dates are stored as number of days since 1/1/1900Time is stored as percentage of day passed

`Ctrl`+`1`

- =DAY(
*date*) ; =MONTH(...)

- =TEXT(MONTH(...), "mmmm")

- =WORKDAY(
*date*,*no. of days offset*,*[Holiday set]*)

29-02-2003 is taken as a value - Bug in excel

## Pivot Table

- Adding a new Calculated Column through Pivot Table Design
- Clearing out cache to reset Pivot Table

## String

- =SUBSTITUTE(
*string*,replace this,with this)

- =EXACT(
*string*,*string*) vs '='

- =TRIM()

- =CODE()

- =MID(
*string*,starting index, number of characters)

## Statistics

- =LINEST()
- =TREND()
- Analysis Tools; R-squared and p values

## Cell Referencing

- =INDIRECT(A2)
- =INDIRECT(
*named range*)

- =ADDRESS()

Last Updated: 19-20-26-27 Aug 2021