Summing Cells to Equal a Certain Value

Occasional Visitor

Hi there,


I'm in the process of creating a decision analytic model on Excel. As part of the model, I need to calculate values along the normal distribution curve for the three measured outcomes we're using. I've been able to find a way to generate values along the curves for each of the individual outcomes, but is there a way I can make it such that the SUM of the values is equal to 1 (as there are only 3 possible outcomes in the model and they need to summate to 1).


I've used the NORMINV(RAND(),mean,STD) function in order to find the values, but I can't seem to figure out a way to relate the three values together such that a maximum value of 1 is achieved. At the moment they always equal to more or less than one. Unfortunately the values are dependent on one another so getting a particular value on the curve for one outcome evidently changes the possible values the other two outcomes can have. 


I hope this makes sense, please help!

1 Reply
Forcing a summation equal to 1 should mean the base values (the outcomes from your model) would change as well.
I believe your reference values somewhere in your calculation is being rounded which tends to give this kind of error in modelling.
On certain cases, I have seen the excel values for normal distribution deviates a little bit at the 3rd or 4th decimal place. Are you taking this in to account? Refer online sources to validate your results on excel.