What I Learned in CompSci #29: Conditional Formatting

Conditional Formatting
Applying a unique cell formatting based on a condition

Rules / Conditions

• Highlight Rules
• Top/Bottom Rules
• Data Bars
• Color Scales
• Icon Sets
• New Rule

Reference: http://moodle.pshs-brc.edu.ph/pluginfile.php/4835/mod_resource/content/0/Conditional%20Formatting_New.pdf

What I Learned in CompSci #28: Logical Operation

Logical Operation

3 Logical Operations

• =AND(logical 1, logical 2,...)
• =OR(logical 1, logical 2,...)
• =NOT(logical)

Truth Table

AND

OR

NOT

IF Function

Returns a value if a condition if TRUE, and another if false

Syntax: =IF(*logical_test*,*value_if_true*,*value_if_false*)

Example

• Single Range : =IF(AND(A1>0,A1<11),“Valid",“Invalid") 
• Nested IF (exact value) : =IF(A1=1,“One", IF(A1=0,“Zero",”Invalid”))
• Nested IF (ranges) : =IF(AND(A1>0,A1<11),“One to Ten", IF(AND(A1>11,A1<21), “Eleven to Twenty",“Invalid"))

Reference: http://moodle.pshs-brc.edu.ph/pluginfile.php/4800/mod_resource/content/0/Logical%20Operation.pdf

http://moodle.pshs-brc.edu.ph/pluginfile.php/4818/mod_resource/content/0/IF.pdf

What I Learned in CompSci #27: Cell Reference

Cell Reference

Cell Ranges
A cell reference can be:

• Single Cell                                             --- A1
• Multiple Cells, separated by comma     --- A2,B3
• Range, Same Column                           --- A3:A9
• Range, Same Row                                --- A4:Z4
• Range, Different Row and Column       --- A5:V7

*DYK? Clicking Ctrl + ` will show the formula of every cell, and clicking it again will return the result of the formulas

Reference Types

Relative - automatically changes when the formula is copied.

Example: =B5+B6

Absolute - the cell reference is fixed and absolute. The cell reference will not change when the formula is copied. Use a dollar($) sign.

Example: =$A$4

Mixed - a mixture of relative and absolute reference types. It has either a relative column but absolute row, or vice-versa.

Example: =$C5 <== Has an absolute column but relative row

Referencing from other sheet

• Same File - manually selecting or using a formula (*name of sheet*!*cell reference*). Example: =Sheet1!A1
• Different File - manually selecting or using a formula ([*name of file]*name of sheet*!*cell reference*  <==Absolute by default. Example: [file.xlsx]sheet1!$A$4

Reference: http://moodle.pshs-brc.edu.ph/pluginfile.php/4780/mod_resource/content/0/Cell%20Reference.pdf

What I Learned in CompSci #26: Errors in Functions & Formulas

Errors

1. #### - the cell contains a number too long for the cell

2. #DIV/0 - the cell contains a formula that divides a value by 0

3. #N/A - there is a missing data or found no match

4. #NAME? - undefined name range or cell name

5. #NULL! - 2 ranges doesn't intersect. This usually happens when the cell references are separated by a space and not by a comma, operator, or colon

6. #NUM! - used an invalid argument to a function

7. #REF! - invalid cell reference

8. #VALUE! - argument for a function is of an incorrect data type 


Reference: http://prajwaldesai.com/wp-content/uploads/2014/01/error-code.jpeg
http://moodle.pshs-brc.edu.ph/pluginfile.php/4746/mod_resource/content/0/Functions%20and%20Formula.pdf

What I Learned in CompSci #25: Functions

Function
     -is a predefined formula in Excel
     -may require arguments as inputs
     -can be mixed with other functions and formulas
     -different function require different numbers of data needed

Example of a function

Functions are pre-built, you just need to stack 'em together

Some Basic Functions
=SUM(A1,Z5)
=PRODUCT(10,B5)
=AVERAGE(B10:C10)
=MAX(A1:Z99)
=NOW()
=IF(A1=A2,B1,C1)


Reference: http://www.changeipadwallpaper.com/wp-content/uploads/1024iPad/3D/Metal%20building%20blocks.jpg
http://moodle.pshs-brc.edu.ph/pluginfile.php/4746/mod_resource/content/0/Functions%20and%20Formula.pdf

What I Learned in CompSci #24: Formula

Formulas
     - used to calculate
     - begins with an = sign
     - is made up of operands and operators
     - may contain constants, cell references, ranges, and grouping symbol

Examples

=15*2        ---   this will result with the cell containing the number 30
=A1+A2     ---   the result will be the sum of the cells' value

List of Operators

Order of Operators

More Examples
="part"&B1     ---and the B1 containing "1", this will result to "part1"
=(1=1)+(1=1)  ---this will result to "2", why? Because (1=1) is equal to true, which is equal to                            1, so 1+1=2


Reference: http://moodle.pshs-brc.edu.ph/pluginfile.php/4746/mod_resource/content/0/Functions%20and%20Formula.pdf

What I Learned in CompSci #23: Data Types

Data Types
     -is a standardized representation for a particular kind of data. It also indicates the potential range of values we can store in them and the operation we could perform with them.

Data Types

• Currency - -922,337,203,685,477.5808 to 922,337,203,685,477.5807 with four decimal digits of fixed precision.
• Date Time - valid dates are all dates after January 1, 1900
• Logical / Boolean - True/False
• Number (Whole Number) - from (-2^63) to (2^63-1)
• Number (Decimal Number) - -1.79E +308 through -2.23E - 308, 0, 2.23E -308 through 1.79E + 308

Reference: http://moodle.pshs-brc.edu.ph/pluginfile.php/4705/mod_resource/content/0/Spreadsheet%20for%20Data%20Analysis.pdf
http://www.jestais.com/wp-content/uploads/2014/05/abstract-binary-data-fresh-hd-wallpaper.jpg

What I Learned in CompSci #22: Tips

Tips in Excel

1. Alt + Enter - Adds a new space

2. Ctrl + ; - Inputs the current date

3. Ctrl + Shift + ; - Inputs the current time

4. Ctrl + Enter - Inputs the same data into multiple cells

5. Check "Advanced Options" - It could save you

6. To have uniform row/column length, select the columns/rows then adjust one of them

7. To add a password, just save, at the save/save as dialogue box click tools, then click 
General Options, fill in, click ok, then ta-da! Your file already has a password, you can also check the backup in case someone gets annoyed by the password and deletes it



Reference: http://travelmanagement.yale.edu/sites/default/files/images/Helpful%20Tips.jpg
http://moodle.pshs-brc.edu.ph/pluginfile.php/4705/mod_resource/content/0/Spreadsheet%20for%20Data%20Analysis.pdf

What I Learned in CompSci #21: Software - Excel

SPREADSHEET
Excel

       There are many good software out there for spreadsheet and data analysis, but what I'm going to blog about is Excel... because it's what I learned from Sir Tom.

Excel - Introduction

• A file of Excel is called a workbook
• A workbook is made up of worksheets
• A worksheet is made up of grid cells
• A cell is where you input data into. Cells are arranged into rows and columns.

Parts of Excel

• Cell - It is where you input data into
• Row / Column Heading - It is found at the topmost or leftmost part. Clicking these will select the entire row or column
• Name Box - Holds a cell's reference number. You can rename a cell by typing a new name here
• Formula Bar - You can type data or formulas here
• Active Cell - The highlighted cell

Reference: http://upload.wikimedia.org/wikipedia/de/thumb/c/c1/Microsoft_Excel_Icon.svg/1065px-Microsoft_Excel_Icon.svg.png
http://moodle.pshs-brc.edu.ph/pluginfile.php/4705/mod_resource/content/0/Spreadsheet%20for%20Data%20Analysis.pdf

3rd Quarter

3rd Quarter Topics

SPREADSHEET

• Software : Excel
• Tips
• Data Types
• Formula
• Function
• Errors in Function
• Cell Reference
• Logical Operation
• Conditional Formatting
• Data Representation : Charts
• MACROS