Formula to sum probabilities to one within an Index Range

Ian Bell
Ian Bell used Ask the Experts™
on
Hi,
I would like one formula to sum probabilities to one within an Index Range.
Please refer to attached sheet
Many thanks
IanSum-Probabilities-to-one.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
C2      =1/B2/SUMPRODUCT(($A$2:$A$31=A2)/$B$2:$B$31)
This formula will compare the values exactly

=1/B2/ROUND(SUMPRODUCT(($A$2:$A$31=A2)/$B$2:$B$31),2)
Ian Bellretired

Author

Commented:
Thanks Saqib, it works like a charm
Ian
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Ian Bellretired

Author

Commented:
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
Does this help?

=1/B2/ROUND(SUMPRODUCT(($A$2:$A$31=A2)/($B$2:$B$31+0.00000000001)),2)
Ian Bellretired

Author

Commented:
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.
Ian Bellretired

Author

Commented:
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?
Ian Bellretired

Author

Commented:
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?
Ian Bellretired

Author

Commented:
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)
Ian Bellretired

Author

Commented:
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.
Ian Bellretired

Author

Commented:
Okay, I think I said the same in a roundabout way.
I still am curious about the 80 ?
80 = 40 * 2

It really should have been 81 but we are already in a safe zone so 80 works.
Ian Bellretired

Author

Commented:
All understood now and many thanks Saqib
Ian

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial