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,
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,Originally Posted by andrewc
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.
Please clarify.
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)
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!
Hi,
Anyone have any ideas?
Thanks
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
Thanks Richard
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks