Formula to sum probabilities to one within an Index Range

C2      =1/B2/SUMPRODUCT(($A$2:$A$31=A2)/$B$2:$B$31)

Thanks Saqib, it works like a charm
Ian

If a cell contains a zero in column B then the whole column (E) displays a divide by zero error, how can this be corrected ?
Thanks

Thanks again Saqib, it takes a long time to calculate but I can run it overnight

How many rows do you have?

What is the maximum length of a set? This may help speed it up.

160,000 rows and 78 columns would contain that formula you created.

What is the maximum length of a data set who's sum needs to be 1?

That is the max length and width of the data set.

In the given sheet in column A there are 4 data sets whose sum needs to be 1. What could be the maximum length of one of those 4 data sets?

Oh I see. The maximum index range as I would understand it to be is 36 but that is very rare.
The average would be around 10-12

See if this performs any faster

=1/B2/ROUND(SUMPRODUCT((--(OFFSET($A$2,MAX(0,ROW(A2)-40),0,80)=A2))/(OFFSET($B$2,MAX(0,ROW(A2)-40),0,80)+0.00000000001)),2)

Is  -40 related to the 31 rows ? in which case if I have 159,600 rows I could use -160,000 ?
What then is  80 in the formula ?
Thanks

The 40 is an additional margin for 36 as a maximum index range.

Okay, I think I said the same in a roundabout way.
I still am curious about the 80 ?

All understood now and many thanks Saqib
Ian