# Array formula to calculate the highest cumulative value

1. ## Array formula to calculate the highest cumulative value

Hi,

I have a column of numbers that I need to accumulate using the formula:

=(product(1+(a1:a2000)/100)-1)*100

Can someone please help me with an array formula to calculate the maximum accumulated value of the column of values?

Thanks!  Register To Reply

2. Originally Posted by andrewc
Hi,

I have a column of numbers that I need to accumulate using the formula:

=(product(1+(a1:a2000)/100)-1)*100

Can someone please help me with an array formula to calculate the maximum accumulated value of the column of values?

Thanks!
Hi,

It's not immediately clear (at least to me) what you're wanting here. The PRODUCT() function will as I guess you know multiply a series of numbers. But what do you mean by wanting a maximum value?

There is only one result from a PRODUCT(range) function.  Register To Reply

3. If you mean that you want to calculate that result for A1:A2 then A1:A3, then A1:A4 etc. all the way to A1:A2000...and then get the maximum of those then I imagine the simplest way would be to use this formula in B1

=(product(1+(a\$1:a1)/100)-1)*100

confirmed with CTRL+SHIFT+ENTER and copied down to B2000, then you get the maximum with

=MAX(B1:B2000)  Register To Reply

4. Sorry that my original post wasn't clear!

That's exactly what I'm trying to do, except in one formula rather than in two steps as you suggested.

Thanks!  Register To Reply

5. Hi,

Anyone have any ideas?

Thanks  Register To Reply

6. Hi,

It's not obvious to me that you can do it in one step. The Max function needs a list to work with, but the array Product function isn't capable as far as I can see, of returning a set of array values for a changing range of cells.

I'll be extremely interested though to see if there is a single cell solution.

Rgds  Register To Reply

7. Thanks Richard  Register To Reply