IP 244 Excel

0.0(0)
Studied by 0 people
call kaiCall Kai
learnLearn
examPractice Test
spaced repetitionSpaced Repetition
heart puzzleMatch
flashcardsFlashcards
GameKnowt Play
Card Sorting

1/30

encourage image

There's no tags or description

Looks like no tags are added yet.

31 Terms

1
New cards

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)


2
New cards

Number of cells in array

  • ROWS() * COLUMNS()


3
New cards

Spilled arrays & other functions

  • Sometimes spilled array used in other formula is source of error.


4
New cards

COUNT & COUNTA

  • COUNT counts number of cells that have numbers

  • ACOUNT counts number of cells not empty


5
New cards

SUMIFS & COUNTIFS

  • SUMIFS has weird format so pay attention.

  • COUNTIFS is more intuitive


6
New cards

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.


7
New cards

Lookup function search modes

  • V&HLOOKUP: 0 - exact, 1 - approx smaller (default)

  • XLOOKUP: 0 - exact (default), -1 - approx smaller, 1 - approx larger


8
New cards

Time & spilled arrays

  • NOW() doesn’t work on spilled array


9
New cards

Match function search modes

  • MATCH: 0 - exact, 1 - approx smaller (default)

  • XMATCH: 0 - exact (default), -1 - approx smaller, 1 - approx larger


10
New cards

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))


11
New cards

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


12
New cards

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


13
New cards

Adding text in cell with formula

  • Use FUNC()&”text”&FUNC()&”text”


14
New cards

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.


15
New cards

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


16
New cards

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).


17
New cards

TEXTAFTER() & TEXTBEFORE()

  • Gets text after or before specified delimiter.

  • Use TEXTAFTER(TEXTBEFORE(); “ “) to extract text within specified delimiter boundary.


18
New cards

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.


19
New cards

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.


20
New cards

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.


21
New cards

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.


22
New cards

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.


23
New cards

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…


24
New cards

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.


25
New cards

Graphs

  • Select appropriate graph

  • Add data (don’t include category names)

  • Press on [+] button to add legend, axis titles, ect.


26
New cards

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.


27
New cards

Trapezium rule integration

  • Same as RR but slightly different formula

  • More columns needed


28
New cards

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.


29
New cards

Goal seek

  • Intuitive as hell

  • Data → What-if analysis


30
New cards

Root finding optimization

  • Newton-Raphson method eqs

  • Needs good initial x0 guess.

  • Repeat for accuracy


31
New cards

Simultaneous equations

  • Use Ax = b → x = A-1b (matrices)

  • MINVERSE() and MMULT() most important functions