Excel #1 Flashcards
(139 cards)
1
Q
F2?
A
Activates the editing mode for an active cell.
Brings back blue moving box showing range in question.
2
Q
CTL + 1
A
Formatting options.
3
Q
F7
A
Spell check
*Note use of CTL = SHF = Down!
4
Q
Activate PIN listing
A
Go to file
5
Q
ALT + Q
A
Open search bar.
6
Q
CTL + F1
A
Minimise and maximise ribbon.
7
Q
Right-click?
A
Mini Toolbar + 19 options
8
Q
Movements about a table of data?
A
Tab, Enter, Shift Tab (Back). Shift Enter (Up).
9
Q
Box and move about parameters?
A
Mouse square, CTL + Enter on first cell, Move about square.
10
Q
F1?
A
Activates Help panel.
11
Q
File?
A
Backstage, Admin area.
12
Q
The seven manual controls?
A
CTL + Up/Down/Left/Right arrows, CTL+ SHIFT Down/Right, CTL + A, CTL + Enter.
13
Q
Non-contagious cell selection?
A
SHIFT + Arrow, SHIFT + F8, SHIFT + Enter, SHIFT + F8….
14
Q
Name?
A
Name of formula or named range incorrect / absent.
15
Q
Ref?
A
Refer to cells that no longer exist.
16
Q
Div?
A
Trying to divide the number by zero.
17
Q
Absolute reference?
A
F4 while active cursor in cell, Alternating.
18
Q
Auto-sum?
A
ALT + =
19
Q
Series creation, row or column?
A
Right=click, One, One cell down, One cell back up, Right-click, Fill series.
20
Q
Flash-Fill criteria?
A
Adjacent, CTL + Enter to stay in top cell, CTL + E to flash fill down.
21
Q
Paste individual table components?
A
F3
22
Q
Go to table components?
A
CTL + G
23
Q
Format painter rules?
A
Click once for one use, Click twice for continual use until switched off.
24
Q
Add manual break in cell (Partick Thistle)?
A
Place cursor, ALT + Enter
25
UNIQUE?
Single list of unique items from longer list of repeating items (Dynamic array).
26
Insert Filter?
CTL + Shift + L
27
Table creation?
CTL + T
28
Start and End of worksheet?
CTL + Home, CTL + End.
29
Save as?
CTL + S, F12.
30
Move between worksheets?
CTL + Page up. CTL + Page down.
31
Delete worksheets?
CTL + Left-click to select pages, Right-click and select delete to remove.
32
Double-click in cell?
Edits, Reveals formula.
33
Fractions?
Space needed between number and fraction to work. 1 1/4 = 1.25 in cell.
34
Automatically generate sequential?
CTL + Autofill (Drag down) every line.
35
Zoom?
CTL + Mouse wheel.
36
Add a column?
CTL + SHIFT + Plus, CTL + Minus.
37
Fill down (List?)
CTL + D.
38
Fill across (List?)
CTL + R
39
Insert cell comment?
SHIFT + F2.
40
Same increase in size drag of box?
CTL + Drag.
41
Round function? (Penny-rounding)?
=ROUND(E2 - (F2 + 1), 2)
42
Hiding values in a cell while others remain visible?
Formatting - 3 x ; ; ;
43
Copy Worksheet?
CTL + Drag worksheet to copy.
44
Move Worksheet?
Drag worksheet to move.
45
What do pivot tables allow?
(1) Quickly summarise large amounts of data. (2) Pivot data about. (3) Key Metrics, Trends, Issues, Successes and Failures.
46
Importance of cleaning data?
Data does not always import as expected. Non-clean data = Non-accurate data!
47
Removing blank rows (Cleaning data #1)
Home > Editing > Find and Select > Go to special > Select blanks > Delete all.
48
Removing duplicate rows (Cleaning data #2)
Click somewhere within the data set. Data > Data Tools > Remove duplicates. Note - Only if an exact duplicate.
49
Clearing formatting (Cleaning data #3)
Home > Editing > Clear. Formats, Contents, Comments, Notes and Hyperlinks.
50
Applying number formatting to pivot tables (Cleaning data #4)
Column by column when it comes to pivot tables. CTL + 1. Select from number tab. Use main ribbon also.
51
Changing the case (Cleaning data #5)
=UPPER(A1). =LOWER(A1). =PROPER(A1). Use HELPER COLUMN + Paste special to (replace).
52
Removing Non-Printing Characters and Spaces (Cleaning data #6)
=CLEAN(A1) = Non-printing characters. =TRIM(A1) = Spaces May have non-printing characters / blanks you can't see.
53
Merging and Splitting columns (Cleaning data #7)
(1) Flash Fill. (2)
=CONCAT(John," ",Walsh)
54
Convert numbers stored as text (Cleaning data #8)
Green triangles, Select all, Highlight all, Convert to number.
55
Finding and replacing text (Cleaning data #9)
(1) CTL + F = Find and Replace. (2) Home > Editing > Find and Select > Replace.
56
Spell Check (Cleaning data #10)
F7. Should do every time as pivot tables will display as separate items. Review > Proofing > Spelling also.
57
Benefits of putting data into table prior to pivot table?
(1) Auto-expands. (2) Table Design ribbon and options. [Pivot table analyse also]. (3) Filters and sort functions applied
58
What should every table have?
A name!
59
4 option panes on a pivot table?
Filters, Rows, Columns, Values.
60
Locking the pivot layout?
Pivot Table Analyse Ribbon > Pivot Table > Options = [Printing] [Data] [Alt Text] [Layout & Format] [Totals & Filters] [ Display].
61
[Pivot Table Analyse]
Pivot Table/Options, Active Field, Group, Filter (Slicer/Timeline), Data, Actions, Calculations, Tools. Show - Field List / +/- buttons / Field Headers.
62
[Pivot Table Design]
Layout, [Subtotals/Grandtotals/Report Layout/Blank Rows] Style Options, [Row Headers/Column Headers/Banded Rows/Banded Columns], Pivot Table Styles.
63
Methods of Aggregation (Pivot Tables)
[Value Field Settings] - Right click or down arrow in miniature.
64
Grouping and Ungrouping Data (Pivot Table)
Pivot Table Analyse > Group > Grouping options.
65
Handling Cells with No Data (Pivot Table)
Pivot Table Analyse > Pivot Table > Options > Layout - Format > For error values show > For empty cells show.
66
Sub Totals and Grand Totals (Pivot Table)
Design Tab > Subtotals & Grand Totals
67
Choosing a Report Layout (Pivot Table)
Design Tab > Report Layout - (1) Compact (2) Outline (3) Tabular
68
Modify Pivot Table Styles (Pivot Table)
Cannot modify pivot table styles. But CAN DUPLICATE!
69
Sorting Pivot Table Data (Pivot Table)
All underneath drop down arrows -
Benefits of sorting
(1) Organises our data in logical order. (2) Increased readability. (3) Analyses the data
ORGANISES / READABILITY / ANALYSES
70
Filtering Pivot Table Data (Pivot Table)
Again, all underneath drop down arrows. Series of boxes.
71
Dynamic (Today's date)
=TODAY( )
72
Dynamic (Today's date and time)
=NOW( )
73
Fixed (Today's date)
= CTL + ;
74
Connecting Slicers & Timelines (Pivot Tables)
Pivot Table Ribbon > Slicer > Report Connections - Use names of charts > Tick boxes > Connected!
75
Hyperlink?
CTL + K
76
Data Validation?
Data > Data Tools > Data Validation
77
Protecting a worksheet?
(1) By default all cells are locked. (2) Select cells & CTL +1 to unlock (2) Review > Protect Group > Protect Workbook. *Summary - We need to select the cells we want to allow users to type into and unlock them prior to protecting the sheet.
78
[2] You can save workbook in different formats!
You can save workbook in different formats!
79
[3] You can share the workbook!
You can share the workbook!
80
[1] Inspect Workbook?
File > Options > Inspect workbook
81
Designing better spreadsheets? (3+3)
(1) Adopt a standard and implement it. (Colours, Fonts & File names). (2) Identify your audience. (3) Include welcome sheets with instructions and key. (4) Separate your data (Source data/Calculation/Analysis should all have separate worksheets). (5) Design for longevity (Use tables / Don't hardcode into cells.) (6) Use consistent and clear structure. (Colour code / Data validation / Protection).
82
Basic Logical Operators
=, >, <, >=, <=, <>
83
Basic Logical Statement IF
(1) =IF(B5>=$H$13,"Approval","Ok") (2) Use of IF allows us to attribute more meaning to a logical statement at its most basic level.
84
Basic Logical Statement AND
(1) =IF(AND(B23>=$H$22, C23 >=H23),"Pass","Fail") (2) Needs to pass test 1 and test 2 to pass.
85
Basic Logical Statement OR
=IF(OR(B32>=SHS31,C32>=$H$32),"Pass","Fail")
86
2 Points on Logical Formulas / IF Formula
(1) IF allows us to attribute more meaning to results by wrapping """" in an F statement.
(2) Text in formulas must always be in quote marks .
87
IFS Function
(1) Same as Nested IFS (IF statements inside other IF statements] but a much more efficient construction. (2) =IFS(G4=$J$5,$K$5,G4=$J$5,$K$6, ..... ) If G4 = 1, Bonus will be £100.
88
Conditional IFS / Multiple criteria (SUMIFS, COUNTIFS, AVERAGEIFS)
=SUMIFS(Sum Range, Criteria Range #1, Criteria Range #2), £, Company Name, Microworld.
Going one step back - SUMIF (Range, Criteria, Sum_range)
A10:A1207, BS A25, AF10:AF1207 When you find 2040 within A10:A1207 (List of codes) return sum value in AF10:AF1207
Moving to one side,
VLOOKUP will return a single value based on a defined criteria
SUMIF will SUM all values that meet a certain, defined criteria
89
Error Handling with IFNA and IFERROR.
Ex. =IFNA(Formula,"No Bonus") IFERRORS Ex. =IFERROR(Formula,0) + Drag down
90
Insert?
(1) Pictures (2) Shapes and textboxes (3) Icons and 3d models (4) SmartArt (5) Screenshots
91
Conditional Formatting
Additional functions in Excel if you are a business analyst. #1
Conditional formatting.
(1) Highlight cell rules (2) Data bars (Left to right) (3) Colour scales (4) Icons sets.
If applying rule to a whole column, use format option box and select option to apply same rule/format all the way down.
If applying to a whole series of columns,
[1] Highlight all data - CTL + SHF + Down.
[2] New rule
[3] Formula to determine which cells to format
[4] =$B5 (First cell top corner) > 25,000,000.
* $B5 allows this to work
92
Sorting
(1) Single list sorting (2) Multi-level sorting (3) Sort using a custom list.
93
VLOOKUP
(1) Lookup Value (2) Array (3) Column index number (4) True - Approx. (5) False - Exact.
Look up this, here in this column, and return value in second column (across from it).
Here, the word 'value' is key!
94
Ranges vs Tables?
(A) Ranges allow non-contagious cells (B) (1) Tables do not but auto-expand, (2) have ++ Function & Design capabilities and (3) Sort and Filter functions.
AUTO-EXPANDS / DESIGN / FILTER
95
XLOOKUP
First 3 mandatory, Second 3 optional. (1) Lookup value (2) Lookup array (3) Return Array (4) If not found "Not found" (5) Type of match (6) Search mode chosen. Note - If using table names in the land of square brackets.
96
OFFSET function?
Used on its own but often with others (SUM & COUNTA) for powerful results. Brings back the call or range following 'directions' there. =SUM(OFFSET(B3, COUNTA(B4:B15),0,-6,1). Ex. Sum of the last 6 months of sales.
97
Data Sort (Advanced Sorting & Filtering)
4-square sort, LEVELS. Up / Down, Cell colour, Font colour.
98
Sort by Custom List (Advanced Sorting & Filtering)
Type out custom list, File >, Use custom list > Option to sort by custom list.
99
SORT & SORTBY (Advanced Sorting & Filtering) 102, 117, 126
Dynamic arrays formula - Use it (1) so we can sort and paste anywhere in spreadsheet and (2) Use it with other formulas (Unique and extract).
SORT - Formula to sort list.
SORTBY - Sort by multiple columns / Sort a list on a column not included in sort array. Note - Use Tables for dynamic update.
The difference between SORT and SORTBY - is that instead of sorting by ascending or descending order we can (1) choose several ranges or arrays to sort by instead. (2) Sort by an array or range outside the selected data.
Using SORT. Sort Index refers to column number.
Dynamic update if using formula. East Wing. If addition at end of list, use table.
Note that SORT is still an ascending and descending selection. Columns have to be adjacent.
Using SORTBY. Sorting by multiple columns. Array 1 (2nd option) is a whole column selection.
100
Advanced Filter ( Advanced Sorting & Filtering )
List out the criteria you require (in format). Copy to new worksheet. ex. Asia / 77,000.
101
1E+40
Roman, Value too large! Mystery solved.
102
#N/A?
Data not available.
103
#NUM?
Contains invalid numeric values.
104
#VALUE?
Something wrong with formula you have typed (or) Something wrong with cells you are referencing.
105
Data Validation (Main)
I Data Validation give user CONTROL over what goes INTO worksheets. II Use of UNIQUE to generate single use names for data validation. III Use of TABLES. To add new values automatically CREATE TABLE.
106
Data Validation (Other Uses)
1. Whole Number (Days of week) 2. Date (Over 18?) 3. Text Length (Airport code).
Uses validation box and drop down WITHIN but uses WHOLE NUMBER OPTION, DATES OPTION or TEXT LENGTH OPTION.
Addition option also includes both INPUT MESSAGE and ERROR MESSAGE.
107
Dynamic Array. Advantages?
(1) Fewer formulas (2) Formulas much easier to write (3) Less errors (4) = More trustworthy spreadsheets (5) Solved some hard problems in Excel.
108
Dynamic Array. What is it?
Enter one formula and get multiple results!
109
Dynamic Arrays. 6 introductions in 2018?
(1) SEQUENCE - Generate an array of sequential numbers. (2) RANDARRAY/RANDBETWEEN - Generate an array of random numbers (3) UNIQUE - Extract unique values from a list or range (4) SORT - Sort range by column (5) SORTBY - Sort range by several columns or Sort by column outside array. (6) FILTER - Filter data and return matching records.
110
Using wildcards - The asterisk and The question mark
* The asterisk means that any number of wildcard letters can occur between the letters. ex. C*g finds Containing
? The question mark means that only one wildcard letter can occur between the letters. ex. S?d finds Sid
111
Quick Analysis.
Quick analysis provides a simple way to do simple things.
(1) CTL + Q
(2) CTL + A on range / Table = Quick analysis in corner.
(1) Conditional Formatting (2) Charts (3) Totals (4) Tables (5) Sparklines
112
Convert a table back into a range
(1) Click anywhere inside a table (2) Click Table Tool > Design > Tools > Convert to Range.
Also - Right click > Table > Convert to range.
113
Standard Formula, CSE ARRAY, Dynamic array
CSE (CTL + SFT + Enter) allows (1) us to combine multiple calculations in one formula and (2) Uses { } brackets to tell us. { Ex =Sum(B11:B16*C11:C16) }
114
RANDARRAY
=RANDARRAY(4,4,5,1000,TRUE/FALSE) = Generate random numbers 4x4 = 16 which are between 5 and 1,000 which are wither whole numbers (integers) or decimals.
Ex. Jump Order from list of names, Copy and paste special, sort values smallest to largest = Jump Order!
115
RANDBETWEEN
=RANDBETWEEN(25000,5000) = Generate random listing of salaries. Random salaries for people.
=RANDBETWEEN(DATE(2005,01,01),DATE(2021,12,31)) = Generate random dates between two dates. Random dates for people's start dates.
116
XLOOKUP (a dynamic array formula) over INDEX and MATCH
More powerful, more flexible plus the notation is a lot easier.
117
2-way look up. Look up information using 2 pieces of criteria as opposed to 1.
Months along top, Companies to side.
1. Create Table - CTL + T
2. Data Validation on both above using Transpose for Horizontal.
3. Named Ranges x 3. These are - Months, Travel companies, Data.
=INDEX(Data,MATCH(B13,Months,O),MATCH(A14,
Companies,0))
See INDEX / MATCH / MATCH
=XLOOKUP(B13,Months,XLOOKUP(A14,Companies,Data,"Not found"0,1))
See XLOOKUP, XLOOKUP
118
CHOOSE
=CHOOSE(A2,"Red","Blue","Red") - Where A2 is either 1,2 or 3! In place of a VLOOKUP.
The (F3) 2 here is a cell reference where you enter 1 to pick out first item, 2 to pick out second item and 3 to pick out third item.
VLOOKUP can also be used but if table disappears then formula and working does also!
Employee - Code - Department (?) Table Codes & Departments.
With CHOOSE we are specifying all of our arguments within one function and we are not referring to external table or external data. Therefore no issue if table deleted while using look up function.
Also just want results, not tables of data.
For CHOOSE ( ) you need to specify an index number to evaluate, which can only be a whole number (1, 2, 3 etc.) whereas for SWITCH ( ) you can specify an expression to evaluate " "
119
SWITCH
=SWITCH(F3,(9,"Excellent",8,"Very good",7,"Good")
The F3 here is a cell reference. Used in place of a lookup function. XLOOKUP.
With SWITCH we define our values within the formula. Therefore no issue if table deleted while using look up function.
Also just want results not tables of data.
Movies - Rating (?) - IMDB score Table has ratings and score.
For CHOOSE ( ) you need to specify an index number to evaluate, which can only be a whole number (1, 2, 3 etc.) whereas for SWITCH ( ) you can specify an expression to evaluate " "
120
Forecasting. Standard - Detail.
1. Create forecasts 2. See trends 3. Predict the future - Always relies on time based data.
X= Dates, Y = Values.
Line chart helpful as allows you to visually see what type of date you have and to select the appropriate function.
Ex. FORECAST.LINEAR (Date starts for forecasted data, X known (Values of data to date), Y known (Dates to date)
Drag down value. Insert Line Chart. Title. Format.
Ex. FORECAST.ETS (Date starts for forecasted data, X known (Values of data to date), Y known (Dates to date) + 3 more automatically, including seasonality.
Ex. FORECAST.ETS.CONFINT ((Date starts for forecasted data, X known (Values of data to date), Y known (Dates to date) + 4 more automatically, including confidence level (95%) and seasonality. Take value and add subtract and then place on line chart (Non-contagious collation).
121
Forecasting. Express option - Forecast Sheets!
Data > Forecast Sheet.
Essentially, a set-up wizard.
Preview offered immediately.
Creates formulas automatically.
122
Add in - FRED
Use of add-in to generate data to work with (UK option)
123
SORT Function (Dynamic array) 102, 117, 127
Use it (1) so we can sort and paste anywhere in spreadsheet and (2) Use it with other formulas (Unique and extract).
=SORT(array, [Sort Index], [Sort Order], [By Row/Column])
Sort by country (ascending) and bib number (descending)
=SORT(A4:A15,{2,3},{1,-1},False)
Using SORT. Sort Index refers to column number.
Dynamic update if using formula. East Wing. If addition at end of list, use table.
Note that SORT is still an ascending and descending selection. Columns have to be adjacent.
Using SORTBY. Sorting by multiple columns. Array 1 (2nd option) is a whole column selection.
The difference between SORT and SORTBY - is that instead of sorting by ascending or descending order we can choose another range or array to sort by instead.
124
Power Query
Power Query is a powerful BI tool, available in Excel that allows you to import data from different sources, clean and transform it and then reimport it back into Excel ready for analysis.
Query to the database allowing
(1) Updates with a click of a button.
(2) The process of acquiring and transforming data simple. [No TRIM, CLEAN, REPLACE,CONCAT, and Flash Fill]. Instead we use Power Query commands using a graphical user interface.
(3) Records all actions so we can easily back-track.
Steps
A. Importing data into the power query editor using GET and TRANSFORM
B. We clean and tidy our data
C. We re-export it out to Excel.
Option we have is to use standard housekeep formulas in Excel or to use Power Query.
125
Power Query > Power Pivot > Power BI
Power Query > Power Pivot > Power BI
M language > DAX Language > Power Query & Power Pivot.
[Fetch > Reshape > Publish] - [Model > Relationships > Cube] - [Visualisations > Interactive experiences > Forecast analytics ]
126
A Dashboard
A dashboard is where you gather together all different types of analysis and display them on one worksheet. Gives a really good idea of what is going on in the business. Ex. Sales dashboard. Interactive example.
Required.
A raw data set. Table / Pivot Table
A linked-through calculations page.
Charts used x3
(1) Combo boxes and (2) Charts and/or Pivot tables used.
127
Combo Box Drop Down [Dashboard #1]
Combo Box Drop Down is key to whole dashboard.
Developer Ribbon > Combo Box [Form Control]
Draw Combo Box
Combo Box as a Title
(1) Unique List of years
2018 (a6)
2019 (a7)
2020 (a8)
(2) Selection
1, 2 or 3 (a12)
(3) Selected Year
=INDEX($A$6:$A$8, $A$12)
1 is now 2018, 2 is now 2019 and 3 is now 2020.
Developer > Properties >
(1) Input Range [$a$6:$a$8)
(2) Cell link ($a$12)
Now drop down menu is complete showing 2018, 2019 and 2020.
and if we look at [Calcs] page we see that any change in the drop down combo box on lead page now changes all formulas on [Calcs] page.
128
Simple solution - In place of using Name Manager
Simply select the range with mouse and enter name in text box, Repeat over if required.
129
CSV file
Comma Separated Value.
130
SUMIFS and COUNTIFS [Dashboard #2]
=SUMIFS(Sales/Units sold column - CTL+SHF+Down, Country column - CTL+SHF+Down, Japan on Cells worksheet,
Year column - CTL+SHF+Down, Year cell selection - Combo box on Cells worksheet)
*Here it is SUMIF because we are totalling units sold, that is 5 in this line (Order), 3 in this line (order), etc.
=COUNTIFS(Products column - CTL+SHF+Down, Product on cells worksheet, Years column - CTL+SHF+Down, Year cell selection - Combo box on Cells worksheet).
*Here it is COUNTIF because we are totalling how many individual lines of orders there are, not totalling numbers between those lines.
131
INDEX
2010 a11
2011 a12
2012 a13
2013 a14
2014 a15
2015 a16
BOX a20
a24 = =INDEX(A11:A16,a20)
Now when 1, 2, 3, 4, or 5 are entered into cell a20, a24 will show 2011, 2012, 2013, 2014 and 2015 respectively!
132
INDEX and MATCH
Data Validation - Intermediate. Use of whole numbers.
Row #1 is days listed (A6-A12)
Row #2 is Numbers 1-7 corresponding with days before. (B6:B12)
We then have a pick a number box (E7) and a corresponding day (E8) where we are placing the INDEX & MATCH formula.
Formula in (E8) is =INDEX(A6:A12,MATCH(E7,B6:B12,0)
- match E7 entry (1 to 7) on the second column containing the corresponding numbers and return the corresponding value on the written day column!
INDEX - Returns a value or reference of the cell at the intersection of a particular row or column, in a given range. MATCH - Automates the finding of the row number / Returns the relative position of an item in an array that matches a specified value in a specific order. Use of INDEX and MATCH offer a more powerful way to perform Lookups. (Overcomes order of column look up). = INDEX (A6:A16, MATCH (H5, B6:B16,0)
133
Linking Charts to Combo Boxes [Dashboard #3]
[1] Think outside the box. Need Chart to link through.
[2] Insert very small Pivot Table. How do we link this to the Combo Box?
[3] Place on (Charts - Units Sold) worksheet / This is a separate sheet.
[4] Put just the years in PivotTable columns and switch off Grand Totals.
[6] Underneath the Pivot Table, place Index formula. =INDEX (Years on Pivot Table A24:C24, Cells $A12)
[7] Now if you select year, say 2018 in Combo Box, Chart updates also!
[8] Everything is now linking through.
[9] Chart data is coming from the Calcs page.
[10] Values on Chart data depends on whatever is in selected year, which comes from Combo Box.
[11] When selected year changes, the values change and in turn the chart changes as well.
134
WHATIF Analysis
WHATIF Analysis is the process of changing values in cells to see how these changes will effect the outcome of formulas in the worksheet.
Data > What If Analysis >
Goal Seek - Find the right input for the value you want.
Scenario Manager - Create different groups of values and scenarios and switch between them
Data Table - See the results of multiple inputs at the same time.
Solver - Solves problems presented to it.
135
Goal Seek
Goal Seek - Specify a value in a cell and then it will adjust figures in the other cells to achieve that value.
1/
Rate 3% p/a
Term 60 periods
Loam amount £24,000
PMT [Formula] = £1,924
2/ £3m Budget to spend after allocating 2% to all employees.
In summary -
Set Cell
To Value
By changing
136
Scenario Manager
Scenario Manager - Create different groups of values and scenarios and switch between them.
P&L
Named Ranges please!
Select changing cells within Scenario Manager.
Build 3 scenarios.
[Scenario Summary] to finish for 3-way illustration.
137
Data Tables
Data Table - See the results of multiple inputs at the same time.
Rate
Term
Loan Amount
PMT
One variable Data Table
Interest Rate PMT here
1% Auto-fills using Data Table
1.25% Auto-fills using Data Table
Two Variable Data Table
PMT 1.0% 1.25% 1.50%
5,000
5,250
5,500
138
Solver
Solver - Solves problems presented to it.
Tickets sold
Price per ticket
Revenue
Costs per ticket
Total costs
Profit
Want profit of £60,000 but costs can't change.
What price do we have to change is sales numbers stay the same?
[Okay, price to high!]. If maximum price as is, how may do we need to sell?
Identify changing cells.
Add constraints [ $B$4 > = 30 ]
139
INDIRECT FUNCTION
Unique function in Excel, in many ways.
Does not perform calculations, evaluate conditions or perform logical tests.
Indirectly references cells, ranges, other sheets and workbooks and lets you return the reference of a cell based on its string representation. As a result you can change your cell reference within a formula without changing the formula itself.
3 examples.
