Solver outputs in exact decimal

Question

I am trying to code a solver in order to solve a 4 unknown equation. 2 of these unknown parameters must be either integers or an EXACT 1 decimal number (127.1 ; 127.2 ; 127.3...). I precise that i do not want to round the value, the solver has to find the exact value (not 127.1234 = 127.1).

the difficulty is on the Exact decimal number, I don't know how to set up this constraint. I have tried with a boolean function :

Function EstValide(Rng As Range) As Boolean
If Round(Rng.Value * 10) = Rng.Value * 10 Then
EstValide = True
End If
End Function

But the solver does not recognize the boolean value of the concerned cell with the relation :=2

solveradd cellref:=Range("B24"), relation:=2, formulatext:=Range("B23") 'test
solveradd cellref:=Range("B25"), relation:=2, formulatext:=Range("B23") 'test

Cells B24 B25 are found with the function and in the cell B23 i have just put "TRUE" because i want my constraint to work when TRUE.

Any Ideas on how i can solve this issue ?

In advance thank you.

Vityata · Answer 1 · 2018-03-29T13:47:01.587

1

So you need to avoid the True/False and to replace it with an Integer value? This is a simple way to achieve it:

Function EstValide(Rng As Range) As Long
    EstValide = Round(Rng.Value2, 0)
End Function

vba uses bank rounding - e.g. it rounds to even number - Why does .NET use banker's rounding as default? See this example:

Sub TestMe()

    Debug.Print Round(5.5, 0)       '6
    Debug.Print Round(6.5, 0)       '6
    Debug.Print Round(7.5, 0)       '8
    Debug.Print Round(8.5, 0)       '8

    With WorksheetFunction
        Debug.Print .Round(5.5, 0)  '6
        Debug.Print .Round(6.5, 0)  '7
        Debug.Print .Round(7.5, 0)  '8
        Debug.Print .Round(8.5, 0)  '9
    End With

End Sub

edited Mar 29 '18 at 13:47

answered Mar 29 '18 at 12:47

Vityata

42,633
8
55
100

you reused OP's Round(), however say no words that it's bank_round, but not school_round. – user6698332 Mar 29 '18 at 13:28
don't mention it :) – user6698332 Mar 29 '18 at 13:38

score 0 · Answer 2 · answered Mar 31 '18 at 16:00

Thanks for your answers.

My problem is not to get integer values. When you code a solver there is a very easy way to say to the solver "I want only integer as solutions" by using the constraint relation:=4

My problem here is to say to the solver "I want to get the exact first decimal number" example: 10.1 10.2 10.3 etc etc but these number must not be rounded. The solver must find the exact value which fits with an exact one decimal number. Example 10.1 and not 10.1234=10.1

It is like the constraint of the integer but design for 1 decimal numbers. And for that I need to set up a function and implement it in my constraint.

Solver outputs in exact decimal

2 Answers2