EXCEL - Sorting Column Data for Hyetograph 4

[Trackfiend]

I've got rainfall data (Time in inches and precipitation depth) in two separate columns in excel and I'm attempting to rearrange the data in one of the columns. Let's say I have a data range of 1 (Cell A1) to 100 (Cell A100). In the new column, I'd like to have the numbers rearranged where the highest date point, 100, is in the middle of the new column (Cell B50), and the remaining data points decay outwards, i.e. Cell B49=49, Cell B51=48, Cell A48=47, Cell A52=46....all the way down to where Cell A1=1 and Cell A100=2. I basically want to create a bell shape curve data range with the highest value in the middle.

Any suggestions?

 

[magoo2]

If the highest value is in the middle, you'll wind up with the rest of the data having values below that. You won't have a bell shaped curve.

If you took the average or median vale in the middle, then you would have data on both side of that.

 

[IRstuff]

Why not just a brute force linear equation, if that's all you want?

Otherwise, you can use the Gaussian equation and pick, say, 50 as your offset and find the exact values from the Gaussian function itself.

TTFN
faq731-376

Need help writing a question or understanding a reply? forum1529


Of course I can. I can do anything. I can do absolutely anything. I'm an expert!
There is a homework forum hosted by engineering.com:

http://www.engineering.com/AskForum/aff/32.aspx

 

[Trackfiend]

I'm trying to force/rearrange the data set into a hyetograph for rainfall data (time vs. precipitation depth). While not a true bell curve, the shape is still a bell curve shape. I've attached the spreadsheet and the column I'd like to rearrange is Column N (tab "Hyetograph"). I've manually typed in the desired rearrangement in Column P, but again, I had to manually type in the values from Column N to make this work. I'm looking for a function or workaround to take the values from Column N and make them look like Column P, with the the new data range lining up with Column J.

 

[GregLocock]

A quick couple of minutes on google suggests that a hyetograph is not just a rearrangement in the time domain of the data.

Cheers

Greg Locock


New here? Try reading these, they might help FAQ731-376

http://eng-tips.com/market.cfm?

 

[SkipVought]

Hi,

I normally post in the Tek-Tips forums, mostly Excel/VBA and I occasionally check Eng-Tips. Noticed this thread and it interests me.

How do you want to rearrange the IDF Curves - LA Region I 10-yr table?

in the mean time I'm attempting to generalize your formulas in that table (rather than $C$6) so that you could, if so inclined, use this table for ANY duration (2, 10, 25, etc) via a control selection.

 

[Trackfiend]

SkipVought - No preference on the arrangement of the existing table. The setup was pulled from existing manuals. My intent is to take the newly arranged data set and export that information into a SWMM model for a watershed analysis. In attempting this, I realized that I couldn't figure out how to rearrange column data into the manner in which I'm attempting.

GregLocock - Not sure what you're getting at....unless you're referring to a cumulative graph plot of a hyetograph using rainfall intensity (or depth) vs. time?

I've attached a revised spreadsheet showing what the chart (smooth and column) would look like if I were to graph the two data series (depth vs. time).

 

[SkipVought]

1) Using NAMED RANGES in this table via Formulas > Defined names > Create from selection > TOP row & LEFT column
[pre]
a b c
2-year 2.815 0.282 -0.899
10-year 4.016 0.347 -0.826
25-year 4.611 0.346 -0.798
50-year 5.097 0.351 -0.783
100-year 5.487 0.334 -0.759
[/pre]

2) Make LIST of Duration Naming the range with the TOP row
[pre]
Dur
_2_year
_10_year
_25_year
_50_year
_100_year
[/pre]

3) Insert a Data > Validation > LIST control in cell J3 to select a duration. Name that cell rDUR

4) The Intensity formula in column L becomes...
[tt]
=(a INDIRECT(rDUR))*((J5/60)+(b INDIRECT(rDUR)))^(c_ INDIRECT(rDUR))
[/tt]
...and by selecting any duration, calculates accordingly.

The only other matter to address is your original question of rearranging the table, and calculating Time & Precip.


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[GregLocock]

As I understand it there is the data, precipitation in each hour over a catchment, and then a curve of how that precipitation flows out through a channel, over the succeeding 24 hours. As such it looks a bit like a first order low pass filtering process. see attached image

However I found another graph claiming to be a hyetograph that looked like something to do with cumulative length of showers.

Cheers

Greg Locock


New here? Try reading these, they might help FAQ731-376

http://eng-tips.com/market.cfm?

 

[IRstuff]

You really can't generate a hyetograph like columns O and P directly from the IDF equations, because they are averaged data. You could, however, brute force the IDF results into a hyetograph using Fig B-1 from

http://www.lmnoeng.com/RainfallMaps/RainfallMaps.htm,

which shows the time-integrated fraction of a 24-hr rainfall

TTFN
faq731-376

Need help writing a question or understanding a reply? forum1529


Of course I can. I can do anything. I can do absolutely anything. I'm an expert!
There is a homework forum hosted by engineering.com:

http://www.engineering.com/AskForum/aff/32.aspx

 

[Drew08]

You certainly can develop a hyetograph directly from an IDF curve, its called a frequency rainfall distribution, completely unrelated to the SCS distribution, and Trackfiend is on the right track, he just needs help working through excel functions, not the hydrology. The idea is to have the 1 minute rainfall depth (at the midpoint of the storm) nested inside a 2 minute depth, nested inside a 3 minute depth, etc. etc.

In excel enter time 1 on your sheet at cell R34, and enter 2 at R33. At R35 enter [R34-2] select and drag to fill in remaining values to 60. At R32 enter [R33-2] select and drag up to fill in remaining values. Result is a column that looks like this:

59
57
55
...
7
5
3
1
2
4
6
...
56
58
60

Then you can either use a vlookup function or an index function (if time step is 1 minute) to select the appropriate incremental depth from Column N.
i.e., in Cell S5 enter vlookup=(R5,$J$5:$N$64,5,false), and drag down the column.

 

[zelgar]

Maybe I'm missing something, but how did you come up with the data in Col P (Precip. (in))? Col P only contains values, not a formula, which I would think would be applicable since the amount of precipitation would be related to the duration and intensity of the storm.

 

[zuludogm]

As Drew08 mentioned, you can use Index to do the sorting that you wanted.

The data that you're showing has 60 points, so you can do a brute force like this. The high-to-low sorted data points will be a range that I call [Data]. Using the simple A1:A100 example, [Data] is the range A1:A100. The spread-sorted data would then be produced by this formula:

=INDEX([Data], IF(ROW()<=50, 2*(ROW()-50), 100-((ROW()-MROUND(ROW(),100)/2)*2-1)))

There are more compact ways to do this with MOD, but this is much more readable.

As the real data in your example uses 60 points and starts on Row 5, you will need to adjust the formula above. Here is a more generalized version.

First, you can set up a Named Range:
_HalfDataPoints =INT(COUNT(Hyetograph!$L$5:$L$64)/2)
That counts the number of data points and divides it by 2, rounded down. It is the 50 you see in the formula above. It will be 30 in the Hyetograph sheet.

Second, we will use the worksheet row as an index, so should make an offset since your data are not starting in Row 1 in the real world. The offset will be a Named Range:
_ROffset =ROW(Hyetograph!$L$5)

Of course, this will be 5, but the Named Range _ROffset makes your formula more readable and if you move your table around, you won't have to adjust your formulas.

Finally, here is the formula above rewritten to be placed in a column beside the high-to-low sorted [Data], where [Data] is the range $L$5:$L$64.

=INDEX($L$5:$L$64, IF(ROW()-_ROffset<=_HalfDataPoints, 2*(ROW()-_ROffset), _HalfDataPoints*2-((ROW()-_ROffset-MROUND(ROW()-_ROffset, _HalfDataPoints*2)/2)*2-1)))

This will make the pattern you want, regardless of the number of data points.