# Accumulate percentage within index range

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

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

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

Alan.
0
retiredAuthor Commented:
Oh sorry I goofed here it is
AccumulatePercentages.xlsx
0
ConsultantCommented:
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
retiredAuthor 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
Thanks
Ian
0
ConsultantCommented:
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?

Thanks,

Alan.
1
retiredAuthor Commented:
I was saying where column E  is greater than zero, not column B
0
retiredAuthor Commented:
Hi Alan,
Column B is not part of the equation. It was used only to display the correct values.
0
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
retiredAuthor 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
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
retiredAuthor 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
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.

1
retiredAuthor 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.
Ian
0
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
retiredAuthor 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
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),))))))
``````
and then copy it down.
1
retiredAuthor 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
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

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

retiredAuthor 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
retiredAuthor Commented:
Works perfectly and thanks for your patience.
Ian
1
Excel & VBA ExpertCommented:
You're welcome Ian! Glad it worked as desired in the end. :)
1
ConsultantCommented:
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
retiredAuthor Commented:
Hi Alan,
Yes you are correct. Neeraj did pick up on it, to which I replied.
Thanks for the feedback.
Ian
0
ConsultantCommented:
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
retiredAuthor 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