 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / June 2007Array formula returning the cumulative sum
Because I am studying in depth the array formulas, I would like to know if it is possible to build a formula such that, given a set of numbers in a vertical range (1 column wide), it returns an array with the cumulative sum. For example, if A1:A5 contains: 3 8 5 1 6 Then, the formula should return 3 11 16 17 23 Of course, I know that the goal can be achieved by mean of a simple formula: =SUM($A$1:A1) copied down However, I keep an interest (let's call it theoretical) in knowing if the exercise is possible? Can anybody help? > Because I am studying in depth the array formulas, I would like to > know if it is possible to build a formula such that, given a set of [quoted text clipped - 26 lines] > > Can anybody help? Perhaps I should explain a little further. I have the impression that array formulas cannot handle correctly the difference between absolute and relative references. They are only able to iterate a single cell reference inside a range of cells. For example, if I write {=SUMIF(A1:A5,A1:A5)} entered with Ctrl- Shift-Enter, the array formula takes one single cell at a time inside the A1:A5 range and makes the comparison, thats all. In this case, array calculating mean iterating. If I write {=SUMIF($A$1:$A$5,$A$1:$A$5)} entered with Ctrl-Shift- Enter I get exactly the same result. Conclusion: there is no real handling of absolute and relative references. Even if you find no solution to my question, I would like to have your feedback, I would appreciate it. Thank you As far as I can see, no function differentiates between absolute and relative references. A1 is the same cell as $A$1. It becomes relevant when copying that cell reference to another cell, not in the evaluation of a formula.  SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) >> Because I am studying in depth the array formulas, I would like to >> know if it is possible to build a formula such that, given a set of [quoted text clipped - 46 lines] > > Thank you ASSUMING THE RANGE IS IN A1:A5 USE THIS FORMULA =SUM(SUBTOTAL(9,(OFFSET(A$1:A5,,,ROW(INDIRECT("1:5")),1)))) > Because I am studying in depth the array formulas, I would like to > know if it is possible to build a formula such that, given a set of [quoted text clipped - 26 lines] > > Can anybody help? I think the OP just wants to return the entire array, so I took the liberty of removing the sum from your formula. =SUBTOTAL(9,(OFFSET(A$1:A5,,,ROW(INDIRECT("1:5")),1))) > ASSUMING THE RANGE IS IN A1:A5 USE THIS FORMULA > [quoted text clipped - 30 lines] > > > > Can anybody help? I think this should work for any number of rows, provided the top row is in row 1. If the top row is not row 1, then the row() arugument will need to be adjusted. =SUM(OFFSET(A$1,0,0,ROW(),1)) entered as an array function of course. Good luck. Ken Norfolk, Va > I think the OP just wants to return the entire array, so I took the liberty > of removing the sum from your formula. [quoted text clipped - 36 lines] > > - Show quoted text - vsoler <vicente.so...@gmail.com> wrote... ... >For example, if A1:A5 contains: > [quoted text clipped - 11 lines] >17 >23 ... FTHOI, an approach that uses no volatile functions, and could work as easily with an array as a range. If your original range were named D, then the array formula =MMULT(--(ROW(D)>=TRANSPOSE(ROW(D))),D) returns {3;11;16;17;23} > vsoler <vicente.so...@gmail.com> wrote... > [quoted text clipped - 25 lines] > > {3;11;16;17;23} Harlan, I really like your proposed solution, it works really well. Thank you Hello, Harlan's solution is fine. Another approach: =SUM($A$1:INDEX($A$1:$A$5,ROW($1:$5))) array-entered, non-volatile. Regards, Bernd Hello again, Let us have a look at current suggestions. I tested them on 1000 rows with random integers between 1 and 999. Calculation time in milliseconds (FastExcel) was: Bernd 0.96 Ken 1.25 JMB 12.32 Lori 114.84 Harlan 534.70 The (optimal?) non array solution (B1: =A1, B2: A2+B1, copy down) took 0.51 Regards, Bernd Would you have posted that if Bernd's had been 2500? SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Hello again, > [quoted text clipped - 14 lines] > Regards, > Bernd Hello Bob, Why not? Harlan's sum(sum(...)) takes only 0.92ms. Regards, Bernd Bernd P <bplumh...@gmail.com> wrote... ... >I tested them on 1000 rows with random integers between 1 and 999. > [quoted text clipped - 7 lines] >The (optimal?) non array solution (B1: =A1, B2: A2+B1, copy down) >took 0.51 OK, now try each approach with a derived array rather than a range. I won't (and didn't) claim my approach was fast, but it is flexible. Note that your approach also relies on implicit indexing. You can enter the array formula =SUM($A$1:INDEX($A$1:$A$5,ROW($A$1:$A$5))) [note: don't be overly broad with your range argument to ROW - with your original argument, $1:$5, any change in rows 1 through 5 outside column A would trigger recalculation of this array formula] in a 5- cell range, but =SUM(SUM($A$1:INDEX($A$1:$A$5,ROW($A$1:$A$5)))) would return the same thing as the previous formula, entered as an array or not. > Hello, > [quoted text clipped - 6 lines] > Regards, > Bernd Bernd, Your formula seems fine for me. It is perhaps what I was looking for (one never knows what one's is after until found). Let me say something. The level of activity and of knowledge that this group is reaching is absolutely remarkable. It has moved forward since I posted some questions some years ago. My congratulations to all of you. I have a field of interest which I may talk you about sometime in the future. In addition to learning how excel works, I'm keen of trying to describe how it should work. My personal opinion is that, putting aside perhaps the new Excel 2007, Microsoft has concentrated a lot more on coloring cells, formatting and visual effects than in increasing the power of calculation of a tool that today is used in every office (or company) in the world. I have given an eye to openoffice calc, but found that it's main purpose is replicating the way excel works. Congratulations again to you all for your good job. Also non-volatile (and assuming no negative values): =PROB(ROW(A1:A5),A1:A5/SUM(A1:A5),,ROW(A1:A5))*SUM(A1:A5) > Because I am studying in depth the array formulas, I would like to > know if it is possible to build a formula such that, given a set of [quoted text clipped - 26 lines] > > Can anybody help? Actually the nonnegative condition is not needed. PROB can take any values positive or negative contrary to what the help file might say. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.