User:Muffingg/Excel

Course Overview

To be submitted by Friday evening
Average: 90%
Does have VBA in it
Big table with data from different tables
- Algorithm based on which rule to separate people from different companies into different tables containing certain company's people
All formulas need to be structured
- Otherwise we would lose 10% of the marks

Modellers and Forecasters often use a separate worksheet for input values (Inflation Rates etc)
List of all created cell and table names can be viewed in Formulas > Defined Names > Name Manager

CTRL + D to copy a formula down (D = down)
CTRL + R to copy a formula across (R = right)
Dynamic referencing is the one that changes as you drag a formula
Absolute referencing remains the same (with % sign)
Structured referencing works in tables
Ways in which to apply a formula to all cells:
- CTRL + D and CTRL + R
- Drag the bottom right corner
- Select all cells, enter the formula and CTRL + ENTER
Across sheets references include ! after the sheet name
- Never need to manually write these out, but simply opening the sheet and clicking the cells should do

Structured referencing is the name of the referencing in tables
Table Name Rules
- It can't have special characters or spaces
- Have some sort of convention, e.g. start with 'Tbl'
Using the Name field at the top left of the workbook, doesn't rename the table
If a column contains a space, the reference needs to have another square bracket around
- e.g. [@[Hours Worked]]
- Not having a @ deals with them as an array, so in some cases it will still give the same results
Absolute structured referencing:
- [@[Hours Worked]:[Hours Worked]]*[Rate]
- Hours worked does not shift, Rate does
Naming a cell can be helpful when a column or row uses a multiplier for example
- It does not need to be absolute when this is used

Functions return one value
- Some return an array of values, but that is an exception
Accept multiple inputs
In the input box, anything that doesn't have a square bracket ( [] ) around it, is a required input arguments
- Squared brackets are optional arguments
'...' at the end means that infinite number of arguments can be entered