Partial Duration Time Series Precipitation

[Gremlin980]

I have two datasets of precipitation data. One is daily rainfall data over 10 years for a given location. The other is a partial duration series (PDS) which shows the estimated rainfall for a given recurrence interval (expected time for precipitation event of specific volume to occur) and intensity (volume of rain/time). By cross referencing the two data tables I'm able to manually determine the return interval for each daily rain event and for multiple days which are part of the same event (ie, adding up totals for consecutive days of rain and then referencing the PDS table). For this example, one day of no rainfall indicates a separation in rain events. Additionally, if a value is below the 1 year interval, then it is a <1 year return interval event. If it is between the 1 and 2 year interval, it is a 1-2 year event.

It isn't feasible for me to do this manually as my rainfall data is over 10 years, so I'd like to automate this in excel. How would you go about this?

I think I've been able to figure out how to create upper and lower bounds for the time series to then match the daily data if it falls between those values, though I can't figure out how to combine multiple days to then output the interval for the specified duration of days. Below is an example of one performed manually over 8 days. Ignore values in parenthesis.

PDS Reference Table

Expected Output Table