# Need Comprehensive / Detailed Guide On Using The Solver Which Includes Many Examples

Jul 2, 2007Does anyone have a comprehensive / detailed guide on using the Solver which includes many examples?

View 3 RepliesDoes anyone have a comprehensive / detailed guide on using the Solver which includes many examples?

View 3 RepliesADVERTISEMENT

I am having problems with this excercise on my worksheet in my business computing class. I was wondering if someone could guide me through on how to use the solver feature.

Excercise:

Eve Blue is the manager of Southland Furniture store and she is planning a New Years Day sale. The store has only 75 square feet of space available to display and stock this merchandise. During the sale each folding table costs $5, retails for $11 and takes up two square feet of space. Each chair costs $4, retails for $9 and takes up one square foot of space. You must order at least one chair and one table and, of course, they must be whole units. The maximum amount allocated for purchasing the tables and chairs for the sale is $280. Eve cannot sell more than 40 chairs but the demand for tables is unlimited. Eve asked you to help her determine how many tables and chairs she should purchase in order to make the most profit.

Layout: (I marked in x's where I need to solve.)

TablesChairs

Unit Wholesale Cost $5.00 $4.00

Unit Retail Price $11.00 $9.00

Profit per Unit $6.00 $5.00

Square feet per Unit 2 1

Quantity to Order x x

Total Cost $- $-

Total Profit $- $-

Total Square Footage00

TOTAL ORDER COST $-

TOTAL ORDER PROFIT $-

TOTAL SPACE REQUIRED0

within excel for a big spreadsheet they developed?

can you suggest a way of doing this or an article to read?

could users press F1 and let my own custom help guide appear?

I have a cell, D5, which is the sum of three other cells, A5 B5 and C5. (all currently empty). Cells A1 through C4 are filled with various numbers.

What I've been trying to do is use solver to say: Make D5 equal 200, do it by manipulating only A5 B5 and C5, and make it subject to the constraint that A5 must equal a value selected from A1:A4, and B5 must equal a value from B1:B4, and C5 ...etc. I have deliberately set it up so that there is only one solution.

I was doing fine until trying to create the constraints. How can I make a constraint that says "this cell" must equal "one of the following cells"? And if I can't do that, is there an alternate method of achieving the same result?

The setup here is I have multiple codes for a product. Coding structure is as follows (options are contained in parantheses):

N -(T,P) - (5L,6,7,8) - (DFL,DJL,DBL,UEDF) - P - (1,6,9,16,19,1H,1H9) - (g,s,blank) - (a,blank)

Clearly, there are a ton of options. What I'm doing is screening those codes and breaking them down into their component parts.

ex) NT6UEDFP1a: N - T - 6 - UEDF - P - 1 - a

ex) NT6DFLP16: N - T - 6 - DFL - P - 16

I'm trying to do multiple if/then scenarios for each option. However, the way I have it now, I'm getting a FALSE when I switch between these two examples (the dfl works, the uedfp generates false). (there are other codes which is why the first line is there. I'm just having trouble with this particular "nt/np" group)

=IF(OR(LEFT($A$14,2)="np",LEFT($A$14,2)="nt"),

IF(OR(MID($A$14,4,3)="dfl",MID($A$14,4,3)="dbl",MID($A$14,4,3)="djl"),[code].....

I have a template I used where, over the course of a month, I add an additional row of information (the 31 rows are there, just the information is inserted with macros every day). The charts, though, are daily, meaning that every day I need the charts to show information from the last row entered. How do I instruct a chart to take the last row of information and take the data from there (and automatically create the chart) instead of having to do it manually?

View 2 Replies View RelatedThe examples cited on [url] will not work for me. I have tried several different one, in new and existing worksheets but cannot get them to work.

View 9 Replies View RelatedI have to use use the solver to calculate something (a mean-variance framework).

I am using the solver to minimize a cartain cell (variance) by making two cells equal through (expected return) by varying 10 cells( weights of assets), but I have to repeat this for 500+ times (for different expected returns).

Someone told me that I could best use some sort of loop through VBA. But I don't have a clue how that works.

I don't think this may even be possible, but what I am trying to do is pull only certain information from one worksheet to another based on whether there are entries on certain dates for employees.

A sample is attached.

On the first worksheet I have a drop down menu for all employees on the second sheet. (the drop down menu pulls the names from the second sheet)

The second worksheet has all employees in Column A and to the right has all their variances by date. Most dates will be blank as they didn't have a variance.

What I would like is on the first worksheet, is to select their name from the drop down menu and have all the dates that they had variances and the variances show up.

If you take a look at the attached excel file it might explain what I am trying to do better.

Is it at all possible to have a hyperlink that when clicked will take the user to detailed information on another page? I have a summary information with account numbers on one page and would like to click on a hyerlink related to the account number and take the user to the detailed information on another page.

View 3 Replies View RelatedI have a detailed in which the particulars are entered as shown below.

Sl.No

Name

Amount

Remuneration

1

A

32500.00

40000.00

[code]....

Now the Abstract of the above data is needed to be populated in the next sheet which looks like below.

Sl.No

Name

Amount

Remuneration

1

A

87500.00

6344.00

[code]....

I know if Sl.No, Name, Amount and Remuneration are in same row(as in Sl.No3), this can be done using VLOOKUP. But now can some one give me a formula where in Name, Amount and Remuneration can be populated if they are not in same row(as in Sl.Nos 1 and 2).

I work with large spreadsheets. I was hoping that someone would know how to create a macro that will take selected cells in column C and search all of column C for exact duplicate entries. The macro envisioned would let you select cells to test. If a dulicate entry is found I would need the macro to test the cells in column E against one another. If both C & E match exact (if possible maybe display the findings in a pop up box) I would like the macro to ask me if I want to delete the duplicate entry, if yes, delete the entire row. If no, skip it and move to the next one. If C but not E match I would need the macro to ask me to remove test cell. if yes, cut out the entire row of the TEST cell and paste it in a new sheet. If no skip it and move on.

Am I just dreaming or can this be done.

I've got several huge information bloks to handle.

Would you be so kind as to give the matter a reasonable consideration?

I've added an xlsx file with more detailed description.

I work with large spreadsheets. I was hoping that someone would know how to create a macro that will take selected cells in column C and search all of column C for exact duplicate entries. The macro envisioned would let you select cells to test. If a dulicate entry is found I would need the macro to test the cells in column E against one another. If both C & E match exact (if possible maybe display the findings in a pop up box) I would like the macro to ask me if I want to delete the duplicate entry, if yes, delete the entire row. If no, skip it and move to the next one. If C but not E match I would need the macro to ask me if I want to remove thetest cell. if yes, cut out the entire row of the TEST cell and paste it in a new sheet. If no skip it and move on.

Am I just dreaming or can this be done. If it can be done, but more info is needed let me know.

I need to create 3 or 4 detailed reports from an excel 2010 worksheet.

My worksheet contains data including date, invoice number, company , consultant, days, dollars. There are approximately 100 records in the worksheet.

Requirement 1 :I want to use excel 2010 to automatically generate a series of reports broken down by consultant showing date, invoice number, company, and consultant with totals for days and dollars, when all the records for consultant 1 are listed, then I want to show consultant 2.

Same for any other consultants with a grand total at the end.

My understanding is that excel 2010 makes this process very simple .

I have a summary sheets for my report that my department no longer wants to use. Rather they want to have a longer, more detailed version of the same thing, but rather than 1 sheet summarizing it all, they want it 1 widget, 1 sheet.

How can I have my Checksheet tab (see upload), turned into multiple tabs. In the specific example I provided, there are 10 widgets, so therefore I created the result in 10 tabs.

How can I get a macro to turn Checksheet tab into the next 10 tabs (see excel upload)?

I have attached the excel sheet of how my summary sheet is, and how it's supposed to look like.

I want to use solver program. But when solver "changes cells" i want it to trigger my pivot tables in the workbook. So i added the code to my worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)

ThisWorkbook.RefreshAll

End Sub

So when a change occurs, all my pivot tables will get refreshed and my data will change. Is solver able to trigger this event while solving an optimization problem?

I am trying to creat a summary sheet to an attendance log that goes back several years. Idealy, I want to be able to set a date range in the formula that will show me a count of how many absenses, lates, or leave earlies. The table I am taking the data from has columns from left to right (Date, Absense, # of Absenses, Late, Duration of, Left Early, Duration of, and MP) For example in column A I want to see the date Range of 10/1/2013 to 4/1/2014 and I want a count/sum of each of the categories.

View 5 Replies View RelatedPlease see attached file. In my column L, I'm trying to find a formula that would reference my column J to the table in the data tab. My problem is the data in column J has a hyphen (-) in the number ex.. 047-258-1 and the next tab does not have a hyphen in between the number...ex 0472581. The correct results in cell L3 would be Walmart. How can I adjust my formula to accommodate this hyphen. Can this also be reflected in my pivot table as well?

View 6 Replies View RelatedWhat I need is a formula for the following:

If Column A equals "No Show" and Column B equals "Anthem" then sum of cell C2 divided by D2

Every time I input this simple little formula I get an error.

A

B

C

D

Status

Coverage

Counts

No Shows

No Show

Anthem

5

10

What would the formula be if A1 includes a certain string of text, then B1 returns a given value? e.g., if A1 contains baby then B1=carly.jpg?

View 3 Replies View RelatedI have a list of codes in a column, they are all 6 digits long. Some are 3 letters followed by 3 numbers (eg. ABC001), and some are just 6 numbers (eg. 000123).

Example list....

ABC001

ABC002

CDE003

EFR005

000123

000345

004567

000567

000789

001234

002345

BBB002

CCC003

DDD004

I want to be able to autofilter with the criteria being all the ones that include letters, or all the ones that are only numbers. So I need to fill a variable with each list so that I can use it for the autofilter criteria.

How would I go about identifying the ones that have letters in from the ones that do not?

I'm writing some VB in an Excel macro to search and replace a string of text.

The problem is that the string contains the " symbol, so when Access gets to that symbol it thinks that it's reached the end of the string.

e.g.

I want to replace "0519","863027" (including the quotes) with "0001","863027"

This is what the code looks like, but it obviously doesn't work :

I have a macro that will create 15 reports based on a filter in a Pivot Table and will save them to a folder based on the filter names. This work great, however in one of the reports I have a few vlookups as an example of one of them,

Code:

=VLOOKUP(Selector,Trends2!$B$6:$LV$22,AI50,FALSE)

. Now when the report is created from the macro, the vlookup is changed to,

Code:

=VLOOKUP(Selector,'Z:ISB DataISR RASC non transactional dataISR dataNational and Zone Reports[ISR National and Zone Template v18a.xlsm]Trends2'!$B$6:$LV$22,AI50,FALSE)

Is there any way to stop this from happening? Is it a general thing in Excel? Code below that creates the reports if needed

Code:

Sub Create_National_Report()

'Start of report creation process

Sheets("Pivot Tables (2)").Select

[Code]....

I have seen a few posts that are close to this but not quite right. I have a situation where I need to do "=LEN..." formula that counts trailing zeros at the end of numbers (meaning zeros that are displayed at the end of a number but do not appear in the actual cell value).

example:

Cell E19 contains the actual value 4773.52, but it is displayed in currency format with FOUR digits so it displays as $4,773.5200.

The formula I am using is =LEN(MID(E19,FIND(".",E19)+1,4)). The result is 2, which means it does not include the last two trailing zeros in the LEN count.

Is there anyway to get it to include the trailing zeros so the result equals 4? Either w/formula, macro, or UDF?

Is weird I know, but I need to know how many decimal places (displayed, not actual) for many numbers on many sheets that I do not create myself.

I am having trouble sorting the data in a worksheet that includes a column of email addresses. Every time I try to sort the sheet it excludes the email column.

View 2 Replies View RelatedI'm looking for a function similar to =WORKDAY but with a slight variation.

I want the formula to return the date that is the closest non-weekend, non-holiday day that is a specific number of CALENDAR days from the start date. So the formula does not return weekend or holiday days, but does include them when counting the days away from the start date.

For example if the start date was Dec 1st, and I specified 14 days after this date, I would want Dec 15th returned (as the 14th is a weekend).

I have a workbook that we are using to track some training, the spreadsheet has the date of the course at the top and the trainee's name in column A

Additional sheets will be added each time a class is scheduled labeled with the date of the class and in the same format as the ones included titled: "1.13.14" & "1.20.14".

I need to add a table to the workbook that will collect ever new hire from column A and the Date of their class from cell "B1" in each sheet.

Copy of SLC-BD Peer Training 1.xlsx

I'm creating a restaurant rota spreadsheet and I need to calculate the hours for each waiter, which is easy enough. But I've got to include stand-by shifts and cover shifts into the rota for the staff and I'd like the formula to ignore the "standby" text, etc in its calculations. I know you can use the =IF(... function, but that only works with one value. As well, the standby shifts will change from shift to shift and week to week, so I need one formula that might encompass all these needs... if there is one!!

An example of my rota for you to see:

MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY SATURDAY SUNDAY

Total hours for week START FINISH START FINISH START FINISH START FINISH START FINISH START FINISH START FINISH

10:00 17:00

10:00 17:00 12:00 16:00

Lunch Hrs/Wk 18:00 #VALUE! 19:00 24:00

standby standby 19:00 24:00 standby standby o2 Cover o2 Cover

Dinner Hrs/Wk #VALUE!

I have a worksheet that includes a cell that gives the current week of the month, in the form of "Week #". I've been using the formula below for several months and have had no issues until this month, and it's driving me crazy.

Given that cell D2 is the beginning of the week (usually Monday, but at the first of the month it may be a different day):

="Week " & ROUNDUP((DAY($D$2)+WEEKDAY(DATE(YEAR($D$2),MONTH($D$2),0)))/7,0)

I'm not sure where the logic is going wrong here; it's worked so well up until now. For the week beginning November 2, 2009, the formula above returns "Week 2" rather than "Week 1".

Copyrights 2005-15 www.BigResource.com, All rights reserved