Formula to sum probabilities to one within an Index Range

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
Ian BellretiredAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saqib Husain, SyedEngineerCommented:
C2      =1/B2/SUMPRODUCT(($A$2:$A$31=A2)/$B$2:$B$31)
0
Saqib Husain, SyedEngineerCommented:
This formula will compare the values exactly

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ian BellretiredAuthor Commented:
Thanks Saqib, it works like a charm
Ian
0
Determine the Perfect Price for Your IT Services

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

Ian BellretiredAuthor 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
0
Saqib Husain, SyedEngineerCommented:
Does this help?

=1/B2/ROUND(SUMPRODUCT(($A$2:$A$31=A2)/($B$2:$B$31+0.00000000001)),2)
0
Ian BellretiredAuthor Commented:
Thanks again Saqib, it takes a long time to calculate but I can run it overnight
0
Saqib Husain, SyedEngineerCommented:
How many rows do you have?

What is the maximum length of a set? This may help speed it up.
0
Ian BellretiredAuthor Commented:
160,000 rows and 78 columns would contain that formula you created.
0
Saqib Husain, SyedEngineerCommented:
What is the maximum length of a data set who's sum needs to be 1?
0
Ian BellretiredAuthor Commented:
That is the max length and width of the data set.
0
Saqib Husain, SyedEngineerCommented:
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?
0
Ian BellretiredAuthor 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
0
Saqib Husain, SyedEngineerCommented:
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)
0
Ian BellretiredAuthor 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
0
Saqib Husain, SyedEngineerCommented:
The 40 is an additional margin for 36 as a maximum index range.
0
Ian BellretiredAuthor Commented:
Okay, I think I said the same in a roundabout way.
I still am curious about the 80 ?
0
Saqib Husain, SyedEngineerCommented:
80 = 40 * 2

It really should have been 81 but we are already in a safe zone so 80 works.
0
Ian BellretiredAuthor Commented:
All understood now and many thanks Saqib
Ian
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Spreadsheets

From novice to tech pro — start learning today.