Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array 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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula returning the cumulative sum
On 14 jun, 21:58, vsoler wrote:
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? 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula returning the cumulative sum
ASSUMING THE RANGE IS IN A1:A5 USE THIS FORMULA
=SUM(SUBTOTAL(9,(OFFSET(A$1:A5,,,ROW(INDIRECT("1:5 ")),1)))) "vsoler" wrote: 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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula returning the cumulative sum
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. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "vsoler" wrote in message oups.com... On 14 jun, 21:58, vsoler wrote: 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? 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula returning the cumulative sum
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))) "N Harkawat" wrote: ASSUMING THE RANGE IS IN A1:A5 USE THIS FORMULA =SUM(SUBTOTAL(9,(OFFSET(A$1:A5,,,ROW(INDIRECT("1:5 ")),1)))) "vsoler" wrote: 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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula returning the cumulative sum
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 On Jun 14, 5:27 pm, JMB wrote: 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))) "N Harkawat" wrote: ASSUMING THE RANGE IS IN A1:A5 USE THIS FORMULA =SUM(SUBTOTAL(9,(OFFSET(A$1:A5,,,ROW(INDIRECT("1:5 ")),1)))) "vsoler" wrote: 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?- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula returning the cumulative sum
vsoler wrote...
.... For example, if A1:A5 contains: 3 8 5 1 6 Then, the formula should return 3 11 16 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} |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula returning the cumulative sum
On 15 jun, 00:04, Harlan Grove wrote:
vsoler wrote... ...For example, if A1:A5 contains: 3 8 5 1 6 Then, the formula should return 3 11 16 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} Harlan, I really like your proposed solution, it works really well. Thank you |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula returning the cumulative sum
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula returning the cumulative sum
Also non-volatile (and assuming no negative values):
=PROB(ROW(A1:A5),A1:A5/SUM(A1:A5),,ROW(A1:A5))*SUM(A1:A5) On 14 Jun, 20:58, vsoler wrote: 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? |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula returning the cumulative sum
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 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula returning the cumulative sum
Would you have posted that if Bernd's had been 2500?
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Bernd P" wrote in message oups.com... 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 |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula returning the cumulative sum
Bernd P wrote...
.... 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 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. |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula returning the cumulative sum
On 15 jun, 10:38, Bernd P wrote:
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 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. |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula returning the cumulative sum
Actually the nonnegative condition is not needed. PROB can take any
values positive or negative contrary to what the help file might say. |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula returning the cumulative sum
Hello Bob,
Why not? Harlan's sum(sum(...)) takes only 0.92ms. Regards, Bernd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Returning an Array from a called function within a macro | New Users to Excel | |||
Returning an address from an array | Excel Discussion (Misc queries) | |||
Returning an array from the INDEX function | Excel Worksheet Functions | |||
Array formula returning wrong results | Excel Discussion (Misc queries) | |||
Array formula returning terminated employees | Excel Worksheet Functions |