#11 Spreadsheets Again
date
May 15, 2023
slug
11-again
status
Published
tags
summary
Finished Intro to spreadsheets
time
1h-30m
type
Post
Finally finished the the Intro to spreadsheets course on Datacamp. Learned about references and how it can be used.
- Chapter 1
- Navigate spreadsheets
- Import data
- Perform calculations
- Format cells
- Chapter 2
- Cell references
- Copy references
- Calculations w/ references
- Absolute references
- Every cell in a spreadsheet has an associated data type
- Data types is auto assigned
Numeric
Data Type- Supports arithmetic and statistical operations
- Right aligned by default
- Precision of decimal places can be +/- using the toolbar
Plain Text
Data Type- Assigned by default, if no other is detected
- Change Format>Number>Plan Text
- Also prepend with single quote like
'2
or'=2+3,
the latter will store the formula rather than evaluating it
Date
Data Type- Format > Number > Date
- Right aligned by default
Currency
Data Type- Anything that starts with
$
, or any other currency is auto detected and assigned - It is right aligned by default
Logical
Data Type- or Boolean values :
TRUE
&FALSE
- These are case sensitive, spreadsheet will convert to upper case
- Center aligned by default
- Returned by formulas that include comparison operators.
=
: equals to<>
: not equals to<
/>
: less / greater than<=
/=>
: less / greater than equals to
- Cell References are a neat way to point and retrieve the values from another cell
#REF!
error is thrown when you try to use self-referencing logic or a circular logic- A reference that either directly or by a chain of other references, refers to itself is called a circular reference.
- Formulas created on circular reference can’t be computed
- Copying of referencing cells horizontally or vertically has the same effect as it follows the referenced cell in the same direction.
- So far these have been relative references
- Absolute references don’t change when copied over
$B$7
is represented by$
before row and column, as this one is showing 7th row of B column- It locks and stops the reference shifting in either direction when copied
- Partial absolute references that change in only one direction
$B4
here we are fixing the column index, so it wont change when moving column wise but only when we move row wise
Examples
D2
![notion image](https://www.notion.so/image/https%3A%2F%2Fs3-us-west-2.amazonaws.com%2Fsecure.notion-static.com%2F6413e190-d6a5-4940-bb7d-ecda54cea428%2FUntitled.png?table=block&id=3b627d4a-34f3-47d0-83dc-36973b111ac3&cache=v2)
D3
![notion image](https://www.notion.so/image/https%3A%2F%2Fs3-us-west-2.amazonaws.com%2Fsecure.notion-static.com%2Fd79aaf86-c5fd-4fd0-a121-65650f68e265%2FUntitled.png?table=block&id=498c07d2-6884-4572-8624-10c220737e2f&cache=v2)
E2
![notion image](https://www.notion.so/image/https%3A%2F%2Fs3-us-west-2.amazonaws.com%2Fsecure.notion-static.com%2F822aeceb-a5e0-4f6a-aff6-d49838962d7d%2FUntitled.png?table=block&id=bf25f25f-e2fd-4bc0-a41c-4488de81f5a0&cache=v2)
E3
![notion image](https://www.notion.so/image/https%3A%2F%2Fs3-us-west-2.amazonaws.com%2Fsecure.notion-static.com%2F84455cc0-7637-40f8-9112-470b40761eef%2FUntitled.png?table=block&id=c38a59bc-f1a6-4dba-85af-c54d1b9ab3a6&cache=v2)