IENG 423 Design of Decision Support Systems

1. IENG 423Design of Decision Support Systems Modeling with Excel Excel Basics More on Functions, trouble shooting formulas

2. MS Excel • Categories of Functions in Excel • Math & Trig • Statistical • Time/Date • Text • Financial • Logical • Lookup • Custom

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

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

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

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

7. MS Excel Common Functions • Date/Time • NOW() • Returns current date and time (from computer’s clock) • TODAY() • Returns current date formatted as a date

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

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

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

11. MS Excel Errors

12. MS Excel – Trouble Shooting Errors • Tracing Precedents • What cells is a formula using? • Tracing Dependencies • What formulas are using this cell? • Watch windows