Let Excel Solver Operate with Natural Numbers

Question

My workbook is a bit complicated, but the basic problem can be illustrated with a short example.

Let's assume that I have 5 cells in Excel: A, B, C, D, and E.

A = 0
B = 5 * A 
C = 0 
D = 10 * C
E = B + D

In the Excel Solver Function I select cell E as the objective that is to be maximised, and cells A and C as the variable cells. Furthermore, I add the constraint that cell E must not exceed 10, and the second constraint that cells A and C must be integers.

The ideal solution would be that the value in cell A should be 0, and the value in cell C should be 10. In the more complicated version of this problem, however, Excel cannot find the optional solution.

The way the current formulas look like I expect that Excel could find the right solution, if excel only used natural numbers to find the optimal solution. For example, Excel currently would look at the outcome for A = 0.01 and C = 9.99. Instead, Excel should strictly compare outcomes for variable choices such as A = 1 and C = 9.

How can I make the Excel solver function operate with natural numbers only?

Grade 'Eh' Bacon Grade 'Eh' Bacon · Accepted Answer · 2015-07-23T17:47:52

I suggest you keep your current cells, and create a mirror set beside them. Each mirror cell will equal the rounded version of the original cell. ie: RoundedA will have the formula:

=ROUND(A,0)

Then when you do your data analysis, solve for the rounded version of those cells, with the changing variable being one of the "original" cells.

EDIT As discussed below, you may need to 'trick' data validation into creating the values you want.

Assume A1 is going to be your "testing" data validation cell. B1 is a permanently blank cell. Set another cell, say, C1, equal to:

=randbetween(1,10)

This will create a random integer between 1 and 10. Set your "variable" cell to be equal to C10. So, your variable cell will always be a random number between 1 & 10 (or you could set "1" equal to the smallest number of your other variables, and "10" equal to the largest number of your other variables. This will create the scope needed to answer your question).

Then when you do data validation, make it try to get A1 = TRUE. Do this by figuring out what the 'test' condition is of your cells. Something like "when X = 0, I know that all my variables are correct". ie set A1 to:

=if(X=0,1,0)

Then do data validation by changing B1 (your blank, unrefered-to cell), waiting until A1 = 1. Does that make sense? It will turn C1 into the random cycling variable between LOWVALUE and HIGHVALUE, always using integers. Data validation will stop when A1 = 1 (which happens when some value X = 0). B1 will just spin uselessly until Data validation finds something (or it will stop after a few hundred tries if it finds nothing).

Let Excel Solver Operate with Natural Numbers

2 Answers