5. Excel

Ryan has a strong knowledge of Excel such as IF statement, V-Lookup, and Pivot Table. He also has some background knowledge of using Macro and VBA.

1. IF Statement

• To assign the status of the performance, Ryan has deployed IF statement to the score:
• =IF(C3>70,"Good",IF(C3>50,"Not Bad","Bad"))

2. V-Lookup

• To assign the grade of each individual, Ryan has deployed V-Lookup:
• =VLOOKUP(H2,B2:E10,4,FALSE)

3.Pivot Table

• Pivot table is deployed to summarize or sort data, and to perform aggregate functions
• It also allows users to transform columns into rows and rows into columns
• Filtering and grouping of data fields

4. Visual Basic for Applications (VBA) / Macro

Under this section, Ryan had used VBA / Macro to transfer the data into new columns and rows. In addition, he had extracted only the date from the raw data, calculated the profit (Sales - Expenses), calculated the total profit, as well as aligning the new data to left. Kindly refer to below for the raw data:

Kindly refer to below for the new dataset generated through VBA / Macro:

Kindly refer to below for the VBA coding / screenshots:

Sub Header()

' Header Macro

Range("I2").Select

ActiveCell.FormulaR1C1 = "Date"

Range("J2").Select

Application.CutCopyMode = False

ActiveCell.FormulaR1C1 = "=RC[-7]"

Range("K2").Select

Application.CutCopyMode = False

ActiveCell.FormulaR1C1 = "=RC[-7]"

Range("L2").Select

Application.CutCopyMode = False

ActiveCell.FormulaR1C1 = "=RC[-7]"

Range("M2").Select

Application.CutCopyMode = False

ActiveCell.FormulaR1C1 = "=RC[-7]"

Range("N2").Select

ActiveCell.FormulaR1C1 = "Profit"

Range("I2:N2").Select

Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Selection.Font.Bold = True

'Content Macro

Range("B3:F1002").Select

Selection.Copy

Range("I3").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Range("I3:I22").Select

Application.CutCopyMode = False

Range("I3:I1002").Select

Selection.NumberFormat = "m/d/yy"

'Calculation of Profit

Range("N3").Select

Application.CutCopyMode = False

ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-1]"

Selection.AutoFill Destination:=Range("N3:N22")

'Selects the F2 cells of the active sheet

Range("N3").Select

'Select the last cell in the column and calculate total profit

Selection.End(xlDown).Select

lastCell = ActiveCell.Address(False, False)

ActiveCell.Offset(1, 0).Select

ActiveCell.Value = "=SUM(N3:" & lastCell & ")"

'DollarSign Macro

Range("N3:N23").Select

Selection.Style = "Currency"

'Align to left

Columns("I:N").Select

With Selection

.HorizontalAlignment = xlLeft

.VerticalAlignment = xlBottom

.WrapText = False

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = False

End With

End Sub