Conditional data selection and calculate count, max and means

nostradamus

Board Regular
Joined
Aug 9, 2010
Messages
143
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have a bit of a complicated if then computational formulas request - not sure what all goes in this.
Background: I have daily records of Flow of water going through the system (col.B), and corresponding dates are in col.A. Col.C has difference in Flow from previous day (DeltaFlow). Col.D is the Flow Log10 transformed and Col.E is the difference from previous day.
Formula Requests:
J2 = Duration of Rise (Number of consecutive days where flow was greater than previous day (use longest streak; if two or more of same length, then use one with higher difference), 30 days up from selected date (match info found in col.F2 & col.G2)
J3 = Duration of Drop (Number of consecutive days where flow was lesser than previous day (use longest streak; if two or more of same length, then use one with higher difference), 30 days up from selected date (match info found in col.F3 & col.G3)
J4 = Magnitude of Rise (max positive flow change in consecutive days, 30 days up from selected date
J5 = Magnitude of Drop (max negative flow change in consecutive days, 30 days up from selected date
J6 = Rate of rise (Means of all positive difference bet. consecutive daily Log10 flow values (colE), 30 days up from selected date
J7 = Rate of drop (Means of all negative difference bet. consecutive daily Log10 flow values (colE), 30 days up from selected date
J8 = Number of flow reversals (count number of times flow values (from col.B) go from up to down and down to up)
CalculationSteps.xlsx
ABCDEFGHIJ
1DateFlowDeltaFlowLog10 FlowLog10 DeltaFlowdaysSelected DatesDescriptionResults
22/7/20161382.140303/13/2016Duration of Rise8
32/8/2016137-12.137-0.003303/13/2016Duration of Drop11
42/9/2016130-72.114-0.023303/13/2016Magnitude of Rise85
52/10/2016127-32.104-0.010303/13/2016Magnitude of Drop12
62/11/2016126-12.100-0.003303/13/2016Rate of rise0.007
72/12/201612712.1040.003303/13/2016Rate of Drop-0.004
82/13/201612702.1040.000303/13/2016Number of Flow reversals7
92/14/2016126-12.100-0.003
102/15/201612602.1000.000
112/16/2016124-22.093-0.007
122/17/201612402.0930.000
132/18/2016123-12.090-0.004
142/19/201612302.0900.000
152/20/2016121-22.083-0.007
162/21/201612322.0900.007
172/22/2016122-12.086-0.004
182/23/2016145232.1610.075
192/24/2016126-192.100-0.061
202/25/201613372.1240.023
212/26/2016132-12.121-0.003
222/27/2016131-12.117-0.003
232/28/2016126-52.100-0.017
242/29/2016124-22.093-0.007
253/1/2016123-12.090-0.004
263/2/2016120-32.079-0.011
273/3/201612002.0790.000
283/4/2016115-52.061-0.018
293/5/201611502.0610.000
303/6/2016114-12.057-0.004
313/7/201611512.0610.004
323/8/201611722.0680.007
333/9/2016139222.1430.075
343/10/2016149102.1730.030
353/11/201615562.1900.017
363/12/201616492.2150.025
373/13/2016199352.2990.084
383/14/2016194-52.288-0.011
393/15/2016182-122.260-0.028
403/16/2016173-92.238-0.022
Sheet1
Cell Formulas
RangeFormula
D2:D40D2=LOG10(B2)
C3:C40,E3:E40C3=B3-B2
J6J6=MEDIAN(IF(E9:E37>=0,E9:E37,""))
J7J7=MEDIAN(IF(E9:E37<=0,E9:E37,""))
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Forum statistics

Threads
1,223,098
Messages
6,170,099
Members
452,301
Latest member
QualityAssurance

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
Back
Top