IENG 423Design of Decision Support Systems

Modeling with ExcelExcel Basics

So, let’s imagine…

In the spirit of the election season that we are in…You are the treasurer for a newly formed political party…Whose whole platform is to have more parties…Your new political party is called the …Party Party

You have a fund raising team-..and, for December they have turned in…

3

Party Party Contributions

Bob 1211.00

Mary 1008.00

Jim 4363.00

Sally 5621.00

Fred 325.00

You need the total contributions

So, you can add the money column

4

Party Party Contributions

Bob 1211.00

Mary 1008.00

Jim 4363.00

Sally 5621.00

Fred 325.00

Total 12528.00

5

•It turns out that not all of the data was in when you start this table•… and Fred thought he could keep part of the money for his own party this weekend

So, we need to recalculate

6

MS Excel

7

Oh, you need to do this for December and January

So, how do you get started

Like Dennis Hopper says “You need a Plan” What is the spreadsheet suppose to do? –

What is it purpose? -- write it down What are the givens, the inputs Are there categories of information that will

be part of the spreadsheet? What outputs do you need? What are the relationships between the

inputs and outputs

So, how do you get started

Like Dennis Hopper says “You need a Plan” Make a sketch – yes on paper! Is it laid out to be meaningful, usable

and useful to - the end user – not you – the end user?

And don’t forget to test it And test it again

So, how do you get started

This does not mean that the plan is “carved in stone”In fact, Excel is pretty powerful with respect to letting you modify your model after you have created it (usually)…and it is pretty smart about adapting your model as you make changes (usually)

Remember

You are developing your spreadsheet application for a customer, client or bossAlways try to put yourself in their shoes

MS Excel

Entering Data Selecting a Cell Entering Text

Excel sort of knows Entering numeric data Entering numbers as text Entering Dates

MS Excel

Entering arithmetic expressions or formulae Prefix entry with arithmetic operator Recommend using = For example,

Entering an = and a cell reference will enter whatever is in the referenced cell in the current active cell

We will come back to this!

MS Excel

Selecting Cells Columns Rows Contiguous Ranges Extending selected contiguous ranges Noncontiguous ranges

MS Excel

Selecting Entire worksheet Other worksheets

MS Excel

Inserting columns in a worksheetInserting rows in a worksheetAdjusting column widths Drag column edge Set to specific width Autofit

Adjusting row heights Like above

MS Excel

Erasing cell data Delete – tries to move neighboring

cells or delete entire row or column Point to cell and hit space bar – enters

a space in cell – will cause cells that use the active cell in a formula to error

Clear Contents – will set cell to have no values

MS Excel

Autofill Will copy active cell contents to other

contiguous cells Will copy formula in active cell to

contiguous cells – with adjustments Extend a series in two or more

selected cells

MS Excel

Copy and Paste Copies selected contents to other

cells Copies formulae with adjustments

MS Excel

SaveSave AsSave often

For Next Time

Read Chapters 3 and 4 of MS Excel 2003 Step by StepDo some research on importing files Txt files Delimited files Other Excel Workbooks XML files