IENG 423Design of Decision Support Systems

Modeling with ExcelExcel BasicsMore on Functions, trouble shooting formulas

MS Excel

Categories of Functions in Excel

Math & Trig Statistical Time/Date Text Financial Logical Lookup Custom

MS Excel Common Functions

Math & Trig ROUND(number, ndigits) Ceiling(number, significance)

Rounds up to multiples of significance Floor(number, significance)

Rounds down to multiples of significance LOG(number, base)

Returns a logarithm of number to a base base

MS Excel Common Functions

Math & Trig RAND()

Returns a uniform random number between 0.0 and 1.0

ROMAN(number) Converts an arabic number to a Roman numeral

SUM(number,number…) Calculates the sum of a set of numbers, range

SUMIF(criteriaRange, criteria, sumRange) Calcs sum of range if criteria =true in criteria range

SQRT(number) Calcs the square root of number

MS Excel Common Functions

Statistical AVERAGE(range)

Returns the arithmetic mean of range COUNT(range)

Returns the number of cells in range containing numbers

FORECAST(x, known_ys, known_xs) Returns linear project of x bases linear model

using known xs and known ys TREND(knownys, knownx, newxs, const)

Returns predicted new y values based on linear least squares model

MS Excel Common Functions

Statistical MAX(range)

Returns the largest value in range MIN(range)

Returns smallest value in range MODE(range)

Returns most frequently occurring value in range MEDIAN(range)

Returns middle most value in a set of values (range)

RANK(number, range, order) Returns the relative rank of number from a set (in

range), either ascending or descending (order)

MS Excel Common Functions

Date/Time NOW()

Returns current date and time (from computer’s clock)

TODAY() Returns current date formatted as a date

MS Excel Common Functions

Financial DB(cost, salvage, life, period, month)

Returns the depreciated value of assets for a specified period of time

PMT(rate, nper, present_value, future_value, type Calculates payments for a loan due at begin or end

of period (type), at a fixed RATE, for fixed number of payments (nper), based on the principal (present_value) and a desired future value

NPV(rate, payment,payment,…) Calculates the net present value of an investment by

using a discount rate and a series of future payments (negative values) and income (positive values).

MS Excel Common Functions

Logical IF(log_test, true_action, false_action)

Take different action depending on whether the log_test is true or false

AND(log_test1, log_test2,…) Returns true only if all log_tests are true

OR(log_test1, log_test2,…) Returns True if any log_test is true

NOT(log_value) Makes true false and false true

MS Excel Common Functions

Text functions CONCATENATE(text1, text2,…)

Returns a single text string made of arguments LEFT(text, n)

Returns the left n characters of text RIGHT(text, n)

Returns the right n characters of text MID(text, start, n)

Returns a subset of the string text, beginning at start for n characters

SEARCH(look_for, in_text, start) Searches for string (look_for) in a string (in_text)

starting at start – returns the character position number in in_text

MS Excel Errors

Error code Probable Problem

##### Cell too small for value

#VALUE! Something wrong with the input to a formula, probably the wrong type

#NAME? The formula contains something that Excel does not recognize, typo?

#REF! Formula makes a reference to a cell that does not exist

#DIV/0! Can’t divide by zero

MS Excel – Trouble Shooting Errors

Tracing Precedents What cells is a formula using?

Tracing Dependencies What formulas are using this cell?

Watch windows