Probability Formula for Large Data Set

Hello,

I am trying to solve the answer to two probability questions with multiple variables as they pertain to a large data set. I am sure after assistance with the formula/method for solving one question and a portion of data, I will be able to solve the rest. The problem and questions are as follows and the data set is attached (if I need to provide more information, clarify anything, or post in a more appropriate forum, please let me know):

Problem background
The 2013-16 drought prompted us to secure additional water supplies through transfers from other agencies. This water could then be stored in Lake Oroville, a major reservoir with a capacity of 3.5 million acre-feet (MAF).
However, storing water in Oroville carries a risk: if a wet winter causes the lake level to get too high, operators must release (spill) excess water to avoid the risk of catastrophic flooding. The maximum volume of water that Oroville can hold without exceeding this level is known as the Flood Control Limit, and it varies by month (in order to accommodate wet season runoff). Any stored water that causes the lake to rise above the Flood Control Level will therefore be lost. In this exercise, you will help decide whether to approve a proposed transfer of 200 thousand acre-feet (TAF) by estimating the probability of spilling this water, given a range of likely future hydrologic conditions.

The CALSIM model estimates the volume of water stored in Oroville over the next 24 months by simulating multiple future precipitation and runoff conditions (hydrologic sequences). These hydrologic sequences come from historical data for the years 1952—2001. Each of the 50 model runs uses a different historical year as the starting point. For example, model run #1 assumes the next 24 months will look like 1952-53, run #2 assumes they will look like 1953-54, etc. Together, the 50 model runs constitute a range of possible futures from which probabilities can be determined.
The results of these model runs are shown in the excel workbook, in the ‘Oroville profile’ tab. For each month, the estimated Oroville Storage and Flood Control Limit is given in TAF. If the 200 TAF of transfer water is added on top of the modelled storage, any excess water over the flood control limits would be subject to spilling.

Questions
1. Assuming we place 200 TAF (200,000 acre ft) of transfer water in storage immediately, what is the probability of having 180 TAF or more (90%) remaining after 24 months?

2. What is the probability of having 100 TAF (50%) or more remaining in storage after 24 months?

Welcome to the forum.

2103 is an issue number for MS365, which is the version you should state in your profile, please. Thanks.

This seems to be a maths question. Once you tell us how you would calculate the two questions manually, we can help you to automate the calculation. Bear in mind we aren't (all) mathematicians (this isn't a maths forum).

Thanks, however see post #3.

You haven't, it's just that I wanted to make clear that the purpose of this place is not solving mathematical problems. If you want to AUTOMATE mathematical problem solving, you will need to provide the mathematical mechanics, so yes, antyhing you can offer here will help.

Having said this, there are one or two here who happen to be maths gurus as well as Excel experts, so if one of them does probability, you might get lucky.

My gut instinct is telling me that this may require the SOLVER add-in (which I believe is part of MS365), but I cannot be sure yet.

Understood and hopefully I get lucky while I attempt to work out the question. I was thinking I could use the Data Analysis add-in utilizing Descriptive Statistics to assist in solving for the answer. Perhaps finding the summary statistics for each year and running a confidence level for Mean (Question 1 using 90%; Questions 2 using 50%). It is apparent I need to research further. Thank you for your insight.