#17 Good Day

date

May 21, 2023

slug

17-good

status

Published

Intermediate Spreadsheets

Errors

Blank vs Missing cells

Blank cells are empty ones that are yet to be used
Missing cells are ones that have value #N/A

dealing with missing and blank values

Adding numbers to blank cells would let the calculation pass with blank cell value defaulting to zero.
#N/A is a missing value on which calculations can’t be performed.
IF(ISBLANK(cell,NA(),cell) will convert the cell to #N/A if it is blank
Using ISERROR() on a blank cell would result in false while on a cell with #N/A would result in True. ISERR() would fail for both blank and ones with #N/A

Common Types of Errors

#DIV/0! : Dividing by zero
#VALUE! : Nonsense data in calculation?
#REF! : Reference error
#NAME? : forgetting to quote a string
#NUM! : numbers out of range
#ERROR! : syntax issue in formula

Interesting observation

=1+"" would result in 1 without any errors
=1+" " would result in =VALUE! error
Empty string "" is treated as null string / empty cell and any arithmetic operation involving those would would treat it as as having value of zero. This makes it equivalent to =1+0 hence would result in 1.
The space b/w quotation makes it a string value. When performing arithmetic operations, the string is converted into a number. " " doesn’t have a valid conversion and fails with a #VALUE! error

Cell Positions

ROW(C5) → 5 & COLUMN(C5) = 3 & ADDRESS(5,3) → $C$5 that’s an absolute address

Using INDIRECT(cell) takes an address of cell and returns the value it has

OFFSET(origin, down, right, [height, widht]) specifies a position relative another cell by moving one value down and right. Height and width tell to return a range.

INDEX(absolute_range,relative_row,relative_col) can be used when we need to specify a relative to a range. This throws an error if the values go out of range. Numbers start at 1.

ADDRESS(row_no,col_no,rel_abs_mode)

ADDRESS(3,2,1) → $B$3
ADDRESS(3,2,2) → B$3
ADDRESS(3,2,3) → $B3
ADDRESS(3,2,4) → B3

Positional Matching

VLOOKUP() is similar to left join on a dataset

MATCH(100,A2:D10,1) on a value would return value that is less than or equal to 100.

Pivot Tables

Pivot tables can be created from any data in row column format. The basic features allow to filter, sort, subtotal and analyze it. Pivot tables can be structured to have one row label, column label and one value

Create a pivot table

Auto : Select any cell. Insert > Pivot table.
Manual : Select all data range (including headers). Insert > Pivot table.

After creating a pivot table screen shows

Editor on right side
Blank Shell on the left

Pivot table doesn’t change any values of the data

Add or remove rows, columns and values by clicking on the edit icon
By default pivot table tries to use sum or count by default.

To change data

No action required when original data is modified
Need reset the range in source if new values are added to the data

Filters can be used using conditions or manually selecting too.

To string match, clear everything, search for string and select all.

COUNT() will only consider numeric values to consider text values we need COUNTA(). There is also COUNTUNIQUE()

Calculated field as don’t actually exist in the actual data but are calculated using it. Add by using Value → Calculated field

Drilling down is the process of pulling the full details for a specific selection of data.

Double click on the value in the pivot table cell and it will open up a new sheet

Grouping can be achieved by right clicking on any value and create grouping rule. Option that is found outside of the pivot table editor

Common issues and Errors

Finding missing data

Use COUNTA() across all columns and see mismatches
To go to the blank cell, first go the the header then jump to next break by cntrl+down

Set formatting

Select a range. Format > Select appropriate format

Best Practices

Rows & Columns should have Use Descriptive fields names. Use to sort and organize data
Values are measurements that can be used for mathematical calculation

When not to use pivot table

Small dataset : use sort, subtotal and filter
Strict Formatting : precise formatting is not available with pivot tables

Data Visualization in Spreadsheets

Spreadsheets are used heavily for Business Intelligence. BI is strategies, methods and technologies business use to produce meaningful info that can be used to enhance decision making.

BI can be used for

Decision making

Measuring goals

Operational efficiency

Optimizing process

Pinpointing opportunities

Different types of charts

A smart Dashboard will only show information that is needed. Start with outcome and work backwards. Best practice is to keep the dataset and dashboard in separate sheets and selectively pull the data.

Formula of reference =sheet2!A1

Formatting the data

Bold or italicize the headings

Select the right data type

Consistent decimal places

For colors less is more. Use greys, white on black

Common Terminology

Table : related data stored in columns and rows

List : manage and analyze smaller set(s) of data

Selecting discontinuous ranges by holding down cntrl and selecting them.

Double click on a chart to edit it quickly.