# Find the cumulative amount

#### bines53

##### Active Member
Hello friends,

There is one column, eight numbers in column A, find which row in the last number that complements the amount of 29
There is a formula must know a lot of friends, like this,
=MMULT(N(ROW(A1:A8)>=TRANSPOSE(ROW(A1:A8))),A1:A8).
You are invited to give a different formula,
The rule only, without volatile functions.
Next Thursday I will present my formula.

Good luck !

David

#### Attachments

• 8.5 KB Views: 53
Last edited:

#### bines53

##### Active Member
Hello friends,

I want to make it clear that the intention is for sequential numbers.

David

#### bosco_yip

##### Excel Ninja
Herein belows some array formulae can be used :

Same as : =MMULT(N(ROW(A1:A8)>=TRANSPOSE(ROW(A1:A8))),A1:A8)

=MATCH(29,SUBTOTAL(109,OFFSET(A1,,,ROW(\$1:\$8))))

or,

=MATCH(29,SUBTOTAL(9,OFFSET(A1,,,ROW(\$1:\$8))))

or,

=MATCH(29,SUMIF(OFFSET(A1,,,ROW(\$1:\$8)),">0"))

The above formulae must enter with SHIFT+CTRL+ENTER instead of just ENTER.

Regards
Bosco

• Thomas Kuriakose

#### bines53

##### Active Member
Hi Bosco ,

A nice solution, but can not get it, I pointed out the only rule is, without volatile functions.
No doubt there are many advantages to the function OFFSET, but the only problem and its principal, which is volatile.

Regards
David

#### bines53

##### Active Member
Hi

{1,2,3,4,5,6,7,8}it is the same to TRANSPOSE(ROW(A1:A8)),

In other words, there is no difference,

It surprises me, yesterday I did a Google search, and I saw very little information about the function, the current challenge is built, although there is a need for a mathematical trick, a little adjustment, function becomes very useful and effective.

Regards
David
http://chandoo.org/forum/members/khalid-ngo.15425/

#### Khalid NGO

##### Excel Ninja
{1,2,3,4,5,6,7,8}it is the same to TRANSPOSE(ROW(A1:A8)),
In other words, there is no difference
Hi David,
Yes, I just saw your attachment here:

there is a need for a mathematical trick, a little adjustment, function becomes very useful and effective.
Interesting...
Waiting for the magic #### John Jairo V

##### Well-Known Member
Hi to all!

Courtesy of Lori - PROB formula - (non CSE and non-volatile formula):

=MATCH(29,INDEX(PROB(ROW(A1:A8),A1:A8/SUM(A1:A8),,ROW(A1:A8))*SUM(A1:A8),))

Blessings!

• Thomas Kuriakose and Khalid NGO

#### Lori

##### Active Member
@John Jairo V - just noticed the reference to this thread which i'd almost forgotten about:

That formula works fine in this case, however it can fail on larger data sizes or if the data sums to zero. For example try extending to the range A1:A27 and filling with 1's (this errors due to rounding in the unit checksum that PROB carries out.) A more robust possibility (if a little longer) is:

=PROB(IF(FREQUENCY(1,0*A1:A8),,ROW(A1:A8)),IF(FREQUENCY(1,0*A1:A8),1-SUM(A1:A8),A1:A8),1,ROW(A1:A8))

[Of course if an index is available (numbers, dates or sorted text) then a much simpler formula is possible such as SUMIF(B1:B8,"<="&B1:B8,A1:A8) where eg B1:B8={1;2;3;4;5;6;7;8}.]

Last edited:
• Thomas Kuriakose and Khalid NGO

#### Lori

##### Active Member
I believe there is a rounding issue because PROB checks that all the values sum to one. Try with CSE =SUM(A1:A623/SUM(A1:A623))=1 next to your formula, I think PROB will return an error whenever this SUM formula returns FALSE.

Last edited:

#### bines53

##### Active Member
Hi @Lori,

I tried to check the function ARRAY.JOIN ,Morefunc add in,instead of IF(FREQUENCY,

=PROB(IF(FREQUENCY(1,0*A1:A8),,ROW(A1:A8)),ARRAY.JOIN(A1:A8,1-SUM(A1:A8)),1,ROW(A1:A8)),It works
And so ,
=PROB(ARRAY.JOIN(ROW(A1:A8),0),IF(FREQUENCY(1,0*A1:A8),1-SUM(A1:A8),A1:A8),1,ROW(A1:A8)),

But with two arguments with ARRAY.JOIN ,
=PROB(ARRAY.JOIN(ROW(A1:A8),0),ARRAY.JOIN(A1:A8,1-SUM(A1:A8)),1,ROW(A1:A8)),Does not work,Not clear.

Regards
David

Last edited:
• Thomas Kuriakose

#### Lori

##### Active Member
Haven't much experience with the morefunc add-in so can't help with that issue. For the first argument another option is MODE.MULT(ROW(A1:A8)*{1,1},0,0). And one other possibility that avoids needing to join the arrays is:

=PROB(ROW(A1:A8)*{1,0,0,0},A1:A8*{1,0,0,0}+{0,9,-9,1}^99,1,ROW(A1:A8))

#### bines53

##### Active Member
Hi @Lori,

This fascinating and amazing, all the possibilities, you offer here !!!

Regards
David

#### bines53

##### Active Member
Hello friends,

I would like to summarize the issue and give a few examples, and I will also file with the examples I present them here.

First, I want to say thank you, Lori ,You made the function PROB will work properly and stable.

Function PROB ,Working well with empty cells and negative numbers.

1.=PROB(ROW(A1:A8)*{1,0,0,0},A1:A8*{1,0,0,0}+{0,9,-9,1}^99,2,6)
The amount, row 2 to row 6.

2.Want to find at least a few rows is required, the amount 29.
=MATCH(1,MMULT(--(PROB(ROW(A1:A8)*{1,0,0,0},A1:A8*{1,0,0,0}+{0,9,-9,1}^99,1,ROW(A1:A8))>=29),1),0) or

=AGGREGATE(15,6,ROW(A1:A8)/(PROB(ROW(A1:A8)*{1,0,0,0},A1:A8*{1,0,0,0}+{0,9,-9,1}^99,1,ROW(A1:A8))>=29),1)

Can be shorter, but is required CSW ,enter with SHIFT+CTRL+ENTER.
=MATCH(29,--PROB(ROW(A1:A8)*{1,0,0,0},A1:A8*{1,0,0,0}+{0,9,-9,1}^99,1,ROW(A1:A8)),0)

3.Want to know, from row 3 What is the row that will give the amount will be 19
=MATCH(1,MMULT(--(PROB(ROW(A1:A8)*{1,0,0,0},A1:A8*{1,0,0,0}+{0,9,-9,1}^99,3,ROW(A1:A8))>=19),1),0)

=AGGREGATE(15,6,ROW(A1:A8)/(PROB(ROW(A1:A8)*{1,0,0,0},A1:A8*{1,0,0,0}+{0,9,-9,1}^99,3,ROW(A1:A8))>=19),1)

4.Want to know what row to the last row, the amount of 29

=MATCH(1,MMULT(--(PROB(ROW(A1:A8)*{1,0,0,0},A1:A8*{1,0,0,0}+{0,9,-9,1}^99,ROW(A1:A8),8)<=29),1),0)

=AGGREGATE(14,6,ROW(A1:A8)/(PROB(ROW(A1:A8)*{1,0,0,0},A1:A8*{1,0,0,0}+{0,9,-9,1}^99,ROW(A1:A8),8)>=29),1)

There may be something more challenging.

David

#### Attachments

• 9.5 KB Views: 17
Last edited: