How to calculate the maximum slope of a curve in Excel

[zacuk]

Hi,

Please help. How can we calculate the maximum slope in the following data please? An Excel formula would be great. (I prefer to avoid macro, unless it is absolute requirement ). The max slope lies somewhere between rows 10 and 20 in the following example data, but the max range will change from batch to batch. Thanks

[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: center"][/TD]
[TD="class: xl65, width: 64, align: center"][/TD]
[/TR]
[TR]
[TD="class: xl65, align: center"][TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Time (h)[/TD]
[TD="class: xl65, width: 64"]Data[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]1[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]4[/TD]
[/TR]
[TR]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]7[/TD]
[/TR]
[TR]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]9[/TD]
[/TR]
[TR]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]15[/TD]
[/TR]
[TR]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]25[/TD]
[/TR]
[TR]
[TD="class: xl65"]7[/TD]
[TD="class: xl65"]60[/TD]
[/TR]
[TR]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]100[/TD]
[/TR]
[TR]
[TD="class: xl65"]9[/TD]
[TD="class: xl66"]150[/TD]
[/TR]
[TR]
[TD="class: xl65"]10[/TD]
[TD="class: xl66"]220[/TD]
[/TR]
[TR]
[TD="class: xl65"]11[/TD]
[TD="class: xl66"]300[/TD]
[/TR]
[TR]
[TD="class: xl65"]12[/TD]
[TD="class: xl66"]373[/TD]
[/TR]
[TR]
[TD="class: xl65"]13[/TD]
[TD="class: xl66"]448[/TD]
[/TR]
[TR]
[TD="class: xl65"]14[/TD]
[TD="class: xl66"]523[/TD]
[/TR]
[TR]
[TD="class: xl65"]15[/TD]
[TD="class: xl66"]598[/TD]
[/TR]
[TR]
[TD="class: xl65"]16[/TD]
[TD="class: xl66"]673[/TD]
[/TR]
[TR]
[TD="class: xl65"]17[/TD]
[TD="class: xl66"]748[/TD]
[/TR]
[TR]
[TD="class: xl65"]18[/TD]
[TD="class: xl66"]800[/TD]
[/TR]
[TR]
[TD="class: xl65"]19[/TD]
[TD="class: xl66"]850[/TD]
[/TR]
[TR]
[TD="class: xl65"]20[/TD]
[TD="class: xl65"]900[/TD]
[/TR]
[TR]
[TD="class: xl65"]21[/TD]
[TD="class: xl65"]920[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="class: xl65, align: center"][/TD]
[/TR]
</tbody>[/TABLE]

 

[asia1558]

Hi,

Please test my solution.

Excel 2016 (Windows) 64 bit
	A	B	C	D	E	F	G
1	Time (h)	Data		Max slope	80	location	11
2	1	1
3	2	4
4	3	7
5	4	9
6	5	15
7	6	25
8	7	60
9	8	100
10	9	150
11	10	220
12	11	300
13	12	373
14	13	448
15	14	523
16	15	598
17	16	673
18	17	748
19	18	800
20	19	850
21	20	900
22	21	920
Sheet2

Cell Formulas
Range	Formula
E1	{=MAX($B$3:$B$22-$B$2:$B$21)}
G1	{=MATCH(E1,$B$3:$B$22-$B$2:$B$21,0)+1}
Press CTRL+SHIFT+ENTER to enter array formulas.

Asia