MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Cumulative products in arrays


Posted by Brian on August 03, 2001 10:01 PM

If I have the array {10,20,30,40} in cell A1, and I want to produce the array {10,200,6000,240000} in cell B1, can I do that by formula?

Thanks!
Brian


Posted by Mark W. on August 04, 2001 6:09 AM

Technically, you don't have the array, {10,20,30,40},
in cell A1. You either have a text representation
of an array constant (i.e., "{10,20,30,40}") in cell
A1 or A1 contains the 1st element, 10, of the array
constant using with the formula, ={10,20,30,40}.
Which is it?

Posted by Brian on August 06, 2001 7:39 AM

Mark,

Thanks for your message. I should have clarified as it does effect how you would approach the problem. Let's say A2 contains 10, B2 contains 20, C2 contains 30, and D2 contains 40. A1 is the result of highlighting the range A2:D2 and pressing ctrl-shift-enter. I would prefer to work off A1 because in practice I would want to apply intermediate operators to the range A2:D2 before attempting to do a cumulative product. But my understanding of cell A1 in this case is that it would contain the entire array but only show the value 10 on the screen. Is this the case?

Thanks again for your help!
Brian

Posted by Mark W. on August 06, 2001 11:36 AM

> my understanding... is that [cell A1] would
> contain the entire array but only show the
> value 10 on the screen.

That is not the case. A1 only contains 10.
=A1 produces 10.

Posted by Brian on August 06, 2001 12:18 PM

Mark,

I guess you're right...by highlighting the formula in A1 and pressing F9, I got the entire array... I was under the mistaken impression it would allow me to reference A1 as an array.
My bad.
Then it possible to do a cumulative product of cells A2 through D2?
Thanks!
Brian

Posted by Mark W. on August 06, 2001 3:33 PM

> Then [is] it possible to do a cumulative
> product of cells A2 through D2?

Enter =PRODUCT($A$2:A2) into cell A1 and fill right. I guess you're right...by highlighting the formula in A1 and pressing F9, I got the entire array... I was under the mistaken impression it would allow me to reference A1 as an array.