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.

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:

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?

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

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.

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?

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!

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:

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
adjustable cells".

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?

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.

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.

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:

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?

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.

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).

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

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.