Accumulate percentage within index range

Hi,
I would like a formula to sum probabilities in range.
Please refer attachment.
Many Thanks
Ian
raceproretiredAsked:
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.

zvitamConsultantCommented:
It seems that the following article will help you How to Calculate Probability Using Excel
0
AlanConsultantCommented:
Hi Ian,

There is no attachment, so hard to know what you want?

Alan.
0
raceproretiredAuthor Commented:
Oh sorry I goofed here it is
AccumulatePercentages.xlsx
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

AlanConsultantCommented:
Hi Racepro,

I am not sure how you are getting the answers you have shown in Column B.

For example, B2 to B19 inclusive, sum to more than 100% - is that correct?  If so, why?

Also, why is B2 equal to zero, whereas B4 is not zero?  I don't understand your logic, and hence there is no way to reproduce that in a formula without understanding why.


Thanks,

Alan.
1
raceproretiredAuthor Commented:
Hi Alan,

Easiest way for me to describe it, is that it is an accumulative figure within the index range
where column E value is greater than zero.
Example
If(E2>0,1/F2  please add Sum range with INDEX
Thanks
Ian
0
AlanConsultantCommented:
Hi Ian,

I still don't understand why is B2 equal to zero, whereas B4 is not zero, but B7 is zero.

I can't see any logic in that, but I guess you are confirming that it is there?

Please can you explain further?

Thanks,

Alan.
1
raceproretiredAuthor Commented:
I was saying where column E  is greater than zero, not column B
0
raceproretiredAuthor Commented:
Hi Alan,
Column B is not part of the equation. It was used only to display the correct values.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
I think Alan raised the correct point.

Like if E4 is 0, why B4 is populated whereas E7:E9 are zeros so the B7:B9?
1
raceproretiredAuthor Commented:
Hi Neeraj,
You may not have considered the index range when commenting.
B7 B8 and B9 display zeros as there are no values for  E7:E9  index range A7:a11
Ian
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
I clearly don't see any consistent pattern in the formulas you placed in column B, maybe I am missing something here.
e.g. B4 = 1/F4, B13 = 1/F13+B12 and B14 = 1/F14+B13 whereas E4, E13 and E14 are zeros.

Please do us a favor, in column H starting from H2 and write comments to justify the formulas you added in column B for each row. Maybe that would help us to understand the logic used behind the calculations in column B.
1
raceproretiredAuthor Commented:
I think you are missing something. Column B is only a column displaying the results of
a formula that I require. Any formulas in there should be ignored as I used them to calculate
the results.
The theory is simple enough
in each index range in column A look for values in column E
and then use formula  1/(Column F) accumulative.
example
index        col E   col F       Formula
100               0      3.2             0
100               5      4.0             .25
100               7      6.0             .42        includes .25
200               2      3.0              .33       start of new series
200               0      5.0              .33
200               4      2.0              .83       includes .33

Thanks
Ian
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Of course I am missing something and I am really sorry for that.
But here is my last attempt to show you my confusion.
Please refer to the screenshot, in column C, you will see the formulas used in column B which you placed as per your logic and I am just trying to understand that logic here. Please look at the comments I have added in highlighted cells in column G.

 Logic.jpg
1
raceproretiredAuthor Commented:
Formulas in column B are to be ignored as I have said. Please disregard them completely.
I had forgotten to convert those formulas to values it was a column only to show you the results.
Instead try and follow example in my last post.
Thanks for your continued patience
Ian
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
I am not talking about the formulas but the logic behind the formulas. After all the values which you showed in column B are correct as per your logic. Can you please take a minute to read my doubts and explain them again?
1
raceproretiredAuthor Commented:
Hi Neeraj,
You are correct, as usual :)  The cell contents in B4 should be  the same as in cell B3
Content in B12 & B13 should read .095.
My 2nd last post explains the logic quite simply.
Sorry for the confusion.
Ian
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Okay, give this a try...

In D2
=IF(E2=0,IF(MAX(INDEX((A$1:A1=A2)*ROW(D$1:D1),))=0,0,SUM(INDEX(D$1:D1,MAX(INDEX((A$1:A1=A2)*ROW(D$1:D1),))))),1/F2+IF(MAX(INDEX((A$1:A1=A2)*ROW(D$1:D1),))=0,0,SUM(INDEX(D$1:D1,MAX(INDEX((A$1:A1=A2)*ROW(D$1:D1),))))))

Open in new window

and then copy it down.
1
raceproretiredAuthor Commented:
It's not accumulating
For example:
cell B4 should be same as B3
Cell B6 should be .4566+.1351 = .5917
Start of new index series is correct
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
It is doing exactly what you were asking.
Please refer to the attached and look at column D where I have inserted the formula. Don't refer to your original column B for reference or cross checking the values in column D, that calculation is wrong as per your logic.
AccumulatePercentages.xlsx
1

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
raceproretiredAuthor Commented:
Ah that is correct we got there in the end. I suppose the easy logic was in the earlier post
which I kept referring to.
copied below
index        col E   col F       Formula
100               0      3.2             0
100               5      4.0             .25
100               7      6.0             .42        includes .25
200               2      3.0              .33       start of new series
200               0      5.0              .33
200               4      2.0              .83       includes .33
0
raceproretiredAuthor Commented:
Works perfectly and thanks for your patience.
Ian
1
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Ian! Glad it worked as desired in the end. :)
1
AlanConsultantCommented:
Hi RacePro,

You have marked that as being correct, and yet the output from the formula does not match what you said was the correct value (your Column B).

Please can you explain how that can be?

Thanks,

Alan.
1
raceproretiredAuthor Commented:
Hi Alan,
Yes you are correct. Neeraj did pick up on it, to which I replied.
Thanks for the feedback.
Ian
0
AlanConsultantCommented:
It would have been more polite to have admitted that when I raised it up above instead of wasting my time by implying I wasn't understanding the logic you were applying to get those 'correct' answers.
0
raceproretiredAuthor Commented:
If you had noticed, the error wasn't discovered till well after your post.
I do apologise if it has caused you some inconvenience.
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.