1/30
Looks like no tags are added yet.
Computer default syntax
Some default symbols used might be different. Make sure you use the default symbols of the device you are working on.
; or , (delimiter in functions)
, or . (decimal in numbers)
Number of cells in array
ROWS() * COLUMNS()
Spilled arrays & other functions
Sometimes spilled array used in other formula is source of error.
COUNT & COUNTA
COUNT counts number of cells that have numbers
ACOUNT counts number of cells not empty
SUMIFS & COUNTIFS
SUMIFS has weird format so pay attention.
COUNTIFS is more intuitive
Array method of conditional SUM
Use IF function on array to give 1 or 0 if condition met, then multiply by values in array, giving value in relative position of array if true & 0 if false. SUM new array.
Lookup function search modes
V&HLOOKUP: 0 - exact, 1 - approx smaller (default)
XLOOKUP: 0 - exact (default), -1 - approx smaller, 1 - approx larger
Time & spilled arrays
NOW() doesn’t work on spilled array
Match function search modes
MATCH: 0 - exact, 1 - approx smaller (default)
XMATCH: 0 - exact (default), -1 - approx smaller, 1 - approx larger
Index & Match
Use match in index with row-row or column-column arrays eg. INDEX(C2:H2;MATCH(113;C13:H13;0))
For value in 2D array from 2 given values: eg. INDEX(C3:H15;MATCH(90;B3:B15;0);MATCH(84;C2:H2;0))
Conditions
Single condition: eg. > 295 or even = G; write as “>295” & “=G”
Closed condition: eg. >295 & <300; use function with multiple conditions (“>295”;”<300”)
Open condition: eg. <295 & >300; sum single condition functions
Text extraction functions
LEFT, RIGHT, MID self-explanatory
SEARCH finds char_num at which word or character starts/is in a text. FIND is same but case sensitive.
TRIM removes spaces on sides, but not in between words of a text
Adding text in cell with formula
Use FUNC()&”text”&FUNC()&”text”
FILTER
(array; include; if_empty)
array: data to be extracted, include: filter condition, if_empty: value if empty
array and include usually two different columns of data.
For AND use *, for OR use + inside include section. With brackets you can code complex conditions.
Graphs & charts
Select data, then insert graph/chart
+ button for axis titles, legend, ect.
right-click to edit data. usually not necessary if data selection was done correctly
Note: skewed to right means peak is to left, vice-versa
Pivot table
Insert Pivot table then select range of data.
NB: Definite filters (eg. January, or 3) you put into “Filters”, Indefinite filters (eg. <=50) you put into “Values” to filted via dropdown & “Value Filters”.
Sort by clicking dropdown on “Row labels” & then “More sort options”. Values and rows can be sorted.
Names (independant) in “Rows” & numbers (dependant) in “Values”. Select correct calculation for field in “Values”. Never use “Columns”.
Use MAX option as way to get raw data into “Values”. For one-to-one data.
For new data (might be needed for filter) that can’t be made in pivot table, make new column in data & then include in new pivot table (replace old one).
TEXTAFTER() & TEXTBEFORE()
Gets text after or before specified delimiter.
Use TEXTAFTER(TEXTBEFORE(); “ “) to extract text within specified delimiter boundary.
Cheat sheet
Read formulas carefully.
Some tips are listed.
Formulas or tips with zig-zag lines on either side means they aren’t listed in slides.
TDLR gives basic method.
Forecast error metrics
Calculate individual et & et2 values as columns first.
MAD (MAE) & MSE both take sum of et with either abs or sqaure in sqaure root as the method for solving negative et values.
MAPE includes standard deviation for datasets of different scale.
Both are single value (output is in only one cell).
Abs can be applied to et first before being used in rest of eqs, so that abs in equation can be ignored.
Moving averages
SMA has time period n, which is fetched from behind position of SMA in excel. (Yes divide by t)
WMA has time period n which is fetched from behind same as SMA, and also weightings wi. Absolute reference wi cells.
Increasing i in wi means increasingly older data. eg. w1→C4, w2→C3, w3→C2
No dividing by t, only sum of weightings times entries.
Only apply moving average on cell where its range (data) has no empty cells. You’ll be able to tell because it should be an extreme outlier compared to other moving average values.
Exponential smoothing
Uses yt-1 and Lt-1 to calculate Lt forecast.
Start on second data entry. Lt-1 doesn’t exist, so set its cell equal to specified value in question, or if not given, first data entry.
ŷt = Lt so no seperate calculation necessary.
Absolute reference α cell.
Holt’s method
Start by calculating Lt & Tt not including values of data to be forecast (ŷt).
Calculate forecast cells with Ft+h|t equation using the previous Lt & Tt values (absolute reference) and respective h values given.
Use ŷt eq to calculate values for non-forecast cells, which will be used in et eq.
Absolute reference α and β cells.
Holt-Winter’s method
Very similar to Holt’s method, but just more variables & different equations.
Ft+h|t equation is a bit more complicated:
Same as Holt’s, but st+h-c is NOT absolute reference. It moves down with each cell & needs to be ‘reset’ to original reference every ‘cycle’ of seasonal factor values (when seasonal factor values run out).
Initialisation is tricky…
Solver
Options → Add-ins → Excel add-ins → Go → Select Solver (one time step)
Data tab → Solver in analyze subsection
Intuitive. Select target cell(s), goal (min,max,specific), then select cell to optimise.
Constraints: standard syntax, include contraints eg. 0 < α < 1 → J2<=1 and J2>=0
✓ Make constrained variables non-negative
GRG Nonlinear
Copy optimised value into new cell with a label next to it & put original value back into original cell.
Graphs
Select appropriate graph
Add data (don’t include category names)
Press on [+] button to add legend, axis titles, ect.
Rectangle rule integration
Left formula default, unless specified.
Sum of individual rectangle areas.
Plug in values & calc
Column for each step for easier final calc.
Trapezium rule integration
Same as RR but slightly different formula
More columns needed
Simpson’s rule integration
Follow formula.
Each yi has own weighting:
First y is 1, then next ys alternate 4, 2, 4, 2, … then last two y are 4, then 1.
Needs even number of intervals.
Goal seek
Intuitive as hell
Data → What-if analysis
Root finding optimization
Newton-Raphson method eqs
Needs good initial x0 guess.
Repeat for accuracy
Simultaneous equations
Use Ax = b → x = A-1b (matrices)
MINVERSE() and MMULT() most important functions