## Ms 57 Solved Assignment In Excel

**GEOL 388**

Excel tips

Excel tips

**Also see the Excel manuals in the NT lab; Please do not take these out of the room**

**The Basics**

**The Basics**

When you first launch Excel, the program will open with a blank worksheet already opened. The main body of the worksheet is composed of a bunch of cells, outlined by light grey gridlines. Cells can contain a number or value, a word or words, or an equation. If the number you type into the cell is physically longer than the width of the cell, then Excel will display that value in scientific notation or in some other truncated form. If the word or sentence you type into a cell is longer than the width of the cell and if the adjacent cell is empty, then the word just writes over the next cell. If the adjacent cell has a value or some sort in it, the sentence or word is truncated at the cell border. When you type an equation into the cell, the solution to that equation is shown in the cell.

The cells are labeled according to their row (horizontal) and column (vertical). Columns are labeled as letters and rows as numbers. You can select an entire row or column by clicking on the number or letter of that row or column. By convention, Excel specifies the column first than the row.

This is a screen shot of a made-up spreadsheet. I have highlighted the cell named F9 (column F, row 9), whose value is 1.350. If you look in the white field just above the column headers, you will see the equation I typed into that cell to get the value 1.350: "=EXP(C9/10)". That equation means raise "e" to the power of whatever is in cell C9 divided by 10 (e^((C9 value)/10)). Equations will be discussed further on in this handout.

You can insert additional columns, rows, or individual cells. Frequently when organizing your spreadsheet, you will realize that you want an entire table of data to be 4 rows further down so you can add some text or constants above it. If you have equations in the cells in the table, it is generally easier to add rows or columns instead of moving the whole table. I will discuss this further in the section on Organizing a Spreadsheet. If you want to insert a row, highlight the row above which you want a new row. Then, select Insert à Rows. A new row appears, with all lower rows renumbered. Same holds for columns. When you insert a new column, it will place the new column to the right of the highlighted column. You can insert a cell also. However, I would strongly advise that you not insert an individual cell in the middle of a large, complex spreadsheet. There could be disastrous effects on your tables and equations!

One cell is referenced by a letter and a number, representing its column and row number respectively. A group of cells can be referenced in a convenient compact way as well. If you wanted to tell Excel to manipulate a table of cell values, you can use a colon ":" to do this. See the example below.

If I wanted to tell Excel to input all the numbers you see here into a function, I would write (or select by highlighting) A1:C7 into the function. The colon tells Excel to include all cells between the two endpoint cell references. If I just wanted to input the B column into a function, the reference would be B1:B7. If I wanted to input row 5 into a function, the reference would be A5:C5.

**Handy Tips**

**Handy Tips**

You can highlight a group of cells by clicking on one cell, holding the mouse button down, and dragging the mouse over the spreadsheet. The cells that are highlighted will appear black with a black cell border, except for the first cell highlighted, which will remain white.

To move the contents of a cell (or many cells) from one place to another, highlight the cell or group of cells, place your cursor on the sides of the cell (on the black outline of the cell) -- the mouse cursor will change to an arrow from a fat cross, click and hold the mouse button on the border, and drag the cell to its final destination. The destination cell will be overwritten!

To copy the cell or group of cells, highlight the cell(s), click Edit à Copy. Then highlight the destination cell(s) and click Edit à Paste. If you want to paste the formulas in the cells or just the values of the cells, you can select Edit à Paste Special.

__Automatic Filling:__

The best thing about Excel is its automatic fill capabilities. Let's say I wanted to make a column of numbers from 0 to 15. Type in a few entries - enough to define a pattern - into a column. Below, I typed in 0 and 1 into a column and highlighted both numbers.

Next, place your cursor on the lower right-hand corner of the highlighted region (it is marked by a little black square on the black border of the highlighted region), click on that corner, hold the mouse button down, and drag the highlighted region down several cells. Below is a screen shot of doing just that. The little pale yellow box with the number 15 in it shows the value of the next consecutive box of the column.

The series that this generated is a series that is incremented by 1. If the original data I typed in was a 0 and a 2, then the series generated would be a series that increments by 2. This is a quick and painless way to generate a long series of numbers that increment predictably.

If the two numbers used to generate the series are the same numbers, i.e. 2 and 2, then the series generated will be a series filled with 2's.

**Built-in Functions**

**Built-in Functions**

Excel has a large library of built-in functions. These are functions that are already described for Excel and are very easy to plug in to a larger equation that you might be writing.

For those of you who are unfamiliar with simple computer programming or Matlab programming, a function is a pre-defined command that takes anywhere from 0 to infinite arguments to perform it's calculations. For example, the built-in function Pi() takes no arguments. Pi() returns the value of Pi up to 15 significant digits. A function like SUM(number1, number2,...) takes up to 30 arguments and adds them all up.

[TIP: sometimes a "number" in a function doesn't have to be just literally one number. You can frequently input a whole range of numbers as one "number". For example, SUM(A1:A15) only uses one argument in the function. It just so happens that that argument contains 15 values. The function call SUM(A1:A15) will produce exactly the same results as SUM(A1, A2, A3, …A15).]

To see a listing of all the built-in functions, highlight a cell and choose Insert à Function. A list of all the functions, subdivided based on the type of function it is, appears with a simple description of what that function does and what input values it needs and in which order. Make sure you pay careful attention to a function's arguments and their order.

Here is a screen shot of what the listing of functions looks like after choosing Insert à Function. I have highlighted the AVERAGE function in the Statistical subdivision. Below the white fields is a description of the function AVERAGE ("Returns the average (arithmetic mean) of its arguments, which can be numbers, names, arrays (a whole column of numbers), or references that contain numbers.") and the arguments it takes. Arguments can also be a mathematical expression or another function that returns a value.

After highlighting AVERAGE and clicking Ok, the following dialog box appears just below the formula bar:

This box gives you a little more information about the formula. You can enter in the arguments to the formula at this point in the "Number 1" field. Clicking on the white, red, and blue little box just to the right of the Number 1 will shrink this dialog box so you can easily access the rest of the spreadsheet.

[TIP: you don’t have to type in the cell references by hand in these types of dialog boxes. You can shrink the dialog box using the blue, white and red button mentioned earlier and then highlight the cells on the spreadsheet you want input into the argument. Click the button again and the dialog box will enlarge.]

__Some useful Built-in functions:__

Pi()

SUM()

AVERAGE()

DEGREES()

EXP() (which raises the argument to the base e)

The Trig functions (SIN(), COS(), TAN())

LINEST() (returns an array that describes a best fit straight line to a set of data)

LN()

LOG()

LOGEST() (returns an array that describes an exponential curve derived from regression analysis that best fits a set of data)

POWER()

RADIANS()

SLOPE()

STDEV()

**Equations**

**Equations**

Excel would be just a giant, confusing calculator if we couldn't write in a big nasty equation and force Excel to solve it.

Equations (as far as I know) return a number. You can't solve symbolic equations in Excel. This means that whatever complicated mathematical expression you type in as an equation must return a number.

You can have cell references in an equation, but the cell that is referenced must contain a number. If you reference a blank cell, the number 0 is automatically inserted.

+ is addition

- is subtraction

* is multiplication

/ is division

a^b raises the left number, a, to the power of the right number, b.

When you want to type in an equation into a cell, the first character you type must be an "=" (equals sign). This tells Excel to evaluate whatever comes after it, otherwise Excel will just treat it like a string (a bunch of letters) and not evaluate the equation. **Excel follows, religiously, the old grade-school rule of nested parentheses and order of arithmetical operations. Missing or incorrect parentheses is the BIGGEST reason for mistakes/errors in Geophysics labs.**

**I would HIGHLY recommend writing out very long equations on paper before typing them into the formula bar.**

For example:

To add the quantity 57 divided by 10 to 4 squared, you would write the following into a cell in Excel:

=(4^2)+(57/10)

Now, in this example, the rules for the order of operations works in our favor, because

=4^2+57/10

returns the same value.

However, here is an example where the parentheses become crucial.

Raise 4 to the power of 2 divided by 0.3 and then multiply that quantity to 47 +3.

The correct way:

=4^(2/0.3) * (47+3)

or =(4^(2/0.3))*(47+3)

The incorrect way:

=4^2/0.3*47+3

**It is always better to err on the side of more parentheses than necessary.**

__Using Cell References in Equations:__

You can also enter in a cell reference into an equation. While typing the equation, you can either manually type in the appropriate cell reference or click on the cell with your mouse.

You can drag and fill equations to make a series of equations as well as dragging and filling in numbers (as explained in the Handy Tips section, under Automatic Filling). For example, let's say you had a series of x values from 0 to 10, incremented by 1. You want to multiply each of those cells by 2 and put those new values in a separate column.

The A column has the first series of numbers. The B column is where I will type in the equation that multiplies the A column by 2. I manually type in the equation into B1, referencing A1 in the equation. Now, I click the green check mark in the formula bar or hit enter to enter in the equation into the cell.

B1 shows the value that the equation returns (0*2)=0, and the formula bar shows the formula I typed into the cell to get that value. Now select the lower right-hand corner of the highlighted B1, hold the mouse button down, and drag down to B11.

There are now similar equations in all of the B column cells. If you notice the cell reference in each of the B column cells, you will see that Excel automatically incremented the cell reference in the direction (in increasing row number) that we filled. I have highlighted B7 for an example. The equation in the formula bar no longer reads A1 (the original cell reference we typed into B1), but it reads A7 instead. Excel is smart enough to increment the cell value appropriately as you drag and fill the equation into the rest of the B column cells.

What if you don't want Excel to increment the cell reference in your equation? You can use dollar signs "$" to hold a cell reference fixed. Below is a similar example to the one we just did, except we will add a constant value (alpha) to the equation.

In A2 I have written (just as plain text) the name of the constant, and in B2, the value of the constant. It isn’t strictly necessary to write down the name of a constant or variable in Excel. However, as far as Spreadsheet Organization goes, it is essential. The series A4:A14 is just a series of values. In B4, I have written in the equation that I am going to drag and fill down to B14. In the equation, the B2 value (our constant) has dollar signs in front of BOTH the column and row reference to hold BOTH the column and row fixed. The A4 (the cell reference that we want to increment) has no dollar signs.

Here's what the table looks like after I drag and fill the contents:

I have highlighted cell B9 so you can see the formula displayed in the formula bar for that cell. The B2 cell reference (our constant) is still the same, despite our dragging and filling, and it has the "$" in front of the column and row. The A9 cell reference has been incremented from the original A4 reference that we typed in.

Technically, since we are only incrementing the row number, we could have written the constant cell reference as B$2 (holding only the row fixed, since we are only incrementing the row). **However, I would suggest - unless you have a compelling reason not to - to always fix both column and row when referencing your constants just to be on the safe side!**

**Common Error Messages**

Quoted portions of the above error message listings were taken from the Excel on-line Help system.

Excel provides Auditing Tools to help troubleshoot error messages or problems in your spreadsheet. If you highlight a cell (with data or a formula in it), click Tools à Auditing, you will see 4 different functions that will help troubleshoot your formulas: Trace Precedents, Trace Dependents, Trace Error, and Remove all arrows. There is also the option of displaying the Auditing toolbar - a helpful thing if you have lots of errors!

In the last example on dragging and filling formulas, I had created a little table that contained a series of values returned by formulas. In the screen shot below, I have highlighted a cell and clicked on Tools à Auditing à Trace Precedents.

The blue arrows show which cells contribute to the value of cell B10. If you look in the formula bar, the cell references displayed there confirm what the auditing tools show by the arrows, that A10 and B2 are in the B10 formula.

Auditing tools only trace precedents or dependents one step back/forward. If for example, the number in B2 was obtained by solving some formula that depended on 10 other cells, the 10 other cells would not be joined by arrows by tracing precedents on cell B10. Tracing dependents will show all the cells that depend on the value of the highlighted cell.

You can remove all the arrows when you are finished troubleshooting a particular cell.

## Entering an array function

Array functions return more than one value into more than one cell. A good example that we use often in this course is the "user defined" function DISTAZ, which takes the latitudes and longtitudes of two points and returns both the great circle distance and azimuth. The way to enter such functions is to

- first select the cells that will receive the values returned by the function;
- then write the function with the appropriate inputs in the Excel input window above the spreadsheet;
- here is the critical step: to enter the function to make it work properly, you must depress the following three keys:
**Ctrl - Shift - Enter** - use the same key combination if you are filling down
- If you get trapped trying to edit an array function, simply hit the
**Esc**key

**SOLVER**

**SOLVER**

Solver is a tool that will iteratively solve equations that have a unique numerical value, but can't easily be solved by the standard algebraic method. I have invented a simple equation below to illustrate how to use solver.

x=log(x) +3 can't easily be solved by merely putting all the x's on one side of the equations and performing the arithmetic, so we need to guess at possible x solutions and evaluate the equation. Solver will guess and evaluate and guess and evaluate iteratively until it gets a solution for x that is within some set boundaries.

In cell D6, I have written out the equation we would like to solve in text. In D8, I have rearranged the equation to equal zero. Solver can solve an equation so it equals some specific number (0 is always a handy value), solve an equation to a maximum value, or solve an equation to a minimum value.

Solver needs a "Target cell", H13, which is a cell that contains the equation in D8 (the left-hand side of the equation in D8). The Target cell is the cell that Solver is trying to get a specific number for (like zero), get a maximum value for, or a minimum value for. Solver also needs a cell to change, H14. The Change cell (H14) is our guess at what x is. I have entered in a initial guess of x=20. Try to guess intelligently and start with an answer that is already fairly close to the true value. Some functions are not well-behaved and can give illogical, but strictly true, answers for x.

Highlight the Target Cell (H13) and choose Tools à Solver

This is the dialog box that appears. The Target cell is written (with dollar signs to fix the cell reference) at the top of the dialog box (this should only be **one** cell, not an array of cells). I selected "Equal to:" a Value of 0, since our equation was rewritten to be equal to zero. I clicked in the white field of "By Changing Cells:" box and then selected H14, our cell that has our initial guess for x. I have not included any constraints, but frequently these can be very important if you already have some prior knowledge of what the answer should be.

Now click Solve.

Solver quickly ran into a problem. After Solver completes its iteration, you can always choose to discard the answer if it doesn't make any sense or is in error.

This is what Solver came up with the first time: X=-12 and the Target cell has an error in it. So, I am going to try a value of X that is closer to the real value. Apparently my function is not so well behaved.

I will try x = 1.

This time Solver found a solution that conforms to all my constraints.

x=3.55026 will yield a solution to my equation shown in D8 of 0.00000011, which is pretty darn close to 0. You will never get all the way to zero. Anything E-04 (which in Excel speak, translates to 1*10^(-04), or smaller is close enough.

There are some options in Solver, under the Options button on the Solver dialog box.

Here you can specify how precise you want Solver to be in reaching your target number, how many iterations to try, and how to do the iteration. The values shown here are the default values, and are frequently adequate for solving many formulas.

You can, of course, have more than one cell that needs changing to solve an equation, you can have more than one variable that is unknown. If you attempt to solve a multivariable problem, I suggest adding as many constraints to the solution as possible to help Solver.

__If Solver isn't loaded?__

If you can't see the Solver option under the tools menu, you may need to manually add it. You can do this by selecting Tools à Add Ins dialog box appears that lists all of the add-ins that Excel offers. Scroll down and make sure that Solver is checked.

If Solver is checked and you still don't see it under the Tools menu, then uncheck it, check it again and click OK. If you still can't get Solver to appear, then select Browse in the Add-ins dialog box. Browse to C:\Program Files\Microsoft Office\Office\Library\Solver\Solver.xla and select Solver.xla and click Ok. If this still doesn't work or it prompts you for a CD you don't have, come find me!

**Graphing**

**Graphing**

Graphing is best done using the Chart Wizard in Excel. Let's use my Sample Datasheet that you have seen earlier in the handout to demonstrate the Chart Wizard.

Here are three sets of y values, all with the same x values. Let's graph x and y1 on a scatter plot.

[HINT: In geophysics, most of the line or point graphs you will do should be done as scatter plots not "line" plots. Line plots will space all the x values equidistant along the x axis, even if their values are not equal increments! This is not good.]

Highlight the x and y1 values and click the Chart Wizard button (the one that looks like a little 3D bar graph).

The first dialog box that appears is the following:

Typically, you will want to select XY (scatter) under the Chart Type. Then choose a Chart sub-type, either the unconnected dots or the dots connected by STRAIGHT lines. You can choose other chart types as you need them, just don’t use the Line chart type for plotting data.

Select your chart type and subtype and click Next.

This is the second Chart Wizard dialog box, showing how your data would plot. The Data Range also shows the exact cells that Excel is plotting. If you need to adjust the Data Range, highlight the info in the data range, then click on the appropriate cells in the spreadsheet. This series is in a column, not a row. The Series tab is shown below:

The Series tab shows the series that are plotted on the chart. One set of y values is equivalent to one series. If you would like to give the Series a more inventive name (recommended), type it in here under Name. This tab also shows the X and Y value cell references. If you need to adjust these, highlight the incorrect info (all of it) and select the correct values on the spreadsheet itself.

When your series values are correct, click Next.

This dialog box is fairly self-explanatory. You can always add/change titles and axis titles later on, once the chart is finished.

The fourth dialog box allows you to choose between putting your chart into a new sheet in your workbook or adding the chart as an object on your existing sheet. It’s up to you, both ways have their pros and cons. You can print just the chart with either scenario.

Click Finish and your new chart appears. I have chosen to include it as an object in my original data sheet.

I have the chart selected (you can see the 8 little black boxes around the perimeter of the chart showing that the chart is "highlighted"). When the chart is selected, the data that the chart is plotting is shown outlined in blue (y values) and purple (x values) on the spreadsheet.

If I want to add another series to the same chart, all I have to do is highlight the y values -- as long as it uses the same x values, click Edit à Copy, highlight the chart, click Edit à Paste. The new y values will appear as a new series on the chart.

Here I have added the y2 values by this copy and paste method. You can also add data by highlighting the chart, selecting Chart à Add Data…, and highlighting the new y values (range values) to be added.

When the Chart is highlighted, the Chart menu selection is available. The first four options under the Chart menu selection are Chart Type, Source Data, Chart Options, and Location. These four options correspond to the four Chart Wizard steps or dialog boxes. You can change the series name or check the x and y values from these options. You can also edit the whole chart from these menu selections after you are all done with chart Wizard.

If you wanted to plot all three series I have shown here at once, you could highlight all four columns and use Chart Wizard. **Chart Wizard assumes that the first column contain the x values and that each subsequent column contains additional y values**.

Another useful set of Series-editing tools are visible if you highlight the data series itself. You must click on a data point that is in the series you want to edit.

I have highlighted the dark blue Series 1. My pointer was over the last point in this Series when I did the screen shot, so you can see the "label" on that last point. Once you have selected the data series, you can choose Format à Selected Data Series… and the Format Data Series dialog box will pop up.

This dialog box will let you customize line colors, data point colors, error bars, data labels, etc.

You can also format the Chart (whole chart) and Plot (just where the data points are) Areas by double clicking on the Chart or Plot areas, respectively. Experiment with all of these formatting options. You can change the font, orientation, and size of any lettering on the Chart, including the x and y axis values.

If the x and y values that you want to plot are separated from each other, or are in the wrong order (y to the left of the x), you can highlight the x values column, then hold down the Ctrl key while you highlight the y value columns. Proceed as normal.

This shows to non-adjacent columns being highlighted in preparation for plotting. I clicked on the x values first, then the y3 values.

If you want to plot two series on one chart, but they have DIFFERENT x values, the easiest way is to copy and paste the new y values onto the chart. Then, highlight the new series, click Chart à Source Data. Then highlight the information in the X values field and select the proper x values on the spreadsheet chart. There may be a more elegant way to add a completely new series, but this is the easiest way!

There is a happy medium between a graph that contains so much information that you can’t separate one series from another and a graph that only has one dataset and requires you to print out 43 different graphs to convey your point. If there are several series that you will be comparing with each other, put them on the same graph (as long as the graph is still easy to read). Save trees.

**If your graphs can't tell the story alone with no additional text, save a figure caption, then your graphs are NOT made properly.**

**Printing**

**Printing**

The only printer that most of you will be set up to print to is the HP LaserJet (black & white) in room 2161 (as mentioned in the "Things you need to know about the Computer lab" section). You need to sign out for the prints you make for administrative purposes. There should be a little white sheet of paper near the printer with a listing for your name, account #, and number of copies. Under name, write your last name or your first and last name. Under Account #, write geo388. If the printout is not for geophysics (or another GEOLOGY class), then write "personal" under account #. Under Number of copies, list how many copies you made. As of today (Jan. 11, 1999), you shouldn't be charged for any prints you make that were required by the class. If this changes, I will let you know.

If there is a problem with the printer, please let me know ASAP. Note: I am not usually around on the weekends to solve problems, so try to have emergencies during the week!

__Print Area__

If you only want to print out a portion of your spreadsheet, then highlight the area you wish to print. Next, click File à Print Area à Set Print Area. Then select File à Print to print. You can always (and should always) check the print Preview under the File menu just to be sure everything is in order. Everytime you redo the Set Print Area command, it will overwrite any previous Set Print Areas.

If you want to print out a graph only, one that is embedded as an object into a spreadsheet, select the chart (so the 8 little black boxes around the perimeter appear on the graph). Then, click File à Print (or Print Preview). You don't have to set the print area to print only the graph.

__Page Setup__

Most of the print options are under Page Setup, under the File menu. Once you have set your print area, you can choose File à Page Setup.

The options are fairly self-explanatory. I frequently "fit" my spreadsheets into a certain number of pages if the width is just a little too wide. But, if the program has to reduce things by ½, don't bother. I won't be able to read it if the text is too small!

The most interesting thing on this tab in the Page Setup is that you can center your print on the page (a good idea, usually). You can monkey with the margins to get a little more on a page if you want.

The Header/Footer tab is fairly boring. I usually don't put headers or footers, but if it helps you than be my guest.

The Sheet Tab contains at least one useful option, the option to print or not to print gridlines. If you make liberal use of the underlining cells/cell border options when making your spreadsheet, you can probably do away with printing the gridlines. Just don't print out a big table of numbers without cell borders marked and no gridlines.

**Organizing your Spreadsheet**

**Organizing your Spreadsheet**

It is difficult to write a "manual" about style and organization. However, if your spreadsheet is well organized and easy to read, your odds at getting the lab done correctly the first time improve exponentially!

I will try to distill how I go about organizing my spreadsheets for you. You can use these as guidelines or starting points for your own organizational style.

- Title your spreadsheet in big bold letters (as you would title a paper)
- Keep all of your global, frequently used constants at the top of the spreadsheet, in a box, separated from your other data. Keep the value of the constant and the units of that constant in two separate boxes.
- Make your series run vertically. (i.e. if you have x values for something, put them in one column and multiple rows – not one row and multiple columns)
- Label your columns with a descriptive label (i.e. "density values, mid crust" NOT "y6")
- Feel free to put an additional bit of text explaining a particular value in more detail off to the side of the cell or table. It will help me grade the lab, and it will help you understand the assignment better.
- If the assignment has several parts/questions, segment your spreadsheet into those same parts. You can use several sheets for the same lab and still have values depending on values on other sheets.
- If I am dealing with a long equation, I will write it out as text in a cell above where I am going to use it in a cell. This helps avoid mistakes when I enter it into a cell as an actual equation.

Just think about the lab and what types of values you need to calculate and what values you are trying to solve for before you sit down and just start typing.

__Colors/Borders__

You can add a color to highlight a piece of your spreadsheet. Of course, you can only print out in color, but shading all of your density values on color and your velocities another (for example) might help keep track of what you are calculating. The highlighting button should be on your toolbar.

You can click on this and choose from a wide variety of colors. If you want to print out the spreadsheet with the highlighting still on, choose very pale colors.

You can also, and should also, outline the borders of your tables and constant lists and other spreadsheet elements.

You can outline one side of a cell (or highlighted region), a whole cell (or highlighted region), or the whole cell (or highlighted region) in bold. This is different than pressing the "Underline" button, which merely underlines the text in a cell, not the whole cell itself.

You can also change the text color, if you prefer that to highlighting a whole region of cells. The same caution about printing it in black and white applies. Make sure the colors are fairly dark. When they are printed in grayscale, they still need to be legible.

__Data Values__

**Watch your significant figures! **It is meaningless to report a number to the sixth decimal place if the constants and data you are inputting only have 2 or 3 sig figs. The only time when I want to see more than 2 or 3 sig figs in your cells is when the variation you are tracking is in the 4^{th} or 5^{th} decimal place.

These are the buttons that will increase or decrease your significant figures in our cells. Don’t increase the sig figs gratuitously, though! All of the cells in a certain column or table should have the same number of sig figs if they are working from the same constants and input data.

**Unformatted text preview: **3/30/2015 Assignment Problem in Excel Easy Excel Tutorial Excel Easy #1 Excel tutorial on the net Excel Introduction Basics Functions Data Analysis VBA 300 Examples Ask us Assignment Problem Follow 5.2k Formulate the Model | Trial and Error | Solve the Model Use the solver in Excel to find the assignment of persons to tasks that minimizes the total cost. Formulate the Model The model we are going to solve looks as follows in Excel. http://www.exceleasy.com/examples/assignmentproblem.html 1/8 3/30/2015 Assignment Problem in Excel Easy Excel Tutorial Advertisement Excel to Tally Converter Versatile & fastest way to post any data from Excel to Tally (any ver) 1. To formulate this assignment problem, answer the following three questions. a. What are the decisions to be made? For this problem, we need Excel to find out which person to assign to which task (Yes=1, No=0). For example, if we assign Person 1 to Task 1, cell C10 equals 1. If not, cell C10 equals 0. b. What are the constraints on these decisions? Each person can only do one task (Supply=1). Each task only needs one person (Demand=1). c. What is the overall measure of performance for these decisions? The overall measure of performance is the total cost of the assignment, so the objective is to minimize this quantity. 2. To make the model easier to understand, name the following ranges. http://www.exceleasy.com/examples/assignmentproblem.html 2/8 3/30/2015 Assignment Problem in Excel Easy Excel Tutorial Range Name Cells Cost C4:E6 Assignment PersonsAssigned C10:E12 C14:E14 Demand TasksAssigned C16:E16 G10:G12 Supply I10:I12 TotalCost I16 3. Insert the following functions. Explanation: The SUM functions calculate the number of tasks assigned to a person and the number of persons assigned to a task. Total Cost equals the sumproduct of Cost and Assignment. Trial and Error With this formulation, it becomes easy to analyze any trial solution. For example, if we assign Person 1 to Task 1, Person 2 to task 2 and Person 3 to Task 3, Tasks Assigned equals Supply and Persons Assigned equals Demand. This solution has a total cost of 147. http://www.exceleasy.com/examples/assignmentproblem.html 3/8 3/30/2015 Assignment Problem in Excel Easy Excel Tutorial It is not necessary to use trial and error. We shall describe next how the Excel Solver can be used to quickly find the optimal solution. Advertisement Buy 1 & Get 1 75% Off! Fly to Mumbai Book now Solve the Model To find the optimal solution, execute the following steps. 1. On the Data tab, click Solver. http://www.exceleasy.com/examples/assignmentproblem.html 4/8 3/30/2015 Assignment Problem in Excel Easy Excel Tutorial Note: can't find the Solver button? Click here to load the Solver addin. Enter the solver parameters (read on). The result should be consistent with the picture below. You have the choice of typing the range names or clicking on the cells in the spreadsheet. 2. Enter TotalCost for the Objective. 3. Click Min. http://www.exceleasy.com/examples/assignmentproblem.html 5/8 3/30/2015 Assignment Problem in Excel Easy Excel Tutorial 4. Enter Assignment for the Changing Variable Cells. 5. Click Add to enter the following constraint. Note: binary variables are either 0 or 1. 6. Click Add to enter the following constraint. 7. Click Add to enter the following constraint. 8. Check 'Make Unconstrained Variables NonNegative' and select 'Simplex LP'. 9. Finally, click Solve. Result: http://www.exceleasy.com/examples/assignmentproblem.html 6/8 3/30/2015 Assignment Problemin Excel Easy Excel Tutorial The optimal solution: Conclusion: it is optimal to assign Person 1 to task 2, Person 2 to Task 3 and Person 3 to Task 1. This solution gives the minimum cost of 129. All constraints are satisfied. Advertisement http://www.exceleasy.com/examples/assignmentproblem.html 7/8 3/30/2015 Assignment Problem in Excel Easy Excel Tutorial Product forecasting statwizards.com Generate product forecasts in Excel using state-of-the-art techniques Excel to mobile app Optimal Control Software Free Excel Templates Bitrix24 - 100% Free CRM Professional Logo Design Free Excel Add-in Download Full Books Microsoft Excel 2010 Excel Help & Fin Models Do you like this free website? Please follow us on Google+ Follow 5.2k Go back to solver, use the side menu >> Go to Top: Assignment Problem | Go to Next Example: Shortest Path Problem Go Search Entire Site Go back to Solver Download Excel File assignmentproblem.xls Follow Excel Easy Copyright (c) 20102015 www.exceleasy.com. All rights reserved. excel 2010 tutorial | how to excel | microsoft excel 2010 | excel macro http://www.exceleasy.com/examples/assignmentproblem.html 8/8 ...

View Full Document

## One thought on “Ms 57 Solved Assignment In Excel”