EXCEL - Sorting Column Data for Hyetograph
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?
Any suggestions?





RE: EXCEL - Sorting Column Data for Hyetograph
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
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
RE: EXCEL - Sorting Column Data for Hyetograph
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
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
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
1) Using NAMED RANGES in this table via Formulas > Defined names > Create from selection > TOP row & LEFT column
2) Make LIST of Duration Naming the range with the TOP row
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
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
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
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
RE: EXCEL - Sorting Column Data for Hyetograph
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.