Slope for specified range

pssureshbabu

New Member
Joined
Oct 5, 2006
Messages
16
Auswertung_test.xls
ABCDEF
3900.022420467.83310.00540458.28590.01125164.0292
3910.022432968.09580.005841558.49610.011463564.1689
3920.022470468.3060.00607958.68880.011613564.4136
3930.022682968.55120.005991558.88150.01185164.6582
3940.022820468.72630.00632959.09160.012088564.8504
3950.022882968.98910.00655459.28430.01210165.095
3960.023132969.18170.00652959.49450.01235165.3396
3970.023182969.42690.006741559.66970.012588565.5493
3980.023095469.65460.00697959.87990.012613565.7589
3990.023332969.86480.006891560.03750.01280165.9861
4000.023445470.09250.00707960.26520.012963566.1783
4010.023357970.33770.00712960.45790.01300166.3879
4020.023682970.56530.00707960.68560.01307666.6151
4030.023920470.7930.00717960.87830.01312666.8597
4040.023795471.02070.007391561.1060.013138567.0868
4050.024082971.26590.007266561.26360.01325167.3314
4060.024345471.49360.007416561.49130.01320167.611
4070.024232971.72130.00747961.7190.013213567.8905
4080.024520471.93150.00732961.91170.013263568.1526
4090.024745472.21170.00725462.17440.013213568.3972
4100.024645472.42190.00740462.47220.01305168.6244
4110.024895472.70210.00725462.66490.01285168.834
4120.025057972.91230.007241562.85750.012713569.0437
4130.025070473.15750.00737963.12030.01267669.2534
4140.025382973.36760.00740463.29540.012588569.463
4150.025532973.61280.00737963.45310.012738569.7426
4160.025520473.77050.007516563.69830.012888569.9697
4170.025970474.01570.00750463.90850.01297670.1969
4180.026207974.26090.007591564.10120.01305170.4415
4190.026120474.52360.00785464.32890.012988570.721
4200.026607974.76880.008116564.59160.01305170.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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Oct 5, 2006
Messages
16
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!
 

Forum statistics

Threads
1,137,367
Messages
5,681,081
Members
419,950
Latest member
BeckiJae

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top