Free Microsoft Excel 2013 Quick Reference

# Implementing modular arithmetic as constraint in Solver

Hi,

I'm currently trying to make a code for implementation of a modular arithmetic as constraint for Solver. I'm having a production optimization problem where I need to get the solution in multiples of a given number x from the user input and I have no idea how to code this so it will be accessible for Solver.

Thankful for all help.

## Related Results

### Relational contraints in Solver (i.e. can't pursue project A & B at same time)

I need to know how to develop relational constraints in solver. I am trying to maximize ROI by changing two variables: which project to invest in (yes/no) and how much to invest in each project I pursue. Consider the following:

Project 2 can't be pursued unless Project 1 is
Project 3 can't be pursued unless Project 1 is
Project 4 can't be pursued unless Project 2 is
Project 4 can't be pursued unless Project 3 is
If Project 6 is funded, so MUST Project 7 (and vice-versa)
Projects 5 and 6 cannot be funded simultaneously

I currently have the data set up like this:

Show# Invest (Yes=1,No=0) ROI Amount Invested Total Return
1 1 7% 0
2 1 9% 0
3 1 4% 0
4 1 25% 0
5 1 21% 0
6 1 14% 0
7 1 8% 0
8 1 16% 0

### ListBox MultiSelectMulti implemented as dropdown in LinkedCell

I've built a ListBox able to transfer multiple selections into one cell separated by a comma. This is great.

The list box looks in A1:A41 (one word per cell). The ListBox is MultiSelectMulti and display all selections in B1 (in the same sheet).

I've added code in the VBA for the sheet containing the ListBox that tells it to separate selections by comma, and remove selections from LinkedCell if unchecked in the ListBox.

BUT...
Right now the ListBox is open and placed close to the ListFillRange and the LinkedCell. This is where I built it and this is where the problem begins.

BECAUSE...
The ListBox is built to appear by clicking a dropdown arrow in a selected cell, and then insert the selected values into this cell. Like a normal datavalidation drop down, but with multiple selections separated by comma.
It is supposed to be implemented to every single cell in an entire column. This column is in another workSheet.

HOW..
Do I apply the ListBox to multiple separate cells in another worksheet as a drop down?

My VBA skills are limited, but i'm a fast learner.

Thanks

### Solver with variable as constraint

Dear all,
I have a complex problem, see if you have time to help me solve that.
I am now doing a scheduling problem. I was given an staff requirement for every 15 mins, for example, 00:00-00:15 have a number, 00:15-00:30 have another......However, the job done by the staff can be within 30 mins , meaning that the work done by the staff in 00:00-00:15 can have a feasible period 00:00-00:30 to finish, while 00:15-00:30 can be finished in 00:15-00:45. This pattern will therefore lead staff requirement become variable, say in 00:15-00:30, we dunno the exact no. of staff needed now. Hence, for each period, I have a staff requirement as variable. After I run this, however, I want to create a plan to cover those staff requirement. Hence I use those variable as constraint now and apply those common scheduling method. However, everytime I run that, the cost which has a minimize objective give different solution each time (different cost).
I wanna sort out what happen and any solution can help
You may need to see the attach file to think a while for what I am doing
Thx
Cheers and this would be great help
Bong

### Solver constraints shall be OR // Solver target cell

Hello everyone,

I have a question regarding the constraints in solver.
I want a sum of percentages to add to one. There are 50 parts alltogether. They all shall be between 3% and 5%, or zero.
Is there a way to make Solver take into account those constraints, do I need to use VBA?

On another matter for the sake of convenience I want to create a sheet, where the user can enter the desired target value and just click a button, to run a makro that solves. But for this makro I can just set the target cell to a specific value, and the button will - of course - always just solve for this. As I cannot link the target cell to a cell in the sheet where the user might enter the desired value, I wonder if there is another way to achieve this.

### Ratios in Excel Solver

How do I enter a ratio as a constraint in Solver? My ratio is 4:1, do I
simpy put 4:1?

### How can I set >= and

How can I set >= and <= constraints for 1 variable in SOLVER?

Say I have a group of variable cells F5:F10. How can I set a constraint for those variables that will keep them between say 4 and 8.

### Limiting Constraints in Solver

I'm using solver via VBA to set up a pricing model. I have two variables, once of which can only be 2 possible values, the other can only be 6 possible values, but I am running in to a wall trying to get the constraints right. Any ideas?

### Loop Formula in Solver

Hi,
I need to set a formula as an Objective Function in Solver. The formula is not straight forward as I need to use For Loop to calculate the value in the diagonal of a nxn matrix. For example, I want to find the total of cell(1,2)+cell(2,3)+cell(3,4)+...+ cell(n-1,n)+cell(n,1) and this total will be the objective function in Solver. How do I write the coding in VBA for SolverOk SetCell? Your help would be much appreciated!
Thanks!

### Macro for 4 constraints in autofilter

Hi

Not sure if this is possible, or if I have tackled this the right way so any
help greatly appreciated! I am (trying to) write a macro that will use
multiple constraints (in 1 column) in autofilter, this is so that I don't
have to use the advanced filter option. The result of the macro should return
rows that contain "19" or "20" or "act" or "regul" in the 3rd field, and hide
the rows that don't. My macro that is not working at the moment:

'
Sheets("Agr-Tra").Select
Selection.AutoFilter Field:=3, Criteria1:="=regul*", Operator:=xlOr
Selection.AutoFilter Field:=3, Criteria1:="=act*", Operator:=xlOr
Selection.AutoFilter Field:=3, Criteria1:="=19*", Operator:=xlOr
Selection.AutoFilter Field:=3, Criteria1:="=20*"
End Sub

This is returning 0 rows with the above criteria, which is incorrect as it
should be over 300 rows.

Much appreciated
Gus

### What does error message "too many adjustable cells" mean in solver

I am new to solver, and am trying to find a solution using a column of
numbers. Basically I want to find the sum of 4311.20 in a list of numbers .

My target cell has the formula =sumproduct(actuals,ones) where actuals is
the name of the cells to use for the solution, and ones is for the binary
number.

In solver I am selected the target cell with the formula, value = to
4311.20, by changing cells has the word "ones" for the column with ones in
it, and the constraints are ones=binary. I get the error message "Too many

### Multiple Solver Constraints in Solver

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?

Thanks,
-Simon

### How to create constaints in solver

Need to create a constraint in Excel Solver where the sum of all investments
into an IT project does not exceed a certain % of the total capital budget
for a 5 year period.

### Modular Arithmetic

I don't know if I need to be in Excel programming or another section, but
here it is...

I want to solve a modular arithmetic problem in Excel (so i have been told
by a math professor).

Specifically, I want to solve for t (time) in the present value formula.
However, i (interest) is not constant, otherwise I'd use the log function.

I want (i) to increase, let's say every 2nd, 3rd, or 4th, year, which makes
this problem a modular arithmetic problem.

I've set up a table to determine the value of (t) given my variable (i) but
I was looking for a more direct approach. Any ideas anyone?

### Using VBA to create a variable "adjustable cells" array in Solver

Hi all!

I am creating a certain optimization system in Excel that uses Solver. Around 60 optimizations are carried out using Solver in combination with VBA. During these optimizations, the adjustable cells array varies. Therefore:

I would like to use VBA to determine the array used in Solver as the adjustable cells.

In other words: If cell A1 returns a value of 5, the number of rows used in the array of adjustable cells in Solver is 5. When cell B1 returns a value of 2, the number of columns used in the array of adjustable cells in Solver is 2.

### Excel solver binary variables

Hi guys

I am having a problem in getting excel solver to solve a linear programming problem.
Suppose i have binary variables as follows:
0 1 1 0 1 0 1
I want to track the change between each variable. eg if there's a change of value from one variable to the next then i want to times it by 15 ie (x2-x1)*15.
The problem is i want a positive 15 not negative 15 in some cases.
I tried using absolute value function but it's not linear so i cant use solver with simplex method to solve it.

Note: I can only use solver simplex method to solve this.
The above problem is an extract of a bigger problem with more constraints in solver.

If you have any more questions feel free to ask.

Thanks

### Macro for 4 constraints in autofilter

Hi

Not sure if this is possible, or if I have tackled this the right way so any
help greatly appreciated! I am (trying to) write a macro that will use
multiple constraints (in 1 column) in autofilter, this is so that I don't
have to use the advanced filter option. The result of the macro should return
rows that contain "19" or "20" or "act" or "regul" in the 3rd field, and hide
the rows that don't. My macro that is not working at the moment:

'
Sheets("Agr-Tra").Select
Selection.AutoFilter Field:=3, Criteria1:="=regul*", Operator:=xlOr
Selection.AutoFilter Field:=3, Criteria1:="=act*", Operator:=xlOr
Selection.AutoFilter Field:=3, Criteria1:="=19*", Operator:=xlOr
Selection.AutoFilter Field:=3, Criteria1:="=20*"
End Sub

This is returning 0 rows with the above criteria, which is incorrect as it
should be over 300 rows.

Much appreciated
Gus

### The number of workbooks open as data in VBA

I must obtain as data in VBA, the number of Excel files open at one time or if possible also the list with their names.
Any idea will be welcome!
Thanks!

### Graphic symbols as selections in a list box?

Can you have a list of graphic symbols as selections in a list box? I want basically to allow selection of one of three emoticons for a list box but can only get text to work now. Can this be done? Is there another alternative short of programming?

### Looping Through A Range - Use Result As Criteria In A Filter

Hi there

I am trying to learn how to loop so am starting with a simple example (see attached), hopefully to apply to a larger spreadsheet. I would like to loop through a named range called "Buildings" and use each result as criteria in a filter on another sheet within the workbook.

E.g. The named range has values B1, B2 and B3. I would like to use each of the values in the range to filter the data on the sheet called "data". Each result will be copied and pasted to another workbook and called B1.xls, B2.xls etc. In this case I will have 3 new workbooks with the information for each of the Buildings.

I would like to do this all in VBA.

If somebody can help me I would be grateful.

Thanks

### Paste Visible Formulas Cells as Values in Filtered Table

Hi VBA great helpers,

in the sample worksheet, I need help with a VB code to copy the formulas in column D, to paste them as values in the same cells (without removing autofilter by clicking on menu Data>Filter>Autofilter, then edit>copy>paste special>values).

Thanks in advance for the help.

### Display negative number as zero in vba

Hi
This is driving me crazy - I have to show negative numbers as zero in my VBA program. I can do this in the worksheet by using Format, Cell, Number, Custom and choosing the format #,#00;"0"
However, when I use NumberFormat in my VBA code, it already has to have quotes around it like this:
```
VB:
Worksheets("Fees4").Range("c44:f46").NumberFormat = "#,#00;0"

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

```
So when I try to add my quotes to display the negative number as zero like this
```
VB:
Worksheets("Fees4").Range("c44:f46").NumberFormat = "#,#00;"0""

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

```
it doesn't like it - any suggestions?

### Using SumIF on dates stored as text in pivot table

Morning everybody,

Sorry I couldn't find an answer in the help so I'm starting a new thread.

I have a pivot table and the top row are dates, unhelpfully stored as text values in the form "Feb-05", "Mar-05".

Further down in the table I have another row running parallel to the date row that tracks the mandays allocated to a particular project (each project has it's own row).

I need to sum the mandays left to spend for the year for each project, so in June I need to sum the mandays corresponding to the months June through December, for example.

A SUMIF seems pretty logical but since my dates are stored as text in the pivot table i can't get the criteria to work.

I have limited flexibility with the spreadsheet as i'm trying to help a colleague and he can't change much in the pivot table.

I realize i could use some ugly 12-deep nested IF formula but was hoping for a more elegant solution.

Thanks. :-)

### VBA: Copying a Multiple Selection but paste as Values in sam

Hi,

I hope someone can help me with a code to handle this problem I faced.

Column AW (Order Qty) and column DZ (Order By) are the vlookup formulas that display the result.
The PartNumbers are being filtered that show only selected rows.

How can I selectively copy certain formula cells and paste as values in the same cells again ?

I heard of the "CopyMultipleSelection subroutine" but how to paste in same multiple cells as values only ?

columnA columnAW columnDZ
PartNum. Ord.Qty OrderBy
row228 100-344 223 SIEMENS
row610 10-236W 173 SONY
row997 2202246 454 HITACHI
row2169 2244W 680 PHILIPS
row9907 33490 998 DELL

### Negative Number As Zero In Calculations

How can I have a negative number stored in one cell count as zero for the purposes of a calculation in a different cell? I want to retain the negative number for other calculations, but have it count as zero in another calculation.

Example:

A1 = -3
A2 = A1 * 5

I would like the resultant to be "0", not "-15"