Slope for specified range

[pssureshbabu]

Auswertung_test.xls
	A	B	C	D	E	F
390	0.0224204	67.8331	0.005404	58.2859	0.011251	64.0292
391	0.0224329	68.0958	0.0058415	58.4961	0.0114635	64.1689
392	0.0224704	68.306	0.006079	58.6888	0.0116135	64.4136
393	0.0226829	68.5512	0.0059915	58.8815	0.011851	64.6582
394	0.0228204	68.7263	0.006329	59.0916	0.0120885	64.8504
395	0.0228829	68.9891	0.006554	59.2843	0.012101	65.095
396	0.0231329	69.1817	0.006529	59.4945	0.012351	65.3396
397	0.0231829	69.4269	0.0067415	59.6697	0.0125885	65.5493
398	0.0230954	69.6546	0.006979	59.8799	0.0126135	65.7589
399	0.0233329	69.8648	0.0068915	60.0375	0.012801	65.9861
400	0.0234454	70.0925	0.007079	60.2652	0.0129635	66.1783
401	0.0233579	70.3377	0.007129	60.4579	0.013001	66.3879
402	0.0236829	70.5653	0.007079	60.6856	0.013076	66.6151
403	0.0239204	70.793	0.007179	60.8783	0.013126	66.8597
404	0.0237954	71.0207	0.0073915	61.106	0.0131385	67.0868
405	0.0240829	71.2659	0.0072665	61.2636	0.013251	67.3314
406	0.0243454	71.4936	0.0074165	61.4913	0.013201	67.611
407	0.0242329	71.7213	0.007479	61.719	0.0132135	67.8905
408	0.0245204	71.9315	0.007329	61.9117	0.0132635	68.1526
409	0.0247454	72.2117	0.007254	62.1744	0.0132135	68.3972
410	0.0246454	72.4219	0.007404	62.4722	0.013051	68.6244
411	0.0248954	72.7021	0.007254	62.6649	0.012851	68.834
412	0.0250579	72.9123	0.0072415	62.8575	0.0127135	69.0437
413	0.0250704	73.1575	0.007379	63.1203	0.012676	69.2534
414	0.0253829	73.3676	0.007404	63.2954	0.0125885	69.463
415	0.0255329	73.6128	0.007379	63.4531	0.0127385	69.7426
416	0.0255204	73.7705	0.0075165	63.6983	0.0128885	69.9697
417	0.0259704	74.0157	0.007504	63.9085	0.012976	70.1969
418	0.0262079	74.2609	0.0075915	64.1012	0.013051	70.4415
419	0.0261204	74.5236	0.007854	64.3289	0.0129885	70.721
420	0.0266079	74.7688	0.0081165	64.5916	0.013051	70.9482
Daten

Hello friends,

I want to find the slope for the specified range in the above data (A:B).

I want to specify the range using let me say I2 reference. (may be 268 is the value in the cell I2)

Then H4 will calculate 30% of 268 and I4 will calculate 70% of 268.

I used the following formula to find the slope of the values within the range 30% - 70%

SLOPE((INDEX(A:A,MATCH($H$4,B:B,1))),(INDEX(B:B,MATCH(I4,B:B,1))))

But #!DIV/0 is the result.

Am I doing something wrong?? Please help me to find the slope within the range I specified.

Thanks,
Babu

 

[Jon von der Heyden]

I contructed this array formula:
=SLOPE(IF(A390:A420>=H4*A390:A420<=I4,A390:A420),IF(B390:B420>=H4*B390:B420<=I4,B390:B420))
confirmed with ctrl+shift+enter

but it didn't either work.

So when I evaluated it further I found that there are no values within range A and B between 30% - 70% of 268. Hence each instance is formulating zero which is why #!DIV/0 is being returned.

Jon

 

[pssureshbabu]

Actually, that was mistake by me.. I have more than 6000 cells for A and B column. I cannot paste all those here.

I reconstructed the formula as below and it works charm

SLOPE((INDEX(B:B;MATCH($H$5;B:B;1)))INDEX(B:B;MATCH($I$5;B:B;1)));(INDEX(A:A;MATCH($H$5;B:B;1)))INDEX(A:A;MATCH($I$5;B:B;1)))

your formula works only if the my data has exact match. But my formula will take the closest value and calculate.

Anyway I appreciate your effort dude, thanks!