How to automatically calculate the "max earning per hour" (or the Max slope of a curve)

[zacuk]

Hi,

How can we make Excel automatically calculate the maximum slope in the following type of data, please? For example, the max earning per hour (or the Max slope) in the following data is $20/hour. Note: the earnings per hour change every day.

[TABLE="width: 128"]
<tbody>[TR]
[TD="width: 64, align: center"]Time
(h)[/TD]
[TD="width: 64, align: center"]Total Earning
($)[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]20[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]30[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]40[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]60[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]80[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]100[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]120[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]140[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]160[/TD]
[/TR]
</tbody>[/TABLE]

Thanks

 

[63falcondude]

Maybe this? (Assuming that your data is from A1 to B12 with headers in row 1).

=MAX(B3:B12-B2:B11) Ctrl Shift Enter

 

[cerfani]

{=MAX(IFERROR((B2:B12-B1:B11)/(A2:A12-A1:A11),0))}

Use ctrl+shift+enter to enter as array formula

try something like that (not tested)... you just calculate the slope for every value, the formula relies on the list to be sorted and since i calcualte the difference in hours you dont need sequential hours

 

[oldbrewer]

[TABLE="width: 751"]
<colgroup><col><col><col span="9"></colgroup><tbody>[TR]
[TD]Time[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD](h)[/TD]
[TD]Total Earning[/TD]
[TD][/TD]
[TD][/TD]
[TD]helper[/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]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD="colspan: 4"]this is just current minus previous[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD="colspan: 3"]as you go up in 1 hour intervals[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]40[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]60[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD="colspan: 3"]just take max(E5:E14)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]80[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]120[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]140[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD="colspan: 4"]can the hours go up in 0.5 hour intervals ?[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]160[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]20[/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]
[/TR]
[TR]
[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]
[/TR]
[TR]
[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]

 

[zacuk]

Hi Cerfani, this looks pretty good. I have modified the array formula to fit into my Excel template. I have also made it calculate 3-point average:

{=MAX(IFERROR((BM14:BM20-BM12:BM18)/(C14:C20-C12:C18),0))}

At the moment, we have specified which rows to look at. As the numbers change from day to day and the 'peak' or 'max' rate may be outside this range (Rows 12 to 20), is it possible to improve the formula to start calculating from any row as soon as the numbers have exceeded 10 times the initial value, within Rows 10 to 50.

So, for example, in the following case, I would like the formula to start calculating from Row#6 (i.e., from 5 h onward):

[TABLE="class: cms_table, width: 128"]
<tbody>[TR]
[TD="width: 64, align: center"]Time
(h)[/TD]
[TD="width: 64, align: center"]Total Earning
($)[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]20[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]50[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]80[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]105[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]120
and so on[/TD]
[/TR]
</tbody>[/TABLE]

Thanks

 

[zacuk]

Hi OldBrewer, yes the hours can go up on 0.5 hour intervals. Thanks