Anyway to bypass iterative solution?

[diresu]

I have the following relationship

x = (A - B*int(x/C))

Is there a non-iterative way to solve for x? By removing the INTEGER function, I can get quite close by not close enough. This is in a large model and it is a little tedious to wait for recalc which is driving the desire to go non-circular. VBA helps a little only.

Would appreciate any suggestion.

I just joined the group. Looking forward to interacting with everyone.

 

[oldbrewer]

give us some idea of the range and type of numbers eg are A and B integers. range from 0 to 100? especially C

but.... with equation as stated there are infinite solutions eg a=50 c=2 b=5 x=7

 

[diresu]

Sorry, I should have clarified. A, B and C are real number constants. I am struggling to find a non-iterative way to find x because of the INT(x/C) term.

Say, x = 234.5 - 5.6*INT(x/67.8)

 

[oldbrewer]

[TABLE="width: 1158"]
<colgroup><col span="13"><col><col span="4"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]X = 244.5-5.6*int((x/67.8))[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.0000001[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]244.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]67.8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5.6[/TD]
[TD="align: right"]238.9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]135.6[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]11.2[/TD]
[TD="align: right"]233.3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]x is somewhere in the range .0000001 to 2983[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]203.4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]16.8[/TD]
[TD="align: right"]227.7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]271.2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]22.4[/TD]
[TD="align: right"]222.1[/TD]
[TD]bingo[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]339[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]216.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]406.8[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]33.6[/TD]
[TD="align: right"]210.9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]474.6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]39.2[/TD]
[TD="align: right"]205.3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]by inspection X = somewhere in the range 203.4 to 271.2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]542.4[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]44.8[/TD]
[TD="align: right"]199.7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 11"]a simple formula inserts the word "bingo" in the row where X=271.2 (where col 4 FIRST becomes less than col1)[/TD]
[TD="align: right"]610.2[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]50.4[/TD]
[TD="align: right"]194.1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]678[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]188.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]now use an offset/match formula to return 203.4 and 271.2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]745.8[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]61.6[/TD]
[TD="align: right"]182.9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]813.6[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]67.2[/TD]
[TD="align: right"]177.3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]271.2[/TD]
[TD][/TD]
[TD]formula is[/TD]
[TD="colspan: 5"]=OFFSET($R$3,MATCH("bingo",$R$4:$R$48,0),-4)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]881.4[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]72.8[/TD]
[TD="align: right"]171.7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]949.2[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]78.4[/TD]
[TD="align: right"]166.1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]203.4[/TD]
[TD][/TD]
[TD]formula is[/TD]
[TD="colspan: 5"]=OFFSET($R$3,MATCH("bingo",$R$4:$R$48,0)-1,-4)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1017[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]84[/TD]
[TD="align: right"]160.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1084.8[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]89.6[/TD]
[TD="align: right"]154.9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1152.6[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]95.2[/TD]
[TD="align: right"]149.3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1220.4[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]100.8[/TD]
[TD="align: right"]143.7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]clearly the value of int(X/67.8) is a constant 3 for this range of X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1288.2[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]106.4[/TD]
[TD="align: right"]138.1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1356[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]112[/TD]
[TD="align: right"]132.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1423.8[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]117.6[/TD]
[TD="align: right"]126.9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]so the formula becomes X = 244.5 - 5.6 *3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1491.6[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]123.2[/TD]
[TD="align: right"]121.3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1559.4[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]128.8[/TD]
[TD="align: right"]115.7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]= 244.5 - 16.8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1627.2[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]134.4[/TD]
[TD="align: right"]110.1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1695[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]140[/TD]
[TD="align: right"]104.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]227.7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1762.8[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]145.6[/TD]
[TD="align: right"]98.9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1830.6[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]151.2[/TD]
[TD="align: right"]93.3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1898.4[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]156.8[/TD]
[TD="align: right"]87.7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1966.2[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]162.4[/TD]
[TD="align: right"]82.1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2034[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]168[/TD]
[TD="align: right"]76.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2101.8[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]173.6[/TD]
[TD="align: right"]70.9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2169.6[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]179.2[/TD]
[TD="align: right"]65.3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2237.4[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]184.8[/TD]
[TD="align: right"]59.7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2305.2[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]190.4[/TD]
[TD="align: right"]54.1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2373[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]196[/TD]
[TD="align: right"]48.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2440.8[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]201.6[/TD]
[TD="align: right"]42.9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2508.6[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]207.2[/TD]
[TD="align: right"]37.3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2576.4[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]212.8[/TD]
[TD="align: right"]31.7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2644.2[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]218.4[/TD]
[TD="align: right"]26.1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2712[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]224[/TD]
[TD="align: right"]20.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2779.8[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]229.6[/TD]
[TD="align: right"]14.9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2847.6[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]235.2[/TD]
[TD="align: right"]9.3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2915.4[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]240.8[/TD]
[TD="align: right"]3.7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2983[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]240.8[/TD]
[TD="align: right"]3.7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[oldbrewer]

[TABLE="width: 998"]
<colgroup><col><col><col><col><col><col><col><col span="6"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]please enter values for A, B and C below[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]X = 244.5-5.6*int((x/67.8))[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD="align: right"]244.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]B[/TD]
[TD="align: right"]5.6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]generic[/TD]
[TD="colspan: 2"]X = A - B * int (( X / C))[/TD]
[TD][/TD]
[TD][/TD]
[TD]C[/TD]
[TD="align: right"]67.8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]int (( X / C)) must be 1, 2, 3, …….N[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]min X[/TD]
[TD]max X[/TD]
[TD]INT VALUE[/TD]
[TD]A-B*int((X/C))[/TD]
[TD]is X in this range[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]67.8[/TD]
[TD="align: right"]135.599999990[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]238.9[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]135.6[/TD]
[TD="align: right"]203.399999990[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]233.3[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]203.4[/TD]
[TD="align: right"]271.199999990[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]227.7[/TD]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]271.2[/TD]
[TD="align: right"]338.999999990[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]222.1[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]339[/TD]
[TD="align: right"]406.799999990[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]216.5[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]406.8[/TD]
[TD="align: right"]474.599999990[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]210.9[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]474.6[/TD]
[TD="align: right"]542.399999990[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]205.3[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]542.4[/TD]
[TD="align: right"]610.199999990[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]199.7[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]610.2[/TD]
[TD="align: right"]677.999999990[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]194.1[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]678[/TD]
[TD="align: right"]745.799999990[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]188.5[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]745.8[/TD]
[TD="align: right"]813.599999990[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]182.9[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]813.6[/TD]
[TD="align: right"]881.399999990[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]177.3[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]881.4[/TD]
[TD="align: right"]949.199999990[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]171.7[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]949.2[/TD]
[TD="align: right"]1016.999999990[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]166.1[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1017[/TD]
[TD="align: right"]1084.799999990[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]160.5[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1084.8[/TD]
[TD="align: right"]1152.599999990[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]154.9[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1152.6[/TD]
[TD="align: right"]1220.399999990[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]149.3[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1220.4[/TD]
[TD="align: right"]1288.199999990[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]143.7[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1288.2[/TD]
[TD="align: right"]1355.999999990[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]138.1[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1356[/TD]
[TD="align: right"]1423.799999990[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]132.5[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1423.8[/TD]
[TD="align: right"]1491.599999990[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]126.9[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1491.6[/TD]
[TD="align: right"]1559.399999990[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]121.3[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1559.4[/TD]
[TD="align: right"]1627.199999990[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]115.7[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1627.2[/TD]
[TD="align: right"]1694.999999990[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]110.1[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1695[/TD]
[TD="align: right"]-0.000000010[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]104.5[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]solution is[/TD]
[TD]227.7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 5"]So you just put in the values for A, B and C and the solution appears in cell C40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[diresu]

Thanks oldbrewer.

This is what I have, a time series in a fairly bulky workbook:
[TABLE="width: 414"]
<colgroup><col><col span="4"></colgroup><tbody>[TR]
[TD]A (random number)
[/TD]
[TD="align: right"]483[/TD]
[TD="align: right"]533[/TD]
[TD="align: right"]403[/TD]
[TD="align: right"]420[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]x
[/TD]
[TD="align: right"]456[/TD]
[TD="align: right"]503[/TD]
[TD="align: right"]382[/TD]
[TD="align: right"]399[/TD]
[/TR]
</tbody>[/TABLE]

x: = A - B*INT(offset("R1C2",3,0)/C) etc. I have tried rearranging without the INT for an approximation which does not come close enough, but circular takes too long to recalc. It looks like iterations are unavoidable. Many thanks. I think I will try your approach with a user defined function to see if that would speed things up.

 

[oldbrewer]

so you are offsetting from 483, down 3 to 456 which is labelled x - I thought we didn't know x - is it another variable in an int() situation?

 

[diresu]

It was circular, x was unknown to begin with. So, say cell "B4" was the first x value, I initially tried "B4" =A -B*INT("B4"/C), it gave me #NUM. So, I tried offset and it worked. Then I reverted to "B4"=A-B*INT("B4"/C) and it accepted the change without complaint. I thought if all failed, I would create "B5" = A-B*INT("B4"/C) and make "B4" = "B5". Doing it this way did give me the solutions, but just very slow in recalc. Your suggestion is very good for a function with a first approximation range and then interpolate, still very much iterative, but seems to do it reasonably quickly. Probably no hope for finding a simple algebraic method. Thank you.