Looking for 1.00 difference in 2 columns

[panyagak]

Hi.

Let me be brief:
I have values in 2 columns B & C.
Column A has 1,2,3,4,5,6,7,8.....to any number.

I need a code to multiply column A values with column B & C values separately with both resultant values substracting from each other until a 1.00 difference is detected.

Example (a): 0.74 and 1.56
14*0.74=10.36
6*1.56=9.36
Difference=1.00

Example (b): 0.47 and 1.28
36*0.47=16.92
14*1.28=17.92
Diff=1.00.

I did these 2 manually. Suppose I have over 1,000 of these?

Please HELP - my shoulders are aching.

Patrick

 

[Eric W]

As stated, you're basically looking for a brute force method to solve a Diophantine equation. You can set this up fairly easily using the Excel Solver. The Solver may not find the smallest values though, since this type of problem typically has an infinity of solutions (or none). Put .74 in A1, 1.56 in A2, 0 in B1:B2, and =SUMPRODUCT(A1:A2,B1:B2)-1 in C1. Then set up the Solver setting C1 to 0, by varying B1:B2, with constraints B1:B2 = integer, B1:B2 > -100, B1:B2 < 100, and let it rip.

However, this is a pretty basic problem from beginning Number Theory. There is a straightforward method to solving these kind of equations. I'm a bit rusty on this kind of thing, but do a web search for Diophantine equations, or Extended Euclidean Algorithm.

Hope this helps.

 

[panyagak]

thats sounds Advanced maths; Masters/PhD!! am scratching my hair / the back of head

 

[panyagak]

please i studied accounting not Maths in Campus....i little bit of elaboration is necessary

 

[Eric W]

Advanced math, sure. Masters or PhD level, no. I took this in my first year of college. Which raises the question: why do you need this? This is a very specific mathematical question, with limited uses outside of a mathematical realm. Depending on what you need it for, there may be a better way to accomplish your goal.

I happened to have a program that calculates the Extended Euclidean algorithm. With a few tweaks, I turned it into a function that calculates the numbers you want. Initial answer to your first question is -950 and 450, answer to the second example is 4900, -1800. It is possible to do some more manipulation to get those numbers smaller, but they are valid answers out of an infinity of answers. 36 and -14 works for your second example, which can be derived from 4900, -1800.

I don't have time to work through the math to figure out the "best" answer out of billions, I don't even know the specifics of your problem. With what I do know, I'd still go with the Solver. Set up a new spreadsheet like this:

	A	B	C	D

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]0.74[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1.56[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C1[/TH]
[TD="align: left"]=ABS(A1*B1-A2*B2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D1[/TH]
[TD="align: left"]=ABS(B1)+ABS(B2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Put your 2 values in A1:A2, zeros in B1:B2, and the formulas as shown in C1:D1.

Make sure you have the Solver installed. It should be on the far right of the Data tab. If it's not there, go to File > Options > Add-ins > click "Go" next to Excel Add-ins on the bottom > check Solver Add-in > check OK.

Now click Solver, and enter these parameters:

Set Objective: $D$1
To: Min
By changing Variable Cells: $B$1:$B$2
Subject to the Constraints:
$B$1:$B$2<=100
$B$1:$B$2=integer
$B$1:$B$2>=0
$C$1=1

Then click Solve. It gets the exact answers you manually created for both your examples.

 

[panyagak]

Dear

Eric W
MrExcel MVP.

I just cannot believe it would be this technical & answers fewer: that you're the ONLY ONE who has replied TWICE!!!!

I'll surely try out your solution tomorrow.

Thanks & God Bless

regards
Patrick

 

[panyagak]

hi Eric W.

Kindly advise.

suppose i have 400 sets of these values in 2 columns in the formatt 0.74 in B2 & 1.56 in C2 plus all tbe 399 remaining pairs down there.

NB: DECIMALS NEED NOT BE THERE. We can as well have 74 & 156 - no problem.

I pre-suppose your solution would be manual:inputting one by one.

Thanks

Patrick

 

[Eric W]

The harder the problem, the fewer the responders. Your problem requires a very specific set of skills, and not a lot of people have them. There are probably some people who could create a brute-force type solution, but it depends on whether they even see the problem and are willing to spend the time on an uncertain solution.

And I'm still puzzled as to what the purpose of this is? Exactly 400 problems, and decimals don't matter? And why is someone with an Accounting background being asked to do an advanced number theoretical problem?

And it's entirely possible that using or not using the decimals could affect the answer. For example, it's not possible that you can solve 74x - 156y = +-1 in integers, but .74x - 1.56y =+-1 does have an answer.


If I get some more time, I'll take another look at this. I could write a VBA macro that performs the Solver tool 400 times, but it probably would be better to write a UDF that does some kind of brute-force algorithm (I don't have time to really analyze it for optimal design). But to even do that much, I'd need more in the way of requirements. Do you want positive values for x and y, like your examples, or is 1 positive/1 negative OK? Are there upper boundaries to x and y, like 1000? Do you really want decimals or not, or why is or is not that an issue?

 

[panyagak]

Thanks Eric.

Have read your reply with surprise & laughter/awe, because it didnt cross my mind at any time that this would an elephant in the room!

Second, your mathematical mind is up there!: I believe like mine before joining campus- I scored "A plain" in both Primary & Secondary Schools hence my preoccupation with "Creating from scratch a mathematical approach to solving numerical problems around us, with NO reference/past-example/manual/pedia on Earth!!!

Third, may be I can send you a true sample of the data set, you can advise how best to send.

Fourth, only "positives" count in this case.

There is no ceiling above which you cannot stretch the solution process AS LONG AS the "Exact 1.00 difference" is spotted in the first instance & it DOESNT HAVE TO - NO...Out of 400, 100 with +-1.00 diff. would be perfect!!!.....1000 limit is too far & even clutter!! Have not gone that far...100 is better BUT NB:

what determines the limit depends on either value: the smaller the higher the you go & V V. eg
0.47(36) & 1.28(14)=+-1.00. If it were 0.235 vs 1.28, it would have been 0.235(72) & 1.28(14)=+-1.00.......etc but the key point is immediately the 1st +-1.00 is spotted, stop the operation & display the results as shown in these 2 examples above.

End Sub.

Good night.

Patrick in Nairobi Kenya.

 

[Eric W]

I haven't had time to analyze this mathematically to find the optimal algorithm, but it sounds like you may be content with a brute force approach.

Open a copy of your workbook. Press Alt-F11 to open the VBA editor. From the menu, click Insert > Module. On the sheet that opens, paste this code:

Public Function GetDiff(ByVal a, ByVal b, ByVal d, ByVal LL, ByVal UL)
Dim i As Long, j As Long

    For i = LL To UL
        For j = LL To UL
            If Round(Abs(i * a - j * b), 10) = d Then
                GetDiff = a & " * " & i & " - " & b & " * " & j & " = " & i * a - j * b
                Exit Function
            End If
        Next j
    Next I
    
    GetDiff = "No answer found"
    
End Function

Now press Alt-Q to close the editor. Now you can use that function in your worksheet like this:

Excel 2012

	B	C	D	E
0.74 * 14 - 1.56 * 6 = 1
0.47 * 36 - 1.28 * 14 = -1
No answer found
47 * 49 - 128 * 18 = -1
0.235 * 72 - 1.28 * 14 = -1

<colgroup><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]0.74[/TD]
[TD="align: right"]1.56[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]0.47[/TD]
[TD="align: right"]1.28[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]74[/TD]
[TD="align: right"]156[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]128[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]0.235[/TD]
[TD="align: right"]1.28[/TD]

[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D1[/TH]
[TD="align: left"]=Getdiff(B1,C1,1,1,100)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Put the formula in D1 and drag down. The parameters in the function are:

The first number
The second number
The difference you want to find
The lower limit of the integer range you want to test
The upper limit of the integer range you want to test

I used 1-100 in this example, and it found answers to 4 out of 5 of the test cases you proposed. If you don't get an answer, you can change the lower/upper limits. HOWEVER, the higher you raise the limits, the longer it may take, and your sheet may bog down entirely. ALSO, some answers will NOT have a solution! No matter how high you raise the limits, the problem in row 3 will never have an answer. I have analyzed that particular problem, and that's pretty easy to prove. So don't think that if you raise the limits high enough you'll get an answer, because you won't.

I have a program that uses the Extended Euclidean Algorithm to show immediately which problems have an answer or not, but I haven't had time to adapt it to your problem. Hopefully, the UDF provided will be sufficient for your needs.