Forum Discussion
Solved
Lookup and Iterate
I have a dataset that i would like to use to lookup but I need to iterate between set values such as 1:03 that falls between 1:00 and 1:05 AFTER my time of concentration is greater than 1 hr. Any help would be greatly appreciated.
| D= | 0.48 | Duration of rainfall event (hours) |
| SI= | 0.02 | Slope of longest flow path (elevation difference between 10% and 85% of path length, divided by .75L) |
| Tc= | 00:29 | Time of concentration (This is the cell that is looked up on the table Column A) |
| *Tc is calculated using equation that assumes grass and aggregate-lined roads and ditches | ||
| Recurrence Interval | (Intensity) first lookup value =LOOKUP(B19,'Riley County Rainfall Intensity'!A2:A117,'Riley County Rainfall Intensity'!B2:B117) | (Discharge) |
| (inches/hr) | Qt (cfs) | |
| 2 year | 2.54 | 132 |
| 5 year | 3.19 | 265 |
| 10 year | 3.75 | 390 |
| 25 year | 4.56 | 598 |
| 50 year | 4.86 | 698 |
| 100 year | 5.89 | 919 |
Table Referenced
| Time | 2yr | 5yr | 10yr | 25yr | 50yr | 100yr |
| 00:05 | 5.79 | 7.26 | 8.53 | 10.37 | 11.85 | 13.38 |
| 00:06 | 5.35 | 6.71 | 7.89 | 9.58 | 10.95 | 12.36 |
| 00:07 | 5 | 6.27 | 7.37 | 8.95 | 10.23 | 11.55 |
| 00:08 | 4.71 | 5.9 | 6.94 | 8.43 | 9.63 | 10.88 |
| 00:09 | 4.46 | 5.59 | 6.57 | 7.98 | 9.12 | 10.3 |
| 00:10 | 4.24 | 5.31 | 6.25 | 7.59 | 8.67 | 9.79 |
| 00:11 | 4.04 | 5.07 | 5.96 | 7.24 | 8.27 | 9.34 |
| 00:12 | 3.86 | 4.85 | 5.7 | 6.92 | 7.91 | 8.93 |
| 00:13 | 3.71 | 4.65 | 5.47 | 6.65 | 7.59 | 8.57 |
| 00:14 | 3.57 | 4.48 | 5.26 | 6.4 | 7.31 | 8.25 |
| 00:15 | 3.44 | 4.32 | 5.08 | 6.17 | 7.05 | 7.96 |
| 00:16 | 3.34 | 4.19 | 4.93 | 5.99 | 6.84 | 7.72 |
| 00:17 | 3.25 | 4.08 | 4.79 | 5.82 | 6.65 | 7.51 |
| 00:18 | 3.16 | 3.97 | 4.67 | 5.67 | 6.47 | 7.31 |
| 00:19 | 3.09 | 3.87 | 4.55 | 5.53 | 6.31 | 7.13 |
| 00:20 | 3.01 | 3.78 | 4.44 | 5.4 | 6.16 | 6.96 |
| 00:21 | 2.94 | 3.69 | 4.34 | 5.27 | 6.02 | 6.8 |
| 00:22 | 2.88 | 3.61 | 4.24 | 5.15 | 5.89 | 6.65 |
| 00:23 | 2.81 | 3.53 | 4.15 | 5.04 | 5.76 | 6.5 |
| 00:24 | 2.75 | 3.46 | 4.06 | 4.94 | 5.64 | 6.37 |
| 00:25 | 2.7 | 3.39 | 3.98 | 4.84 | 5.53 | 6.24 |
| 00:26 | 2.64 | 3.32 | 3.9 | 4.74 | 5.42 | 6.12 |
| 00:27 | 2.59 | 3.25 | 3.83 | 4.65 | 5.31 | 6 |
| 00:28 | 2.54 | 3.19 | 3.75 | 4.56 | 5.21 | 5.89 |
| 00:29 | 2.49 | 3.13 | 3.68 | 4.48 | 5.12 | 5.78 |
| 00:30 | 2.44 | 3.07 | 3.61 | 4.39 | 5.02 | 5.68 |
| 00:31 | 2.4 | 3.02 | 3.55 | 4.32 | 4.94 | 5.58 |
| 00:32 | 2.36 | 2.97 | 3.49 | 4.25 | 4.86 | 5.49 |
| 00:33 | 2.32 | 2.92 | 3.43 | 4.18 | 4.78 | 5.41 |
| 00:34 | 2.28 | 2.87 | 3.38 | 4.11 | 4.71 | 5.32 |
| 00:35 | 2.25 | 2.82 | 3.32 | 4.05 | 4.63 | 5.24 |
| 00:36 | 2.21 | 2.78 | 3.27 | 3.98 | 4.56 | 5.16 |
| 00:37 | 2.18 | 2.73 | 3.22 | 3.92 | 4.49 | 5.09 |
| 00:38 | 2.14 | 2.69 | 3.17 | 3.86 | 4.42 | 5.01 |
| 00:39 | 2.11 | 2.65 | 3.12 | 3.8 | 4.36 | 4.94 |
| 00:40 | 2.08 | 2.61 | 3.07 | 3.75 | 4.3 | 4.87 |
| 00:41 | 2.05 | 2.57 | 3.03 | 3.69 | 4.23 | 4.8 |
| 00:42 | 2.02 | 2.53 | 2.98 | 3.64 | 4.17 | 4.73 |
| 00:43 | 1.99 | 2.49 | 2.94 | 3.59 | 4.12 | 4.67 |
| 00:44 | 1.96 | 2.46 | 2.9 | 3.54 | 4.06 | 4.61 |
| 00:45 | 1.93 | 2.42 | 2.86 | 3.49 | 4 | 4.54 |
| 00:46 | 1.9 | 2.39 | 2.82 | 3.44 | 3.95 | 4.48 |
| 00:47 | 1.88 | 2.36 | 2.78 | 3.39 | 3.9 | 4.43 |
| 00:48 | 1.85 | 2.32 | 2.74 | 3.35 | 3.85 | 4.37 |
| 00:49 | 1.82 | 2.29 | 2.7 | 3.31 | 3.8 | 4.31 |
| 00:50 | 1.8 | 2.26 | 2.67 | 3.26 | 3.75 | 4.26 |
| 00:51 | 1.78 | 2.23 | 2.63 | 3.22 | 3.7 | 4.21 |
| 00:52 | 1.75 | 2.2 | 2.6 | 3.18 | 3.66 | 4.16 |
| 00:53 | 1.73 | 2.17 | 2.57 | 3.14 | 3.61 | 4.11 |
| 00:54 | 1.71 | 2.15 | 2.53 | 3.1 | 3.57 | 4.06 |
| 00:55 | 1.69 | 2.12 | 2.5 | 3.06 | 3.52 | 4.01 |
| 00:56 | 1.67 | 2.09 | 2.47 | 3.03 | 3.48 | 3.96 |
| 00:57 | 1.65 | 2.07 | 2.44 | 2.99 | 3.44 | 3.92 |
| 00:58 | 1.63 | 2.04 | 2.41 | 2.95 | 3.4 | 3.87 |
| 00:59 | 1.61 | 2.02 | 2.38 | 2.92 | 3.36 | 3.83 |
| 01:00 | 1.59 | 2 | 2.36 | 2.89 | 3.32 | 3.78 |
| 01:05 | 1.51 | 1.89 | 2.24 | 2.74 | 3.16 | 3.6 |
| 01:10 | 1.44 | 1.8 | 2.13 | 2.61 | 3.01 | 3.43 |
| 01:15 | 1.37 | 1.72 | 2.03 | 2.5 | 2.88 | 3.28 |
| 01:20 | 1.31 | 1.65 | 1.94 | 2.39 | 2.75 | 3.14 |
| 01:25 | 1.26 | 1.58 | 1.86 | 2.29 | 2.64 | 3.02 |
| 01:30 | 1.21 | 1.51 | 1.79 | 2.2 | 2.54 | 2.9 |
| 01:35 | 1.16 | 1.46 | 1.72 | 2.12 | 2.45 | 2.79 |
| 01:40 | 1.12 | 1.4 | 1.66 | 2.04 | 2.36 | 2.69 |
| 01:45 | 1.08 | 1.35 | 1.6 | 1.97 | 2.28 | 2.6 |
| 01:50 | 1.04 | 1.31 | 1.55 | 1.91 | 2.2 | 2.52 |
| 01:55 | 1.01 | 1.27 | 1.5 | 1.85 | 2.13 | 2.44 |
| 02:00 | 0.98 | 1.23 | 1.45 | 1.79 | 2.07 | 2.37 |
| 02:05 | 0.95 | 1.19 | 1.41 | 1.74 | 2.01 | 2.3 |
| 02:10 | 0.92 | 1.16 | 1.37 | 1.69 | 1.96 | 2.24 |
| 02:15 | 0.9 | 1.13 | 1.34 | 1.65 | 1.91 | 2.18 |
| 02:20 | 0.87 | 1.1 | 1.3 | 1.6 | 1.86 | 2.13 |
| 02:25 | 0.85 | 1.07 | 1.27 | 1.56 | 1.81 | 2.08 |
| 02:30 | 0.83 | 1.04 | 1.24 | 1.53 | 1.77 | 2.03 |
| 02:35 | 0.81 | 1.02 | 1.21 | 1.49 | 1.73 | 1.98 |
| 02:40 | 0.79 | 1 | 1.18 | 1.46 | 1.69 | 1.94 |
| 02:45 | 0.77 | 0.97 | 1.15 | 1.42 | 1.65 | 1.9 |
| 02:50 | 0.76 | 0.95 | 1.13 | 1.39 | 1.62 | 1.86 |
| 02:55 | 0.74 | 0.93 | 1.1 | 1.36 | 1.58 | 1.82 |
| 03:00 | 0.73 | 0.91 | 1.08 | 1.34 | 1.55 | 1.78 |
| 03:15 | 0.68 | 0.86 | 1.02 | 1.26 | 1.46 | 1.68 |
| 03:30 | 0.65 | 0.81 | 0.97 | 1.19 | 1.39 | 1.59 |
| 03:45 | 0.61 | 0.77 | 0.92 | 1.13 | 1.32 | 1.52 |
| 04:00 | 0.58 | 0.74 | 0.87 | 1.08 | 1.26 | 1.44 |
| 04:15 | 0.56 | 0.7 | 0.83 | 1.03 | 1.2 | 1.38 |
| 04:30 | 0.53 | 0.67 | 0.8 | 0.99 | 1.15 | 1.32 |
| 04:45 | 0.51 | 0.65 | 0.77 | 0.95 | 1.1 | 1.27 |
| 05:00 | 0.49 | 0.62 | 0.74 | 0.91 | 1.06 | 1.22 |
| 05:15 | 0.47 | 0.6 | 0.71 | 0.88 | 1.02 | 1.17 |
| 05:30 | 0.46 | 0.58 | 0.68 | 0.85 | 0.98 | 1.13 |
| 05:45 | 0.44 | 0.56 | 0.66 | 0.82 | 0.95 | 1.09 |
| 06:00 | 0.43 | 0.54 | 0.64 | 0.79 | 0.92 | 1.05 |
| 06:30 | 0.4 | 0.51 | 0.6 | 0.74 | 0.86 | 0.99 |
| 07:00 | 0.38 | 0.48 | 0.57 | 0.7 | 0.81 | 0.93 |
| 07:30 | 0.36 | 0.45 | 0.54 | 0.66 | 0.77 | 0.88 |
| 08:00 | 0.34 | 0.43 | 0.51 | 0.63 | 0.73 | 0.84 |
| 08:30 | 0.32 | 0.41 | 0.49 | 0.6 | 0.7 | 0.8 |
| 09:00 | 0.31 | 0.39 | 0.47 | 0.57 | 0.66 | 0.76 |
| 09:30 | 0.3 | 0.38 | 0.45 | 0.55 | 0.63 | 0.72 |
| 10:00 | 0.29 | 0.36 | 0.43 | 0.53 | 0.61 | 0.69 |
| 10:30 | 0.27 | 0.35 | 0.41 | 0.51 | 0.58 | 0.66 |
| 11:00 | 0.26 | 0.33 | 0.4 | 0.49 | 0.56 | 0.64 |
| 11:30 | 0.25 | 0.32 | 0.38 | 0.47 | 0.54 | 0.61 |
| 12:00 | 0.25 | 0.31 | 0.37 | 0.45 | 0.52 | 0.59 |
| 13:00 | 0.23 | 0.29 | 0.34 | 0.42 | 0.49 | 0.55 |
| 14:00 | 0.22 | 0.27 | 0.32 | 0.4 | 0.46 | 0.52 |
| 15:00 | 0.21 | 0.26 | 0.31 | 0.37 | 0.43 | 0.49 |
| 16:00 | 0.19 | 0.25 | 0.29 | 0.35 | 0.41 | 0.46 |
| 17:00 | 0.19 | 0.23 | 0.27 | 0.34 | 0.38 | 0.44 |
| 18:00 | 0.18 | 0.22 | 0.26 | 0.32 | 0.37 | 0.41 |
| 19:00 | 0.17 | 0.21 | 0.25 | 0.3 | 0.35 | 0.39 |
| 20:00 | 0.16 | 0.2 | 0.24 | 0.29 | 0.33 | 0.38 |
| 21:00 | 0.16 | 0.19 | 0.23 | 0.28 | 0.32 | 0.36 |
| 22:00 | 0.15 | 0.19 | 0.22 | 0.27 | 0.31 | 0.35 |
| 23:00 | 0.14 | 0.18 | 0.21 | 0.26 | 0.29 | 0.33 |
| 00:00 | 0.14 | 0.17 | 0.2 | 0.25 | 0.28 | 0.32 |
ColeF2070 ah. here is one option (I'll think and prob add another):
=IFERROR( FORECAST.LINEAR( $L$3, INDEX('Riley County Rainfall Intensity'!$B$2:$G$117,MATCH($L$3-0.0001,'Riley County Rainfall Intensity'!$A$2:$A$117,1)+{0,1},ROW(K9)-ROW($K$8)), INDEX('Riley County Rainfall Intensity'!$A$2:$A$117,MATCH($L$3-0.0001,'Riley County Rainfall Intensity'!$A$2:$A$117,1)+{0,1})), "not found")and then drag down
and an option using new array formula:
=LET(in, G3, vals, CHOOSEROWS('Riley County Rainfall Intensity'!A2:G117,XMATCH(G3,'Riley County Rainfall Intensity'!A2:A117,-1)+{0,1}), diff, (in-INDEX(vals,1,1))/(INDEX(vals,2,1)-INDEX(vals,1,1)), DROP(TRANSPOSE(diff*(TAKE(vals,-1)-TAKE(vals,1))+TAKE(vals,1)),1) )but maybe someone else knows an easier way.
3 Replies
ColeF2070 first I want to thank you for formatting that all in tables to make things easier. next I think what you want is to fill in the set of data from 2 year to 100 year based on that Tc= value.
That said I think you have some typos/error in that the lookup row you have is 1 below the data you listed except for the 50 year which is completely different row.
In the attached I give 2 examples:
using new 365 formula:
=TRANSPOSE(XLOOKUP(G3,'Riley County Rainfall Intensity'!A2:A117,'Riley County Rainfall Intensity'!B2:G117,"not found",1))using older formula and then hit ctrl-shift-enter (and I think it will work but I can't test):
=IFERROR(TRANSPOSE(INDEX('Riley County Rainfall Intensity'!B2:G117,MATCH(L3,'Riley County Rainfall Intensity'!A2:A117,1),0)),"not found")oops had to edit to find nearest. Note: the newer XLOOKUP will let you easily select next larger or next lower but the older Match will only find next lower so if you want the next higher you can do a small minus then +1 to the index so something like this:
=IFERROR(TRANSPOSE(INDEX('Riley County Rainfall Intensity'!B2:G117,MATCH(L3-0.001,'Riley County Rainfall Intensity'!A2:A117,1)+1,0)),"not found")m_tarler Thank you, I may very well have some errors lol, I am trying to get a result between two sets of values when the referenced cell is between the values on the table. (ie Time = 1:03 falls between time 1:00 and 1:05 so something that iterates with the givens. (1:05-1:00/1.51-1.59)=(1:05-1:03/1.51-x))
ColeF2070 ah. here is one option (I'll think and prob add another):
=IFERROR( FORECAST.LINEAR( $L$3, INDEX('Riley County Rainfall Intensity'!$B$2:$G$117,MATCH($L$3-0.0001,'Riley County Rainfall Intensity'!$A$2:$A$117,1)+{0,1},ROW(K9)-ROW($K$8)), INDEX('Riley County Rainfall Intensity'!$A$2:$A$117,MATCH($L$3-0.0001,'Riley County Rainfall Intensity'!$A$2:$A$117,1)+{0,1})), "not found")and then drag down
and an option using new array formula:
=LET(in, G3, vals, CHOOSEROWS('Riley County Rainfall Intensity'!A2:G117,XMATCH(G3,'Riley County Rainfall Intensity'!A2:A117,-1)+{0,1}), diff, (in-INDEX(vals,1,1))/(INDEX(vals,2,1)-INDEX(vals,1,1)), DROP(TRANSPOSE(diff*(TAKE(vals,-1)-TAKE(vals,1))+TAKE(vals,1)),1) )but maybe someone else knows an easier way.
