IENG 423Design of Decision Support Systems

Modeling with ExcelExcel BasicsFiltering, sorting, data validation, summarizing data

Note about files

Through out these slides references are made to files to use to try the concepts being discussed. These files are from the CD included with the MS Excel: Step by Step book

MS Excel

Using Filters Sometimes you have more data in a

worksheet than you want to see …Takes you attention away from what

is relevant to the problem One way to deal with this issue is the

use of Filters

MS Excel

Using Filters

Note: If your primary DSS problem is one of locating, extracting or organizing records of data, rather than dynamic calculations, then the problem might be more suitably frames as a database problem

MS Excel

Using Filters But, sometimes

Using MS Access (or another DBMS) complicates the solution

MS Access has a nontrivial learning curve Your block of data is not that big You want to keep your DSS application in

Excel Your client know Excel, does not know Access ! You need to do dynamic modeling…

MS Excel

Using Filters It may make more sense to stay with

Excel

MS Excel

Using Filters Sometimes you have too much data –

rows and rows You can limit the amount of data for

your view… …or analysis You want the user to focus on a

specific subset of data

MS Excel

Using Filters Use Filter.xls from UsingFilters

subdirectory

Filters eliminate rows (whole rows) that do not meet criteria

Note: they are not permanently eliminated

Turn off the filter and they are back

MS Excel

Using Filters Select a column or a header Select Data->Filter->AutoFilter

This toggles on filtering for the selected column

Select the pull-down arrow – see filter dialog box Select value from list Top 10 Custom Show All

MS Excel

Data Validation You can control using input By setting rules for what can and cannot be

entered in a cell Improved user interface – pick from list Reduces errors

Remember Murphy’s Law - if a user can do it wrong there is good chance it will be done wrong

MS Excel

Data Validation Select cell where you want data

validation Select Data from the main toolbar… …then select Validation The Data Validation dialog box will

pop-up Select the type of data validation that

you want

MS Excel

Data Validation For the selected cell or cells you can

limit input to – A specific range of whole numbers ( 1 to 10) A specific range of decimal numbers (0.5 to

6.5) A specific date or date range (1/1/2008 to

1/15/2008) A specific time or time range (08:00 AM to

11:30 am)

MS Excel

Data Validation Validating from a list

You can an options list and only values that are in that list range can be entered in the cell or cells

Suppose in G1:G5 you have WV, VA, PA, MD, and DE

Then for Cell A1 using list validation with the rule

=G1:G5Would limit input into A1 to one of those five

state values

MS Excel

Data Validation Validating from a list

Note: the question came up in class – Can you validate from a list with the list coming from a different worksheet

In other words for A1 on sheet1 the rule =Sheet2!G1:G5 Well, the answer is no, sort of Excel 2003 specifically forbids this However, read on….

MS Excel

Data Validation Validating from a list

Suppose you have a lookup list on another worksheet (say Sheet2)

Select you validation list on Sheet2 and make is named range (like states for the state code list)

Then in the validation rule for your cell (i.e. A1) Enter an = and then the named range For example =states That works!

MS Excel

Data Validation Validating from a list

You can also set an Input prompt for the cell or cells

This prompt will show up in a balloon when you hover over the cell

You can also set a specific error message to be displayed when the user enters something that does not conform to the rule

MS ExcelData Validation Use Sorting.xls from

ReorderingAndSummarizing folder Create a list of the days of the week

abbreviations (Mon, Tue, Wed,…) Out in the worksheet like H1:H7 Select cell to set list validation Select Data->Validation, then List from the

validation dialog box Click in Source field, then drag across your

day-of-week list range (H1:H7) Then click OK

MS Excel

Sorting and Summarizing Data Use Sorting.xls in

ReorderingAndSummarizing folder Select Range or Header for column Select range of a single column in a

table with more columns… …and sort … Excel will want to fix your sort

selection to include adjacent columns, its will say—

.. Expand Selection

MS Excel

Sorting and Summarizing Data In Sorting.xls

Select Sales -> Data -> Sort (don’t expand range– what happened)

It scrambled the data, didn’t it! Undo Sort Redo Sort, but accept expanded range …what is different?

MS Excel

Sorting and Summarizing Data What is the sort order? If you sort days of the week what do

you get What is the first day of the week –

alphabetically? …the second?

MS ExcelSorting and Summarizing Data What is the sort order? What if you want a non-normal order

Like in day-of-week order Create a custom list

Select Tools -> Options -> Custom Lists Enter or select list then OK

Then to sort by day of week Select header or range Select Data->Sort, then on the Sort dialog box,

select Options In the Options dialog box select the appropriate

list under First Key Sort Order

MS ExcelMaking a Data List Use Sorting.xls in

ReorderingAndSummarizing folder

Why, what is a data list Data in rows and columns belong together …like records in a set Excel 2007 calls this a table

To create a data list (can’t have a filter enabled Select a range, select Data->List->Create

List

MS Excel

Making a Data List After creating a data list Sort again

Click anywhere inside the list, then select Data->Sort – Select Sales

Sort treats list as a whole – that is, it sorted the list together

FYI – autofiltering is automatically turned on when you create a list You can turn it off it you want

MS Excel

To get on-the-fly totals Select a range (column or row – sales

for example) Look in the autosum box on the status

bar

Change Sum to other statistic – right click on Autosum box, select Mean, Min, Max, …

MS Excel

To add a Total row Click “Toggle Total Row” in toolbox To get a different statistic

Change to Average if you want the mean – right click in Total row cell, select Average

MS Excel

Displaying Data in Collapsible Levels Sort by column that has categories–

like Week Can’t be a list The click Data->Subtotals

Complete dialog box Shows summary data for levels Controls on left side allow you to collapse

or expand levels

That is all