Drop down list to pull different formulas for interpolation?

Hey I ran into a wall today. What I am trying to do is setup a spreadsheet do some hydraulic calculations for me. I want to be able to input basic information in a few cells and select a design storm from a dropdown list, and then have all the formulas pull through.

I have a drop down list for different design storms. These design storms have different amounts of rainfall and intensity based on the duration of the storm. So based on a calculated time of concentration, a rainfall intensity must be linear interpolated according to a design storm.

Here is where my spreadsheet is at currently. I setup a drop down list for the design storms. A formula for Time of Concentration is in there. I am having trouble with the rainfall intensity formula. On the IDF Table tab, I setup design storms and the numbers 1-7 in a corresponding column. I then used Vlookup to associate the drop down list with the table I just made, to yield number results 1-7. I then tried to use CHOOSE to recognize the number in the cell from VLOOKUP, and then to use the correct formulas with each storm. The formulas I used use FORECAST to read back to the IDF table to linear interpolate to the correct design storm column. I can't get CHOOSE to work with the long formula string I have for linear interpolation.

Can someone please take a look at this? I don't care if I have to scrap

Hi wglenn13

Welcome to the forum.

This is what I would do! See the BLUE formulas.

wglenn13 Rational Method.xlsx

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

If several people have responded, when you reply please make it clear WHO you are responding
to by mentioning their name.

Awesome! Thank you so much. I was over complicating it.