AGSC 2301 TTU FINAL

How to make a text result in a function?

"word"

Absolute

$A$1

Column Absolute

$A1

=AND

(argument 1, argument 2)

=INDEX

(array, row,column)

=IF

(question, if true, if false)

=PPMT

(rate/peryr, PERIOD 1,yrs*peryr,-loan amt)

Offset

(row,column)

=VLOOKUP

(value to look at, date area, column)

Operators

+,-,/,*

What can VBA do?

- automate tasks - increase productivity - macros - custom functions - create stand alone apps

Demand Shifters

-Price of related goods -Expected future prices -Change in Income -Change in expected future income -Change in number of buyers -Change in consumer preferences

Summary Stat Table

-average -min -max -range -N -hist bins -bin size

Histogram Table

-bin number -bin increment -bin height -frequency

How many values does the function evaluate to? =SUM(F2*(G2:G10/H2:H10)*J2)

1

How many layers does excel allow a nested IF function to have?

64

Not Equal

<>

Frequency

= ( data, bin height)

Bin Increment

= bin number * bin size

Bin height

= minimum + increment

Histogram Bin Size

= range difference/bins

Random number between 2 and 66

=22+(RAND()*(66-2))

Consecrate A1 and A2 with a single space

=A1&" "&A2

If A1 is not equal to A2, yes, if not, no

=IF(A1<>A2,"YES","NO")

Nested AND in IF

=IF(AND(something is true, else is true), value if true, value of false)

Where to next IF into IF function?

=IF(question, true, IF FUNCTION

Characteristics and name function

=INDEX( h and w numbers,MATCH(name, names, 0),MATCH(characteristic, h and w titles,0))

Index for state name and abbreviation

=INDEX(abbreviations, MATCH(state, whole state names,0),1)

Find the middle name for cell P37

=RIGHT(P37,LEN(P37)-SEARCH(" ",P37))

Return last 2 digits of the year from the date in cell A1

=RIGHT(YEAR(A1),2)

Total returned values

=SUM(( range<>value)*(range))

Total number of cells in range

=SUM((range<>value)*1)

Formula for cumulative payments

=SUM(payment 0:payment 1)

What is VBA?

A programming language for excel and other MS apps

Row Absolute

A$1

What is the matrix form system of equations?

A*X=B

What solves the issue of 1 condition in an IF function?

AND, OR

Shortcut for VBA editor

Alt+F11

Shortcut for format box

Alt+O+E

How to force calculation back to automatic at the end of a macro?

Application.Calculation = xlCalculationAutomatic

What code requires user to ask for calculation?

Application.Calculation = xlCalculationManual

Change calculation of manual in VBA

Application.Calculation= xlCalculationManual

'turn off screen updating

Application.ScreenUpdating = False

Change screen updating in VBA

Application.ScreenUpdating = False

What code can be used to speed up the simulation calculations?

Application.ScreenUpdating = False

'screen updated back to default

Application.ScreenUpdating = True

What shape is Normal Distribution described as

Bell Curve

Supply Shifters

Changes in the cost of inputs Changes in the number of producers Change in price of related goods Change in productivity Change in expected future price

Shortcut for undo

Ctrl + Z

What is the keyboard shortcut to copy selected objects?

Ctrl+C

An array function is entered into the cell with the keyboard combination

Ctrl+Shift+Enter

When constructing a matrix for a system of equations, variable p in the X matrix corresponds to which equation?

Demand

Shortccut for edit

F2

What is the shortcut to enter edit mode on selected cells?

F2

How to step into debug menu

F8

'assign variable for numbers 1 to 10,000

For i= 1 to 10000

Every function has

Name Arguments

Bell Curve Distribution

Normal

What are the components of a math model?

Objective function, Constraints, pos variable function, variables

'recalculate numbers in range A1:A5

Range("A1:A5").Calculate

Place trial number not column A

Range("A2").Offset( i , 0).Value= i

'move to cell beside A3

Range("B3").Offset(i,0) = trial

'place trial number into column J

Range("J1").Offset(i,0).Value=i

VBA module consists of 2 procedures

Sub and Function

When constructing a matrix for a system of equations, variable q in the X matrix corresponds to which equation?

Supply

What does a logical expression evaluate to?

T or F

Distribution for a 52 card deck

Uniform

VBA

Visual Basic for Applications

Methods are

actions on the object

IPMT,PPMT,PMT absolute or not?

all absolute

Step 5 of Simulation Process

answer problem question

Bin Increment

bin size/bin #

Data from a population is a

census

Step 1 of Simulation Process

define domain of possible inputs

Code for 10,000 trials

for i = 1 to 10,000

Step 2 of Simulation Process

generate inputs randomly from a probability distribution

Objective function

goal of problem

Range

group of 1 or more cells

Standard deviation is a measure of

how spread out numbers are

Is the following function in inverse or standard form? p=120-2qd

inverse

Constraint function

limitations

Step 3 of Simulation Process example

look for win or loss in di roll

68% of students have a GPA between 1.5 and 3.5. Assuming Normal Distribution, what is the mean and standard deviation?

mean= 2.5 std dev= 1

actions on objects are

methods

N

number of data points

A random variable must have a

numeric value

Step 3 of Simulation Process

preform deterministic computation on the inputs

Range("A8")

range object

Single-cell Array

returns a single value into a single cell

Multi-Cell Array

returns more than 1 value into multiple cells

What are the 2 primary reasons spreadsheets are in such widespread use?

reuse and duplication

Data from a subset of a population is a

sample

Selction.Copy

select previous object, and copy

Is the following function in inverse or standard form? p+2qd=120

standard

When writing a system of equations, what form should the functions be in?

standard

Variance

sum of squares/N-1

Xi-Xbar=

total rain - $average

The best approach to simulate a deck of 52 cards is

uniform distribution

Step 4 of Simulation Process

use VBA to do computations

Decision variable

values that can be chosen