What I Learned in CompSci #31: MACROS

MACROS
Is used to repeat a set of action by pressing a set of keys.

Recording a Macro
1. Enable the Developer Tab. You can do this by going to the Advanced Option and checking the Developer Tab

2. Go to the Developer Tab and click Record Macro

3. Fill in the required details then click OK

4. You are now recording the Macro, do what the Macro should do.

5. When finished, click the Stop button at the status bar

6. Done! You just created a Macro


Reference:
http://brainchamber.com/wordpress/wp-content/uploads/2013/07/excel_marco1.png

What I Learned in CompSci #30: Charts

Data Representation:
Charts

Used for easier representation of data.

Types of Charts

• Column
• Line
• Pie
• Bar
• Area

There are others, but I will only post about these....

Column Chart

-usually used for showing data changes over time or comparison among items.

Line Chart

-used to display continuous data over time. Useful for showing trends in data at equal intervals.

Pie Chart

-show the size of items in one data series, proportional to the sum of the items. The data points in a pie chart are displayed as a percentage of the whole pie.

Bar Chart

-just a column chart, but the bars are horizontal. Used when the axis labels are long or when the values that are showing are duration.

Area Chart

-emphasize the magnitude of change over time, and can be used to draw attention to the total value across a trend. It is also recommended to use the 3D Area Chart because in 2D Area charts, the data at the back may be hidden.



Reference: http://moodle.pshs-brc.edu.ph/pluginfile.php/4913/mod_resource/content/0/Charts.pdf

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