Finding the equal number of 2 rows of numbers.

[Jesper Greve]

Hi ...

I have 2 linear funktions, i would like to find the point where the intercepts.

eg. B3 = (45 degrees=Income)
Useing the tabel below, cell B3 should return the value 920

The lines (45 degrees) and (income) are createt from 2 data sets relative to the x values.



X value 45 degree Income
700 700 788
710 710 794
720 720 800
730 730 806
740 740 812
750 750 818
760 760 824
770 770 830
780 780 836
790 790 842
800 800 848
810 810 854
820 820 860
830 830 866
840 840 872
850 850 878
860 860 884
870 870 890
880 880 896
890 890 902
900 900 908
910 910 914
920 920 920
930 930 926
940 940 932
950 950 938
960 960 944
970 970 950
980 980 956
990 990 962
1.000 1.000 968
1.010 1.010 974
1.020 1.020 980
1.030 1.030 986
1.040 1.040 992
1.050 1.050 998
1.060 1.060 1.004
1.070 1.070 1.010
1.080 1.080 1.016
1.090 1.090 1.022
1.100 1.100 1.028
1.110 1.110 1.034
1.120 1.120 1.040
1.130 1.130 1.046
1.140 1.140 1.052
1.150 1.150 1.058
1.160 1.160 1.064
1.170 1.170 1.070


Thanks in advance

Best regards

Jesper

 

[Tana Lee]

Aloha...

I'm not a programmer, BUT...

I think you're looking for a function to compare the first column with the second. When they match. compare the results with the third. If THEY match, enter the value in B3.

So if col1=col2, take that value and see where it matches col3.

I don't know if it has to do with 'compare', or 'match' or 'index' or some other kind of lookup function.

This useless info is yours for free.

Tana-Lee

 

[Jesper Greve]

This isn't quite what im looking for, if i have a data series of 2000 entrys it will be a very long formel.

I was hoping 4 something like b2:b2000 = c2:c2000
and it returned B40=920

Best regards

Jesper

 

[bat17]

If you don't mind adding another column then you could have "=AND(A1=B1,A1=C1)" copied down in Column D and then the formula "=INDEX(C1:C2000,MATCH(TRUE,D1:D2000,0),1)" will find the value for you.
I am sure that this could be done in a single step using an array formula but that is beoynd me.

Peter

 

[Aladin Akyurek]

...I am sure that this could be done in a single step using an array formula but that is beoynd me...

The basic logic is the same: A1=C1 ===> $A$1:$A$48=$C$1:$C$48...

=INDEX($B$1:$B$48,MATCH(TRUE,$A$1:$A$48=$C$1:$C$48,0))

which requires confirming with control+shift+enter.

With an additional Index, you can turn this into an ordinary formula:

=INDEX($B$1:$B$48,MATCH(TRUE,INDEX($A$1:$A$48=$C$1:$C$48,0,1),0))

which is not less costly.