"Max-Min difference" from cell range

[Nostradamus]

# question is cross-posted in mrexcel forum #
https://www.mrexcel.com/board/thread...rence.1212573/

I need help with a couple of formulas based on information (cell range/reference) obtained from the cell above.

E2 identifies the number of consecutive days value increased (excluding 0s)
E4 need help with formula: from the identified range of consecutive days of increasing flow in col.B (result in E2), find the difference bet max and min values (you'll need to go one cell above the identified range to catch the starting point) - expected result in F4

E3 identifies the number of consecutive days value decreased (excluding 0s)
E5 need help with formula: from the identified range of consecutive days of decreasing flow in col.B (result in E3), find the difference bet max and min values (you'll need to go one cell above the identified range to catch the starting point) - expected result in F5

Please check link below for the dataset and spreadsheet info

https://www.mrexcel.com/board/thread...rence.1212573/

Thanks!!

[Bernie Deitrick]

It is unclear from your example if 0 changes (which is neither a rise or a fall) should be included in rises as well as falls (as you showed) - I opted for excluding those from the run - it requires a lot more explanation if the pauses should be included in runs or not. It is doable, but you need to explain the logic that you want implemented in the helper column of formulas.

[Nostradamus]

You are right, the 0 doesn't necessarily mean a rise or a drop in flow.
In my actual spreadsheet here (AD2 to AD8) is where I have all the formulas developed.
Specifically, the 0 difference (with a 50cfs buffer) has been added in formulas in cells AD2 and AD3.

I will ultimately have your formulas in cells AD4 and AD5.

Please let me know your thoughts, and I really appreciate you taking the time to help me with this formula!!

[Bernie Deitrick]

I added a helper column in AN, then used formulas in AD in the yellow cells. I don't know where you got your example values in AD, but I came up with something different. I assumed you wanted the X days before the listed date....

Here is the file