Implimenting a shift in a power trendline graph

[Fergus]

Hi,

I have data taken from standard graphs which give flows for drainage
areas.

In the past we would use this data to plot a straight line on a log
scale. We would then print this graph out and manually draw lines
parallel to it at specific flow rates (for a known drainage area).
This would give us flow graphs for a particular channel shape for
10yr, 50yr, 100yr and 500yr storms. We would then use these lines to
manually read off the flows at these return periods for other drainage
areas.

I have been creating a spreadsheet that automatically interpolates or
extrapolates information from the graph data. This gives me two
points which I plot on a log scale. For example:

Area Q
(acres) cfs
3000 3040
300 580

Using the LINEST function I can find the c and b constants in the
power function y=cx^b. This then allows me to plug in any value of
Area and get the corresponding flow (Q). These flows however are for
a specific storm. I need to shift the graph in order to get the flow
for a specific return period.
For example:

Area Flow Return Period
2630 1410 10-yr
2630 2222 50-yr
2630 2647 100-yr
2630 3900 500-yr

My problem is how do I shift the the line automatically (through a
worksheet function) so that it has the same shape and gradient but
incorporates a different point (eg. 2630, 1410 for the 10yr storm)

I hopw that I've explained my problem sufficiently! Thanks in advance
for your help.

 

[Jon Peltier]

Hey Fergus -

I assume by shift, you mean a parallel line on the log-log chart, offset
up or down. It's up to you to verify that your phenomenon can be
predicted by this simple offset, but I'll show you the steps in the
Excel model.

In my sample worksheet, I set up A13 like this:

Area Flow Log Area Log Flow
3000 3040 3.47712 3.4828
300 580 2.47712 2.7634

Using columns C and D as my data, I got fitting parameters, and stuck
them into F1:G2 like this:

slope int
0.71945 0.98127

I transposed your other data like this in A5:F7 (it includes the data
above, which I'm using to offset the intercepts):

10-yr 50-yr 100-yr 500-yr above
Area 2630 2630 2630 2630 2630
Flow 1410 2222 2647 3900 2765.3

I computed the flow from the first data above at 2630 acres, by putting
2630 into F6 and this formula into G6:

=10^(G2+F2*LOG(F6))

So the data in A1:B3 is somewhat worse than a 100-year storm, eh?
Anyway, I then used row 8 for my new intercept calculations. A8 has the
label Intercept, B8 has this formula:

=$G$2+LOG(B7)-LOG($F7)

which I dragged to fill B8:F8. Of course, F8 matches G2, since it's the
same data. Then I used rows 9 & 10 for all the new lines that have to
be drawn. A9:A10 contain the original limits on area, 300 and 3000,
though you could widen them as much as you trust the extrapolation.

Cell B9 contains this formula:

=10^(B$8+$F$2*LOG($A9))

which I then dragged right and then down to fill B9:F10. In all these
cases I used the dollar signs to allow dragging to fill in the formulas.
A dollar sign before a column letter means even if I drag to a new
column, the letter stays the same; without the dollar sign the letter
would adjust up a letter for every column I dragged it to the right,
down to the left. Same with the rows.

So A5:F10 now looks like this:

10-yr 50-yr 100-yr 500-yr above
Area 2630 2630 2630 2630 2630
Flow 1410 2222 2647 3900 2765.3
Int. 0.68875 0.88627 0.96228 1.13059 0.98127
3000 1550.05 2442.70 2909.92 4287.38 3040
300 295.73 466.04 555.18 817.99 580

Select A5:F5, then hold the Ctrl key while selecting A9:F10 (you have
two areas selected). Run the chart wizard to make a new chart with
these series (series in columns on step two of the wizard), or copy this
discontiguous range, select the existing chart, and use Paste Special
from the Edit menu to add the data to the chart as new series, in
columns, series names in first row, categories in the first column.

- Jon