×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# EXCEL - Sorting Column Data for Hyetograph4

## EXCEL - Sorting Column Data for Hyetograph

(OP)
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?
Replies continue below

### RE: EXCEL - Sorting Column Data for Hyetograph

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.

### RE: EXCEL - Sorting Column Data for Hyetograph

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: Eng-Tips.com Forum Policies

Need help writing a question or understanding a reply? forum1529: Translation Assistance for Engineers

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

### RE: EXCEL - Sorting Column Data for Hyetograph

(OP)
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.

### RE: EXCEL - Sorting Column Data for Hyetograph

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.

### RE: EXCEL - Sorting Column Data for Hyetograph

(OP)
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).

### RE: EXCEL - Sorting Column Data for Hyetograph

2

1) Using NAMED RANGES in this table via Formulas > Defined names > Create from selection > TOP row & LEFT column

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


2) Make LIST of Duration Naming the range with the TOP row

Dur
_2_year
_10_year
_25_year
_50_year
_100_year


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...

=(a INDIRECT(rDUR))*((J5/60)+(b INDIRECT(rDUR)))^(c_ INDIRECT(rDUR))

...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,

Just traded in my OLD subtlety...
for a NUance!

### RE: EXCEL - Sorting Column Data for Hyetograph

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: Eng-Tips.com Forum Policies http://eng-tips.com/market.cfm?

### RE: EXCEL - Sorting Column Data for Hyetograph

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.h..., which shows the time-integrated fraction of a 24-hr rainfall

TTFN
FAQ731-376: Eng-Tips.com Forum Policies

Need help writing a question or understanding a reply? forum1529: Translation Assistance for Engineers

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

### RE: EXCEL - Sorting Column Data for Hyetograph

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.

### RE: EXCEL - Sorting Column Data for Hyetograph

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.

### RE: EXCEL - Sorting Column Data for Hyetograph

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.

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Close Box

# Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!