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

 

[panyagak]

Hi Eric.

Thank you so much - unreservedly....it worked

 

[Eric W]

You're welcome.

 

[panyagak]

Hi Eric W.

I still cant believe what your solution did to my analysis in terms of simplicity.

Its PURE EARTHLY MAGIC in IT!!!

YOU'RE A "WITCH" & MAGICIAN!!

Good day & God bless

 

[Eric W]

Those are some of the nicer things I've been called!

I wonder if I can get the powers that be here to change MVP to Magician?

I'm happy it worked for you.

 

[panyagak]

Cheers Eric W.

 

[panyagak]

Hi Eric W.

This quizz can only be directed to you & countless thanks for solving it.

Is there a way by a code, to place into 2 separate columns the 2 values solved for the entire set, (eg. 6 & 14: 36 & 14 being 6 & 36 on its column and also 14 & 14)?

Cheers
Patrick

 

[Eric W]

Sure, there are a few ways to do that. We could convert the UDF into a regular macro and have it read down the column and have it place the answers in the columns as it goes. Or we could convert the UDF into an array formula and use Excel's normal function rules to get the various parts. Since that's quicker, I'll do that, but let me know if you want the other way.

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 = Array(i, j, i * a - j * b)
                Exit Function
            End If
        Next j
    Next I
    
    GetDiff = Array("N/A", "N/A", "N/A")
    
End Function

Install as before, or just change the 2 lines starting with "GetDiff = ".

To use it as an array formula:
Excel 2012

	A	B	C	D	E	F	G	H	I
N/A	N/A	N/A	N/A

<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]0.74[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1.56[/TD]
[TD="bgcolor: #FFFFFF"]14[/TD]
[TD="bgcolor: #FFFFFF, align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF, align: right"]0.47[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1.28[/TD]
[TD="bgcolor: #FFFFFF"]36[/TD]
[TD="bgcolor: #FFFFFF, align: right"]14[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]-1[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFFFF, align: right"]74[/TD]
[TD="bgcolor: #FFFFFF, align: right"]156[/TD]
[TD="bgcolor: #FFFFFF"]N/A[/TD]
[TD="bgcolor: #FFFFFF, align: right"]N/A[/TD]

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

[TD="align: center"]4[/TD]
[TD="bgcolor: #FFFFFF, align: right"]47[/TD]
[TD="bgcolor: #FFFFFF, align: right"]128[/TD]
[TD="bgcolor: #FFFFFF"]49[/TD]
[TD="bgcolor: #FFFFFF, align: right"]18[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]-1[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FFFFFF, align: right"]0.235[/TD]
[TD="bgcolor: #FFFFFF, align: right"]1.28[/TD]
[TD="bgcolor: #FFFFFF"]72[/TD]
[TD="bgcolor: #FFFFFF, align: right"]14[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]-1[/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"]G1[/TH]
[TD="align: left"]=INDEX(getdiff(A1,B1,1,1,100),1)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]H1[/TH]
[TD="align: left"]=INDEX(getdiff(A1,B1,1,1,100),2)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]I1[/TH]
[TD="align: left"]=INDEX(getdiff(A1,B1,1,1,100),3)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array 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"]C1:E1[/TH]
[TD="align: left"]{=getdiff(A1,B1,1,1,100)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



There are 2 ways to do it. The first way is to select 3 cells, C1:E1, all at once, enter the formula, and confirm using Control+Shift+Enter. You can then copy and paste the 3 cells down the column.

The second way is to use INDEX to pull out the element from the array you want. The G1:I1 formulas are entered individually in their cells.

The first way is more efficient since it only makes the calculation once, whereas the second way does it 3 times.

Good luck!

 

[panyagak]

IT WORKED!!

There you go again, Eric W.

I"ll never forget your brilliant work.

You never know, I may soon request your Paypal details!!!

God bless you.
Cheers

 

[panyagak]

Eric W.

Good morning.

Thanks so far for "your only solutions".

Maths is endless:

Another 1.00 diff. maths problem. In this case let col. B remain unchanged as Col. A values vary.

Any approach using a CODE?

Eg.
Col. A Col. B Col. C
1.22 1.44 1.22*2-1.44=1.00
0.88 0.76 0.88*2-0.76=1.00
1.02 2.06 1.02*3-2.06=1.00
0.79 1.37 0.70*3-1.37=1.00
0.65 1.60 0.65*4-1.60=1.00
0.59 1.95 0.59*5-1.95=1.00
0.48 1.88 0.48*6-1.88=1.00

Thanks
Patrick

 

[Eric W]

That's just basic algebra:

Excel 2012

	A	B	C	D	E
1.22	1.22*2-1.44=1.00
0.88	0.88*2-0.76=1.00
1.02	1.02*3-2.06=1.00
0.79	0.70*3-1.37=1.00
0.65	0.65*4-1.60=1.00
0.59	0.59*5-1.95=1.00
0.48	0.48*6-1.88=1.00

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

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

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

[TD="align: center"]2[/TD]

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

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

[TD="align: center"]3[/TD]

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

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

[TD="align: center"]4[/TD]

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

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

[TD="align: center"]5[/TD]

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

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

[TD="align: center"]6[/TD]

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

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

[TD="align: center"]7[/TD]

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

[TD="align: right"][/TD]
[TD="align: right"]6[/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"]E1[/TH]
[TD="align: left"]=(1+B1)/A1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]