nostradamus
Board Regular
- Joined
- Aug 9, 2010
- Messages
- 143
- Office Version
- 365
- Platform
- 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)
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 | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | |||
1 | Date | Flow | DeltaFlow | Log10 Flow | Log10 DeltaFlow | days | Selected Dates | Description | Results | |||
2 | 2/7/2016 | 138 | 2.140 | 30 | 3/13/2016 | Duration of Rise | 8 | |||||
3 | 2/8/2016 | 137 | -1 | 2.137 | -0.003 | 30 | 3/13/2016 | Duration of Drop | 11 | |||
4 | 2/9/2016 | 130 | -7 | 2.114 | -0.023 | 30 | 3/13/2016 | Magnitude of Rise | 85 | |||
5 | 2/10/2016 | 127 | -3 | 2.104 | -0.010 | 30 | 3/13/2016 | Magnitude of Drop | 12 | |||
6 | 2/11/2016 | 126 | -1 | 2.100 | -0.003 | 30 | 3/13/2016 | Rate of rise | 0.007 | |||
7 | 2/12/2016 | 127 | 1 | 2.104 | 0.003 | 30 | 3/13/2016 | Rate of Drop | -0.004 | |||
8 | 2/13/2016 | 127 | 0 | 2.104 | 0.000 | 30 | 3/13/2016 | Number of Flow reversals | 7 | |||
9 | 2/14/2016 | 126 | -1 | 2.100 | -0.003 | |||||||
10 | 2/15/2016 | 126 | 0 | 2.100 | 0.000 | |||||||
11 | 2/16/2016 | 124 | -2 | 2.093 | -0.007 | |||||||
12 | 2/17/2016 | 124 | 0 | 2.093 | 0.000 | |||||||
13 | 2/18/2016 | 123 | -1 | 2.090 | -0.004 | |||||||
14 | 2/19/2016 | 123 | 0 | 2.090 | 0.000 | |||||||
15 | 2/20/2016 | 121 | -2 | 2.083 | -0.007 | |||||||
16 | 2/21/2016 | 123 | 2 | 2.090 | 0.007 | |||||||
17 | 2/22/2016 | 122 | -1 | 2.086 | -0.004 | |||||||
18 | 2/23/2016 | 145 | 23 | 2.161 | 0.075 | |||||||
19 | 2/24/2016 | 126 | -19 | 2.100 | -0.061 | |||||||
20 | 2/25/2016 | 133 | 7 | 2.124 | 0.023 | |||||||
21 | 2/26/2016 | 132 | -1 | 2.121 | -0.003 | |||||||
22 | 2/27/2016 | 131 | -1 | 2.117 | -0.003 | |||||||
23 | 2/28/2016 | 126 | -5 | 2.100 | -0.017 | |||||||
24 | 2/29/2016 | 124 | -2 | 2.093 | -0.007 | |||||||
25 | 3/1/2016 | 123 | -1 | 2.090 | -0.004 | |||||||
26 | 3/2/2016 | 120 | -3 | 2.079 | -0.011 | |||||||
27 | 3/3/2016 | 120 | 0 | 2.079 | 0.000 | |||||||
28 | 3/4/2016 | 115 | -5 | 2.061 | -0.018 | |||||||
29 | 3/5/2016 | 115 | 0 | 2.061 | 0.000 | |||||||
30 | 3/6/2016 | 114 | -1 | 2.057 | -0.004 | |||||||
31 | 3/7/2016 | 115 | 1 | 2.061 | 0.004 | |||||||
32 | 3/8/2016 | 117 | 2 | 2.068 | 0.007 | |||||||
33 | 3/9/2016 | 139 | 22 | 2.143 | 0.075 | |||||||
34 | 3/10/2016 | 149 | 10 | 2.173 | 0.030 | |||||||
35 | 3/11/2016 | 155 | 6 | 2.190 | 0.017 | |||||||
36 | 3/12/2016 | 164 | 9 | 2.215 | 0.025 | |||||||
37 | 3/13/2016 | 199 | 35 | 2.299 | 0.084 | |||||||
38 | 3/14/2016 | 194 | -5 | 2.288 | -0.011 | |||||||
39 | 3/15/2016 | 182 | -12 | 2.260 | -0.028 | |||||||
40 | 3/16/2016 | 173 | -9 | 2.238 | -0.022 | |||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D2:D40 | D2 | =LOG10(B2) |
C3:C40,E3:E40 | C3 | =B3-B2 |
J6 | J6 | =MEDIAN(IF(E9:E37>=0,E9:E37,"")) |
J7 | J7 | =MEDIAN(IF(E9:E37<=0,E9:E37,"")) |