• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find the cumulative amount

B

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

  • Challenge 2.xlsx
    8.5 KB · Views: 60
Last edited:
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
 
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
 
Hi @ Khalid NGO,

{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/
 
@John Jairo V - just noticed the reference to this thread which i'd almost forgotten about:
(http://www.pcreview.co.uk/threads/array-formula-returning-the-cumulative-sum.3121728/)

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:
Last edited:
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:
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:
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))
 
Hi @Lori,

Thank you for your help,
This fascinating and amazing, all the possibilities, you offer here !!!

Regards
David
 
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

  • chall-111.xlsx
    9.5 KB · Views: 25
Last edited:
You must log in or register to reply here.
Back
Top