Today’s author, Monica Poinescu, a Software Developer in Test on the Excel team, gives an overview of some new functions in Excel 2007 and along the way shows us how to do some simple expense tracking.
Considering the current economic environment, one may desire to better keep track of personal expenses. The new functions introduced in Office Excel 2007 simplify this effort. Here is how: given a list of expenses sorted by dates and categories (food, travel, clothing, etc.), how do I find how much was spent for each category, per month? And what’s the average amount spent? The functions SUMIFS, AVERAGEIFS and COUNTIFS perform selective calculations: they take into account only those values which fulfill several criteria. The values could be in a range spanning several rows and columns.
Let’s assume I entered the expenses in a table:
The solution below uses the new Excel 2007 feature structured references; see 'Tables Part 3: Using Formulas with Tables' for more details on how to simplify your formulas based on tables.
I’m also using the function SUMIFS which extends the functionality of SUMIF: given a range to be summed, it will add only those values that fulfill all the given criteria (one could use between 1 and 127 criteria applied to corresponding ranges).
The syntax is SUMIFS( the_range_to_sum, range_to_apply_criteria1, criteria1, [range_to_apply_criteria2, criteria2], … ) where the first parameter tells Excel what range we want to sum and the following pairs of arguments tell Excel on which range to apply the respective condition.
In visual terms, I think of each pair (range_to_apply_criteria1, criteria1) as a punch card
with some (0 or more) perforations, in the places where the criteria is TRUE. Stack all these cards on top of each other, the_range_to_sum being on the bottom, and SUMIFS adds only the values that remain visible.
Then start building your reports table by entering in F1:I3 the categories and the months:
and in G2 enter:
=SUMIFS( Table1[Amount], Table1[Category], G$1, Table1[Date], $F2 )
which will evaluate the ‘home’ expenses for January (235.35).
It should look like this:
Similarly, in H2 enter:
=SUMIFS( Table1[Amount], Table1[Category], H$1, Table1[Date], $F2 )
this will evaluate the ‘food’ expenses for January .
And in I2 enter:
=SUMIFS( Table1[Amount], Table1[Category], I$1, Table1[Date], $F2 )
to get your travel related amount.
You can copy/paste these formulas down as you add more months. This will automatically take into account new entries in your expense list. Also, in case you need to figure the average expense per category, within each month, you can use:
=AVERAGEIFS( Table1[Amount], Table1[Category], "home", Table1[Date], "January" ) - this returns 117.675
Quick and Easy Data Access with Excel Services
Today’s author, John Campbell, a Program Manager on the Excel Services team, shows us the quick and easy way to get external data access working with your Excel Services spreadsheets.
I have seen a lot of great blog posts that tell folks how to do great things with refreshed data in an Excel spreadsheet using Excel Services. I’ve seen posts about slicing dashboard pages, about using .odc files to manage data, and more. One thing that continues to be an issue is how to get connected to that data in the first place, in way that will work on Excel Services. The key is all about ‘telling the server’ what user identity to use when connecting to your data source. I know a number of people have gotten over this hump, but there are a lot of others who struggle here. In this posting I will shed some light on why the server works the way it does here, and show you the easiest way to get this up and running. I’m abbreviating in this post – so I’m not going to cover every last detail, option, and scenario here. If you want a lot more detail, and want to go deeper on why, and other options I don’t talk about here, then see the whitepaper I wrote on the topic: http://technet.microsoft.com/en-us/library/cc262899.aspx.
First, some quick terminology and background:
Credentials: The log in name/password of a user. These are used to establish your identity to the larger network/system.
Authentication: ‘Who you are’ – this is how the system verifies your identity.
Authorization: ‘What you can do’ – this is the process of the system determining what rights or permissions you have.
Connection String: This is a string, typically stored in a workbook or .odc file, which has the basic information needed to connect to a data source. It contains things like what server to use, how to authenticate, along with any special parameters that are needed to form the raw connection to the database.
Your goal is to get the user, who is viewing your spreadsheet in their browser, authenticated and authorized against the data source. (After all, your cool report showing sales figures sliced by time won’t be worth much if you can’t refresh the data to see the current numbers.) To be more specific, I am talking about getting an identity to use for authentication/authorization from the Excel Calculation Server (ECS) to the data source (circled in the picture):
Connection strings will typically contain information about how to authenticate to the data source, but, for various reasons, Excel Services can’t parse them to determine how to perform the authentication. So the user must explicitly set how the authentication is to be performed. There are three options for this: Windows (delegate your domain credentials to each box on the way to, and including, the data source), SSO (lookup a username/password combo to use on your behalf out of an Single Sign On database in SharePoint), or None (no specific options are set – just take the connection string and give it a try). By default, the Windows authentication is attempted because A) it is the most secure, and B) it has the best chance of working, assuming Kerberos is configured in a typical environment, without somebody (the administrator of the server or database) needing to do something special to make it work.
Ok, enough with the background info. So, you say you can’t setup Kerberos and don’t want to spend a bunch of time getting into details of authentication - so what’s the easiest thing to do? The rest of this blog is about how to configure the None option, and related server switches, to get external data refresh working on Excel Services.
Step 1: Configure the unattended account in the Excel Services administration page.
The unattended account is really just a set of credentials that Excel Services impersonates before certain types of external data connections are made. Think of it as a dummy user account that Excel Services keeps around for connecting to data. By default, many connection strings (like connections to SQL or Analysis Services), are configured to use whatever identity is available in the process at the time the connection is made. What all that boils down to is, if the connection string specifies that integrated security is used, then the identity of the unattended account will be used at the data source for authentication/authorization.
So here is how to do it:
On the server box, go to the Excel Services admin page: From the taskbar, click Start, point to All Programs, point to Microsoft Office Server, and then click SharePoint 3.0 Central Administration. Select the name of the SSP from the left-hand navigation bar. For example, SharedServices1 as shown.
Then, click Edit Excel Service Settings. Then scroll down and find the Unattended Account settings.
Enter in the name and password of an account that has access to the data sources you are planning to access, and click OK.
A few things to pay special attention to:
- If any other users want to use this account for their data refresh, they will need to make sure it has access to their data sources.
- Security is important! You should never use an account that has access to the SQL databases that are running your SharePoint farm. If the unattended account does have access to SharePoint, then it becomes possible for users to load a workbook on the server that connects directly to the SharePoint databases, bypassing most SharePoint security.
Step 2: Configure the data connection in workbook
From the Excel Data ribbon, in the Connections section, click Connections.
In the Workbook Connections dialog box, which shows all the external data connections that are currently being used by the workbook, select the connection that needs to be changed, and then click Properties.
In the Connection Properties dialog box, which allows many properties of the connection to be changed, click the Definition tab.
Click the Authentication Settings button.
From the Excel Services Authentication dialog box, select the None option.
Now just click OK on the various dialogs until you are back to your spreadsheet.
That’s all you need to do. Also note that with this None setting, if you use something like SQL authentication where a username and password are saved in a connection string, then SQL (or other data providers) should use those credentials to connect to the data. I say should, because Excel and Excel Services doesn’t control this – whether that username/password gets used is completely up to the specific implementation of the data provider.
If you want more information about why authentication works this way, more details about the other options, more information about managing data connections, or prescriptive guidance and Q&A’s in this area - then see the whitepaper, where I go much deeper, at http://technet.microsoft.com/en-us/library/cc262899.aspx .
New Case Study Involving Excel Services
A new case study has been published that describes how Microsoft IT used Excel Services to build a portal for finance data. Here's a snippet from TechNet:
Controller Workspace: Connecting With Excel Services in Microsoft Office SharePoint Server 2007
Describes how Microsoft IT created a centralized user interface platform, called Controller Workspace, that enables users to view reports, key performance indicators (KPIs), and other finance data. Out-of-box features of Microsoft Office 2007 Excel Services in Office SharePoint Server 2007 provide the foundation for the centralized portal.
The case study can be found here. There is a video demonstration at the link as well.
Hidden and Invisible Objects
Today’s author, Ben Rampson, a Program Manager on the Excel team, talks about cleaning up spreadsheets.
I often receive files demonstrating issues customers are experiencing with Excel. Recently I have noticed a common problem in some of these files that impacts the file’s performance and size: hidden and invisible objects.
When gathering data in Excel it is common to start with information found in a variety of sources, frequently lists or tables located on the Internet. Customers often copy and paste this data into Excel from the original source, but unfortunately this can also unintentionally paste many additional objects into the spreadsheet besides the data. These objects (shapes, text boxes, controls, etc.) are often not noticeable to the user after the paste, but can result in slower performance and larger file sizes. One step customers can take to avoid unwanted, hidden, and invisible objects in their document is to find and remove the objects after the paste operation as part of their data cleansing process.
Below is an image of part of a data set I copied and pasted into Excel 2007 from a website. The data set had 35 rows of data, but only the first few are shown.
After pasting my data (and extra objects) into Excel.
Looking at this image it is evident that some additional shapes were copied into Excel; an icon is shown in cell A1 and there are checkboxes in many of the rows in column B. I could select these visible objects and delete them, however, I would end up missing some of the objects that have been pasted into my sheet.
The following steps will help you find and delete all of the additional objects in your Excel 2007 worksheet.
The first thing you want to do is verify that you have additional objects on your spreadsheet. The easiest way to view a sheet’s objects is to turn on the on the Selection Pane (on the Home Tab go to the Editing Chunk > click the Find and Select Dropdown > select the Selection Pane option making the pane visible). Looking at the selection pane you will see a list of all objects on the current sheet and an indication if the objects are visible or hidden. If the selection pane is blank then you do not have extra objects on the sheet.
The top of the Selection and Visibility Pane for my sheet. Notice that 100 objects were pasted into my document. I have seen customer files with thousands of hidden objects.
Once you have the selection pane open and verified that you have objects on the sheet, the next step is to put your spreadsheet in Design Mode if it is supported on your current workbook. Design Mode will only be enabled in Excel if your sheet contains certain types of controls. Turning on Design Mode will allow you to select all controls, not just the basic shapes and form controls. To enter Design Mode select the Design Mode button in the Controls Chunk on the Developer Tab. If this button is disabled in the Ribbon then your sheet does not contain the types of controls that require this step. (Note: If you do not have the Developer Tab visible in the Ribbon you can enable it with the following steps: Office Button > Excel Options > check the “Show Developer tab in the Ribbon” checkbox on the default Popular tab).
You now are ready to select objects using the Go To Special dialog. To select all objects in the sheet: Ctrl+G to open the Go To dialog > select the Special button > select Object > click OK. The objects will then be selected and their selection handles are visible on the sheet.
My sheet with all objects selected. You can see that in addition to the previously visible objects I now also have selected some textboxes previously hidden on my sheet.
At this point you can simply hit the Delete key and remove all of these objects from the sheet. You can also choose to be more selective about the objects you delete; if you have other objects already on your sheet you wish to keep, ctrl+clicking the objects in either the selection pane or on the sheet will remove them from the current selection prior to hitting delete.
With just a little data cleansing work to you can avoid having additional unwanted objects in your document, resulting in faster performance and smaller file size.
Update to Microsoft's BI Strategy and Vision
Many of you might have heard or seen posts about a recent change to the Microsoft BI Vision & Strategy, namely, PerformancePoint will no longer be sold as a separate SKU but rather be part of the SharePoint Server enterprise offering, and the specific planning components will receive one more update this summer. More information on this change can be found on Microsoft’s Business Intelligence site and the The BI Blog. Just what does this change mean? For the most part, it means that more customers and partners will have access to the Microsoft BI offerings through SharePoint, more parts of the organization will have access to, and the ability to manage, analyze and understand the key drivers of their business, and more users will be empowered to make the right decisions.
For Excel, this means that those financial models you rely on, reports and other tidbits locked in your workbooks can become part of your dashboard and balanced scorecards. You can already post these to Excel Services for management and distribution of your workbooks. With PerformancePoint, they can now be easily rolled into your scorecards as well.
"But I already have Scorecards in Excel…"
PerformancePoint allows you to create context-based web dashboards that can change based on the user's selection, and provide additional commentary via a browser. You can easily include your existing workbooks within the PerformancePoint Dashboard, thus leveraging your analysis to explain a recent trend, promote further analysis, or justify a decision. Having everyone on the same page just got easier.
Repeating a Set of Data
I thought this would be interesting to share to see how other Excel folks might have solved this problem. I got a question recently from a customer asking how to automatically take a set of values in one column, say:
1000
5000
600
and repeat each value, say, 4 times in the next column, so that it looks something like this:
1000
1000
1000
1000
5000
5000
5000
5000
... and so on.
My response: the only way to truly do this automatically is to write a custom macro. However, if you can suffer through a few extra clicks, then my suggestion would be to (using the example data above):
- Select the three input cells (e.g. A1:A3)
- Select “Copy” (Ctrl+C)
- Select the next 9 cells underneath these inputs (A4:A12)
- Select “Paste” (Ctrl+V)
- Click the Sort button to sort the data into 3 distinct buckets (600, 1000, 5000).
For step #3, the number of cells you select will always be: NumberOfInputs x NumberOfRepetitions – NumberofInputs. In this case: 3 x 4 – 3 = 9.
That seemed to work well enough for the customer. What do you think? Is there another (better?) way?
When a Number is Not a Number
If you use Excel to track identification numbers (serial numbers, tracking numbers, etc.) you may find that if the IDs have more than 15 digits, Excel loses the digits after the 15th and makes them all zero. In other words, type "123456789123456789" into a cell and Excel will show you "123456789123456000". What's going on? Is this a bug?! Nope. You've hit an Excel limit with how large of a number Excel can handle when performing math operations on a number.
But in this case, we don't care about performing math on things like tracking numbers. The solution? Don't make it a number at all. Select the cells where you will be entering these identification numbers, right-click, select Format Cells and choose the Text option in the Number tab. This tells Excel to treat the number like normal alphanumeric text, which means they will display exactly as you enetered them, but you can't really use the value represented in the string as inputs to formulas (I'm over simplifying here a bit - those formula savvy among us know there are ways around this, but that's beyond the point of this discussion).
There's a shortcut too, if you type an apostrophe before entering that long tracking number, that's a signal to Excel to treat the value as Text.
Custom Number Formats
I've decided to start posting some of the replies I send to customers who write me asking for help on how to do something in Excel or Excel Services, especially those which I think might be solutions to problems others are likely to have.
For my first go I've picked - for no particular reason other than it was one of the more recent mails I've gotten - a question that has to do with custom number formats.
This customer writes:
What formula could I use to input a number like this 26-859-7423? with the dashes. I would like to be able to just type 268597423 and the number look like 26-859-7423. Can you help me?
Yes, indeed!
- Select the range of cells you want to format in this way.
- Right-click the selection and choose Format Cells.
- In the Number tab of the Format Cells dialog that appears, select the Custom option in the Category list.
- There's an input box underneath the word "Type:" where you can specify your own custom number format. In your case, what you want to enter there is 00-000-0000.
- Click OK.
The value "268597423" should now appear as "26-859-7423".
For more information on custom number formats, see this online help article.
Printing Envelopes Using Excel and Word
Today's author, Eric Patterson, a Program Manager on the Excel team, describes how to use Excel Tables as a source for a mail merge – a great productivity tool for sending out holiday greeting cards! :) Eric wrote this before the holidays but unfortunately I was out of the office and didn’t get to post this until now. Very useful nonetheless.
At this time of the year I find myself preparing to send out a large number of greeting cards to friends and family. I have most of my addresses in Excel with columns for FirstName, LastName, Address, City, State and Postal Code. The workbooks looks like this:
If you're like me, you don't keep your address list up to date and you don't print envelopes often. I have a number of addresses that I know are correct but there are others that I need to confirm before printing them out. Given the number that I need to do for the holidays, I do want to get started though.
Creating a Table
The first step is to create a Table from the list of addresses.
- Select a cell within the range containing the addresses, such as cell B2.
- On the Insert Tab, click on the Table button
- A dialog will appear asking for the range of cells containing your addresses and whether your table has headers. Verify that the items are correct and click OK.
- The range of cells will now have a table style applied and dropdowns at the top of each column that provide options for sorting and filtering.
Defining a Name for the Table
Excel allows you to define names that apply to a range of cells in the worksheet. When a name is defined for a Table in a worksheet, the name will update automatically as the table is resized to add or remove data. This is helpful in this case because I want to add and remove addresses during the process of printing envelopes.
- To define a name, click "Define Name" command on the Formula Tab.
- In the New Name dialog, type the name Addresses and the range of cells your addresses are in ($B$2:$G$15 in this case)
- Click on OK to create the name
Verifying Addresses
After my Table is created and a name applied, I start going through the addresses to confirm which ones are correct. I do this by adding a new column to the Table for this year. To do this, just type 2008 in cell H2. The Table and the name that we defined will expand automatically. Once you have the new column, then you can type something in the column to indicate whether the address is correct. I used a "Y" in my list.
Sorting the List
Next I want to separate the table into 2 tables; one for confirmed addresses and one for those that need to be updated.
- Click on the dropdown in the 2008 column
- Click on the Sort A to Z command and click OK
Here is what the sorted table looks like:
Separating the Tables
The next step is to cut and paste the unconfirmed addresses to another sheet in the workbook
- Select the table headers and copy and paste to a new worksheet (I named the sheet “NeedAddresses”)
- Select the records that are unconfirmed and cut and paste them to the new worksheet below the headers
- The Table will need to be resized to exclude the blank rows. Using the arrow handle in the lower right corner of the table, resize it to just include your addresses.
- Save your workbook (Addresses.xlsx) and you are now ready for the mail merge.
Start the Mail Merge
The steps above created a workbook containing a table of addresses on one sheet that has a name (Addresses) defined that corresponds to the table range. The remaining steps will show how to create the mail merge in Word to create envelopes.
- Start Microsoft Word
- On the Mailings tab, select the Start Mail Merge / Envelopes Command
- Select the appropriate Envelope size and click OK
- Select the Use Existing List command from the Select Recipients Dropdown
- Select your address file in the Select Data Source dialog and click Open
- Select the Defined Name, "Addresses" in the Select Table dialog and click OK
The Word document is now connected to the Table in Excel.
Adding the Address Block and Previewing Envelopes
Next you can insert the address block into your envelope and preview the results
- On the Mailings tab click on the Address Block command to add an address block
- Position the address block where you want it on the envelope by adding carriage returns, center alignment, etc.
- To Preview what it will look like, click on the "Preview Results" command.
- You can also use the other commands in the Preview Results group to find recipients or navigate through the addresses.
- When everything looks good, you can click on the Finish & Merge dropdown to print your envelopes.
- In the Merge to Printer dialog, I would suggest starting with just the current record to make sure that you have the envelope positioned correctly.
Tip: Cut plain paper the size of the envelope and add marking to show the positioning, then use it for a few trial runs.
- When things are printing as expected, you may want to print sets of say 10 envelopes at a time based on your printer using the From __ To: __ choice in the merge to printer dialog.
How do I finish my Envelopes?
As you validate more of your addresses, or if you want to send a card to someone that wasn't on your original list, follow these steps:
- First you need to clear the addresses that you've already printed. I copy the entire table to a sheet named "Printed" to start with, then clear the contents of the cells within the original table, leaving the table headers.
- Next I copy addresses from my "NeedAddresses" sheet and paste them just below the headers in the "ReadyToPrint" sheet which will expand the Table and the Addresses defined name.
More information about creating mailing labels with Excel and Word.
Why Can't I Open Two Files With the Same Name?
This is a common question we hear from customers.
The answer: calculation ambiguity with linked cells. If you had a cell ='[Book1.xlsx]Sheet1'!$G$33 and you had two books named “Book1” open, there’s no way to tell which one you mean.
VBA + Excel Performance: Your Feedback Requested...
The Excel blog is back after a short break for the holidays. Happy New Year! Today's post comes from Chad Rothschiller, a Program Manager on the Excel team, who is looking at ways to improve performance in the next version of Excel.
The Excel development team is currently investigating a number of scenarios where VBA macros take longer to run in Excel 2007 than in earlier versions (i.e. Excel 2003).
We are looking for folks who believe they have run into a VBA performance slowdown and who would be willing to share their code/workbook/steps to reproduce the issue. Given the enormous variety of solutions people build in Excel using VBA, we think it is important to survey the Excel community to collect a range of examples that are important to Excel users.
Specifically, we are interested in looking for VBA code that takes longer to run in Excel 2007 than in previous versions of Excel. Other than that, pretty much anything goes with respect to feature & code usage. To successfully analyze any problem, we really need to be able to run the code & reproduce the problem in-house, so the more specific and helpful the comment, the better. General comments about something slowing down, without the example code that demonstrates the issue, can prove difficult to isolate and analyze.
A couple notes:
- Don't forget to send us the workbook as well, if it contains data or a certain structure required by your code (for example, if your code assumes particular sheet names).
- If you can't send us the whole solution, or if you need to simplify the code at all, please keep in mind that those changes can end up altering where the performance bottle neck occurs, create new bottle necks, etc. In these cases it would be really helpful to also send us:
- a description of what the original code is doing,
- what sort of slowdown you are seeing on your target systems
... this simply helps us reproduce your results in-house.
We have already taken a look on the web for reported issues and followed up with some folks, but if you've already posted comments publicly, it wouldn't hurt for you to let us know those links just to be sure we've covered those. Anything submitted will be treated as 100% confidential and used only for our own internal performance testing.
Folks can send files or descriptions to: xlfiles@microsoft.com. If emailing files will not work, contact us and we can make other arrangements. Once we have the code / solutions, we will baseline their performance in earlier versions of Excel and then run it on a set of test machines using the version of Excel that's currently under development, and compare the results. Thanks in advance to anyone that sends in examples.
UK Excel User Conference
Is Excel an important tool you can't live without?
Would you like to meet with others to learn more about how to optimize your usage of Excel?
Do you like London in the spring time?
If you answered Yes to any of these questions, you might be interested in the upcoming UK Excel User Conference. It takes places on April 1st and 2nd, 2009. There will be great speakers from the Excel community, and the topics look to be very informative. And, it's free!
To learn more, check out the full details here.
Understanding Excel Services Load Balancing Options
Today's author, Steve Tullis, a Program Manager on the Excel Services team, talks about the various load balancing schemes available for Excel Services.
I have received a number of questions in the past few weeks about the load balancer administration options available for the Excel Services custom load balancer. Here’s the scoop: the settings account for the “OpenWorkbook” method when first accessing Excel Services. Once you have an active session with a workbook, additional requests from that user will always be sent to the ECS on which the “OpenWorkbook” request was handled.
So, why do we have three options – Workbook URL, Round Robin, and Local? The options are meant to provide the admin better control over the resource usage on his/her farm. An explanation of each option follows:
- Workbook URL: Arguably, this provides the most efficient use of your hardware. Requests are sent from the WFE to an ECS based on a hash of the URL. This ensures the same ECS will always handle requests for the same workbook – regardless of the user making the request. Which means a workbook will only ever be retrieved from SharePoint once – until it is pushed out of the cache, or the file changes in SharePoint.
- Round robin: Each OpenWorkbook request goes to the next ECS in the rotation. The result is that the same wb can be loaded from SharePoint N times, where N = the number of ECS machines in your farm. Keep in mind two things: (1) requests against an active session will always return to the same ECS, and (2) if the wb is cached on the ECS when an OpenWorkbook request arrives, it will be loaded from cache provided the file has not changed in SharePoint. The benefit with this option is that each concurrent request goes to a different server; thus, theoretically, queues on the server (CPU, I/O) will be shorter, thus requests can be handled more efficiently. The effectiveness of this is dictated by the composition of your workbooks (wbs with very long running calculations tend to invalidate this).
- Local: This setting ensures ECS processing happens on the same WFE machine which received the request, which requires that the ECS service must be running on every WFE. Again, the same wb can be loaded N times, where N = the number of ECS / WFE machines in the farm. The benefit here is, theoretically, performance. By running the ECS on the WFE, and reducing the # of server-to-server hops, end user performance should be faster. The cost is in retrieval from SharePoint & cache size due to the potential of the same wb being open on multiple WFEs.
As you can see, there are pros and cons to each option. But, with the information provided above, as well as a bit of knowledge and experimentation on your side, you should be able to correctly select the right load balancing option for your installation.
Using Excel to Optimize a Network Problem
Today's author, Diego Oppenheimer, a Program Manager on the Excel team, delves into some of the more advanced functionality of Excel to solve a challenging problem. The file used in this blog post can be found in the attachments at the bottom of this post.
Throughout this next blog post I will be using a tool that comes with Excel called Solver. For more information on solver please visit the Office Online site (http://office.microsoft.com/en-us/excel/HP051983681033.aspx?pid=CH010004571033).
Background:
I am currently planning a vacation drive exploring the state of Washington. Ideally in the short amount of time I have off I would like to do as many hikes as possible. I will be starting from Seattle and finishing at Kettle Falls in eastern WA. Along the way I have identified 4 possible stopping places that I am interested in. From stop to stop I have researched how many hikes I can do along the way between each one of my stops. With a tool like Microsoft Live Maps I could easily plot the shortest distance between Seattle and Kettle Falls, but I am more interested in optimizing my trip to include as many hikes as possible.
For this purpose I have decided to use simple principles of linear programming and create a network model with Excel that would maximize my hiking time on my upcoming vacation. This problem in graph theory is defined as a shortest path problem. Shortest path algorithms are used in web mapping software such as Live Maps.
For a better graphical understanding of my journey I created the following map:
Without ever "moving backwards" I have routed all possible combinations from stop to stop from Seattle, WA to Kettle Falls, WA. I have also numbered my stops from left to right for easier referencing in my workbook.
I built the table below for a quick reference on driving time between the different stopping points. You will notice that I have put a number in brackets (X) representing the number of hikes on that particular part of the journey:
Ex: Seattle to Darrington - Time: 1.4 hrs with 2 hikes that I would be interesting in doing.
| Seattle | Longmire | Darrington | Yakima | Wenatchee | Kettle Falls | |
| Seattle | X | 4 (5) | 1.4 (2) | 2.2 (3) | 2.5 (5) | 5.5 (6) |
| Longmire | 4 (5) | X | 4.2 (2) | 2.3 (2) | 4(2) | 6.3 (4) |
| Darrington | 1.4(2) | 4.2 (2) | X | 3.4 (3) | 3.3 (5) | 7 (3) |
| Yakima | 2.2 (3) | 2.3 (2) | 3.4 (3) | X | 2(1) | 4.4 (3) |
| Wenatchee | 2.5 (5) | 4(2) | 3.3 (5) | 2(1) | X | 4.2 (2) |
| Kettle Falls | 5.5 (6) | 6.3 (4) | 7 (3) | 4.4 (3) | 4.2 (2) | x |
The Setup:
Given that I love hiking I wanted to set up this optimizer so that it could be reused for any amount of stops I wanted. I've only included 6 in this version to maintain simplicity.
First let's create a table that numbers and labels our stop so we create the following table starting at O3:
| Stop | Name |
| 1 | Seattle |
| 2 | Longmire |
| 3 | Darrington |
| 4 | Yakima |
| 5 | Wenatchee |
| 6 | Kettle Falls |
Great now let's create our possible routes table. This table will represent the paths I drew out on my map and will also contain information on estimate time (in hours) and number of hikes.
We can see that now the table has all the possible routes I can take and also the estimated time between each path as well as the number of hikes on each path. We can now on B3 add our route selector which will be a binary switch (1 for True, 0 for False) that will represent if a path was chosen or not. For now let's initialize all these values to 0.
Solver will change our boolean values in the Route Select column so an easy way to find out how many totals hours and how many totals hikes I can do in my optimal path I can use the function SUMPRODUCT.
SUMPRODUCT will return the sum of the products of the two ranges I give it in this case (Route Select x Number of Hikes).
So let's label C17 as "Number of Hikes" and insert the following formula in C18:
=SUMPRODUCT(Route Selector X Number of Hikes)
Or in this example
Where Table1 is our possible route tables, Route Selector is the range from B3:B12 and Number of Hikes is the range from D4:D12.
Ok so to recap we have our possible routes table and we still have on O3 our stop numbers and stop names.
Next step is to create the constraint system that is necessary for solving this problem. In simple English we need to create constraints so that solver will only find solutions that follow these rules:
- I always leave Seattle,WA.
- I always arrive at Kettle Falls,WA.
- If I arrive at a certain node the next step has to be leaving that node. (With the exception of Seattle and Kettle Falls).
The easiest way to do this is to create once again a binary selector where +1 indicates arriving at a location and -1 indicates leaving a location. Our new network map would be something like this:
And last we will define our flow column by using SUMIFs. SUMIF will add the cells specific by a given set of conditions. In our case the condition is that all our stops "net flow" are 0 except our starting point and ending point.
=SUMIF(ToStopRange, Stop , Route Select Range) - SUMIF (FromStopRange, Stop, RouteSelect Range).
So on Q3 we will start a range titled "Flow". We will define our flow column by using SUMIFs. SUMIF will add the cells specific by a given set of conditions. In our case the condition is that all our stops "net flow" are 0 except our starting point and ending point.
And we copy this formula down from Q3 to Q6.
Since the flow range is determined by the values in the route selector we still need to give solver a reference range to match the flow I indicated in my map diagram. So adjacent to Flow I will create my constraint column and manually input the values -1 for Seattle, 0 for all my middle nodes and +1 for Kettle Falls,WA.
Setting Up Solver.Finally
To enable solver: http://blogs.msdn.com/excel/archive/2006/09/06/743902.aspx
We have 3 tasks to do when setting up solver:
Set Target Cell: Here we will select the cell right next to number of hikes since we want to maximize this number we select "Max".
By Changing Variable Cells:
The cells to change are our Route Selector Range.
Constraints:
Here we will specify the constraints that we discussed before:
- The Flow column must be equal to the constraints column. Select Flow Range = Constraint Range
- The Route Select Range has to be >= 0 (we only want positive number).
- Route Selector must contain binary values.
And we are finally done with setting up our solver. No we just click "Solve" and done. we now have an optimized hiking travel plan.
If you copied all the number I used you should be getting a maximum of 14 hikes and your ideal travel path is:
- Seattle to Longmire
- Longmire to Darrington
- Darrington to Wenatchee
- Wenatchee to Kettle Falls
Conclusion:
So now we have built a path optimizer that an easily be expanded (add more nodes) or changed around to optimize other variables like Estimated travel time. Even though this example is fairly simple (a map and some simple arithmetic would have provided faster results) the value of these methods comes into play when analyzing large amount of nodes. Similar principles to those discussed in this post are used for optimizing all sorts of manufacturing, transportation and distribution systems.
0 komentar:
Posting Komentar