# Mutually Exclusive Cells

#### Skudwincell

##### New Member
Hey Guys,

I'm currently doing some uni analytics work and I'm trying to make a cell represent the chance of two other cells being mutually exclusive. Is there a way of including the probability of each cell occurring in the formula?

The example is

Year 1:
Low Price: x, 0.4
High Price: y, 0.6

Year 2:
Low Price: a, 0.4
High Price b, 0.6

and so on..

I want a cell to show the answer after assuming the cells are mutually exclusive.

I'm assuming the formula I need to use is P(x) + P somehow?

Any help would be greatly appreciated as this is due soon!

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Welcome to the Forum!

I'm not sure what you're looking for here. Do we interpret the question as saying that in year 1, something can have a low value, \$x, with 40% probability, and a high value, \$y, with 60% probability. If so, these possibilities are mutually exclusive, because it's either x or y, and can't be both.

Similarly, is it the same something in year 2 with 40% probability of low price, a, and 60% probability of high price, b?

What probability/probabilities do you need to determine?

Rather than being mutually exclusive, did you mean to say that the year 1 and year 2 results are independent?

Welcome to the Forum!

I'm not sure what you're looking for here. Do we interpret the question as saying that in year 1, something can have a low value, \$x, with 40% probability, and a high value, \$y, with 60% probability. If so, these possibilities are mutually exclusive, because it's either x or y, and can't be both.

Similarly, is it the same something in year 2 with 40% probability of low price, a, and 60% probability of high price, b?

What probability/probabilities do you need to determine?

Rather than being mutually exclusive, did you mean to say that the year 1 and year 2 results are independent?

Hey Stephen,

Yes I think I mean independent. Basically I need to determine what price I pay for oil from years 1 to 15. The probability for x and y remains the same each year but x and y both change year from year. I know what I sell my oil at but I need to determine the margin on it each year.

After this I need to do basic analysis such as NPV to show which option is the best one to go for.

(I have multiple scenarios to carry out and then I have to implement changes into the model.)

Are you able to help at all?

Are you able to help at all?

Happy to help with implementing in Excel, less inclined to do your assignment for you

If I'm interpreting this correctly, it sounds like you're testing multiple scenarios that might look something like this:

Excel 2010
ABCDE
1Scenario:123etc …
2Yr 1 Hi\$120\$130\$150
3Yr 1 Lo\$100\$80\$40
4Yr 2 Hi\$125\$130\$150
5Yr 2 Lo\$105\$70\$75
6…..
7Yr 10 Hi\$130\$200\$150
8Yr 10 Lo\$100\$100\$80
9
10
11Expected\$112\$110\$106
12price Yr 1

<tbody>
</tbody>
1
where B11: =0.4*B3+0.6*B2

After this I need to do basic analysis such as NPV to show which option is the best one to go for.

How do you define which scenario is the "best one"? Scenario 3 gives you the lowest expected price, and hence the highest expected margin, in year 1. But if you're locked into selling at \$140, say, then you have a 60% chance of making a loss under scenario 3, whereas scenarios 1 and 2 will always deliver profits.

If you're only interested in maximising total expected margin, you can simply attached NPV factors to the expected margins year by year.

In the (I suspect more likely) event that you're interested in maximising the chance that the NPV of your margin is at least \$X (where X might be 0, or some higher amount) you could use Monte Carlo simulation to take into account the variability of your net margin.

Replies
3
Views
241
Replies
3
Views
2K
Replies
6
Views
2K
Replies
1
Views
3K
Replies
1
Views
1K

1,219,828
Messages
6,150,468
Members
450,966
Latest member
Yali

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back