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 Replies
ADVERTISEMENT
Solver Examples
Nov 8, 2006I 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
Creating A Help Guide Within Excel
Sep 26, 2007within 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?
Multiple Solver Constraints In Solver
Sep 10, 2006I 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?
Getting A FALSE When Switch Between Two Examples?
Apr 11, 2014 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].....
Dynamic Chart Examples
Feb 5, 2008I 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 RelatedDynamic Named Range Examples Won't Work For Me
Feb 27, 2007The 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 RelatedSolver Solver To Calculate Through VBA
Jan 12, 2007I 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.
Pulling Detailed Data From One Sheet To Another
May 4, 2009I 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.
Hyperlink To Flow To Detailed Worksheet
Jul 6, 2009Is 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 RelatedExtract Abstract From Detailed Sheet
Oct 17, 2012I 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).
Detailed Macro To Find Duplicates
Feb 22, 2007I 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.
Added XLSX File With More Detailed Description?
May 3, 2014I'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.
Very Difficult & Detailed Macro To Find Duplicates
Feb 22, 2007I 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.
Excel 2010 :: Create 3 Or 4 Detailed Reports From Worksheet?
Apr 24, 2014I 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 .
Turn Summary Sheet Into Detailed Sheets (multiple Tabs)?
Apr 14, 2014I 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.
Solver- When Solver "changes Cells"
Jan 21, 2009I 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?
If Then Formula That Includes Counting?
Dec 20, 2013I 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 RelatedHow To Look Up A Cell With That Includes A Hyphen (-)
Dec 3, 2008Please 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 RelatedIf / And Formula That Includes Ratios And Words
Jan 30, 2014What 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 Is The Command For Includes A Certain String Of Text
Jun 12, 2009What 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 RelatedIdentify Difference Between A Value That Includes Letters And One That Does Not
Jun 10, 2014I 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?
How To Search For A String That Includes The Symbol
Sep 18, 2009I'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 :
Formula Includes Path And File Name?
May 1, 2014I 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]....
Len Formula That Includes Trailing Zeros
Jun 25, 2007I 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.
Sorting Data That Includes Email Addresses
Jan 16, 2014I 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 Related=WORKDAY But Includes Weekends In The Specified Number Of Days
Nov 24, 2008I'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).
Table That Includes Data From Current And Future Sheets
Jan 14, 2014I 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
Calculating Hours In Rota That Includes Text Fields?
Jan 12, 2012I'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!
Worksheet That Includes A Cell That Gives The Current Week Of The Month
Nov 3, 2009I 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".