# Managerial 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
- Worksheets and Workbooks
- Ribbon
- Basic Techniques:
- Formulas
- Financial Functions [TBD]
- Shortcuts
- Tips
- References

## 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]*)

## 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

## Financial Formulae

1. NPV## Worksheets and Workbooks

1.`Ctrl`

`Home`: Selects cell A1

2.

`Ctrl`

`End`: Selects the cell at the intersection of the last-used column and last-used row on the worksheet

3.

`Page Up`: Scrolls up one screen

4.

`Page Down`: Scrolls down one screen

5.

`Alt`

`Page Up`: Scrolls left one screen

6.

`Alt`

`Page Down`: Scrolls right one screen

7.

`Ctrl`

`Page Up`: Selects the previous worksheet

8.

`Ctrl`

`Page Down`: Selects the next worksheet

9.

`Ctrl`

`Tab`: Moves to the next open Excel workbook

## Ribbon

1. [WINDOWS only]`Alt`: Pressing the Alt key once will allow you to quickly select a ribbon using its letter key

## Basic Techniques:

- 1. Cell Limitations: 1,048,576 rows by 16,384 columns
- 2. Block of cells is called
**Range**. - 3. Holding down
`Ctrl`tells Excel that you want to jump to the end of the range. - 4. Holding down
`Shift`tells Excel to select all of the intermediate cells along the way. - 5.
`Ctrl``A`or`Ctrl``*`i.e`Ctrl``Shift``8`to Select All cells - 6.
`F8`enables 'Extend Selection' mode, ShiftF8 enables 'Add Selection' mode. These could be used in a repeated fashion to select non contiguous modes. - 7. Holding down
`Ctrl`key after selecting a range lets you select another non contiguous range. - 8. Cell selection: Name Box,
`F5`Goto Dialog Box -> Goto Special - 9. Date formatting: Dates have equivalent numeric value.
**1/1/1900**is the beginning of the Excel epoch, and that each date is 1 greater than the day before. Thus numeric values can be converted to dates. - 9. Copying Formula: Double click bottom right square of an existing cell with a formula. Alternatively, one could copy a cell with formula and then paste it on desired range.
- 10.
`F4`Absolute addressing: $D$10 when copied remains the same across cells. - 11. Autosum: Select a cell next to the numbers you want to sum, click Σ i.e "AutoSum" button.
- 12. Show formulas: Formulas -> Show Formulas
- 13. Conditional Formatting using new rules:
- 14. Pivot Table and Slicers for aggregating and slicing tabular data
- 15. Pivot Charts for generating charts for corresponding pivot tables
- 16. Charts are useful for data visualisation. Charts can also be stored as templates and reused.
- 17. What-if annalysis and Data Tables
- 18. Goal Seek
- 19. Auditing: Trace decendents, Trance Precedents
- 20.
`F9`Formulas -> Evaluate formula - 21. Excel Solver solves optimisation problems for given Objectives, Variables and Contraints
- 22.
`Ctrl``T`for creating a table

## Formulas

- 1. IF
- 2. Text functions: LEFT, RIGHT, MID, LEN, FIND, VALUE, and CONCATENATE
- 3. Data -> Text to Columns. Use width/delimiters to split text into columns
- 4. Text Concatenation =D4&"@gmail.com" --> xan@gmail.com
- 5.
`ctrl`+`E`: Flash fill - matches a pattern in adjacent cell to extract data. Flash Fill is not foolproof; sometimes it just does not get the pattern. - 6. =TODAY() : Current Date ; =NOW() : Current Date and Time
- 7. Renaming ranges : Formulas -> Create from selection. Formulae can now be written in terms of cell names instead of coordinates like C5. This improves readability. Similar ease of names in formulas is seen when creating tables.
- 8.
`F3`: Using renamed ranges - 9. Formulas ->
**Apply Names**to replace newly created name ranges to formulas. - 10. =VLOOKUP(...) : Fetch value in a table wrt matching value in adjacent column with some offset
- 11. =HLOOKUP(...) searches wrt vertical offset
- 12. =INDEX(...) function can be used to easily extract any entry from a rectangular array of cells; '=INDEX()' function is an array function.
- 13. =MATCH(...) function can be used to locate a text string in a row or column. Returns index of the match.
- 14. =IFERROR(...) error trap
- 15. Rounding functions
- 16. COUNT(): numbers ; COUNTA(): numbers + text ; COUNTBLANK()
- 17. LARGE([range], k): kth largest number. Similarly- SMALL(), RANK()
- 18. VAR(): Variance; STDEV(): Standard deviation
- 19. RANK.EQ(): Ranks a given value wrt an array in asc/desc
- 20. COUNTIFS() and SUMIFS()
- 21. Data -> Remove Duplicates: Remove duplicates / Duplicate combinations in a given range
- 22. Data -> Sort: Sort a given range wrt column value/colors.
- 23. 3D formula: =SUM(Worksheet1:Worksheet2!A2)
- 24. Referencing tables =SUM(Table1[Column1])
- 25. Resolving Circular Dependencies: Excel options -> Formulas -> Enable iterative calculations (Gauss Seidel Iteration)

## Financial Functions [TBD]

## Shortcuts

1.`ctrl`+

`H`: Find and Replace

2.

`ctrl`+

`shift`+

`1`: Number format (2 decimals)

3.

`ctrl`+

`shift`+

`2`: Time format

4.

`ctrl`+

`shift`+

`3`: Date format

## Tips

Using`'`before value will store value as a string in the cell