+ Reply to Thread
Results 1 to 7 of 7

Array formula to calculate the highest cumulative value

  1. #1
    Forum Contributor
    Join Date
    02-08-2005
    MS-Off Ver
    Microsoft 365
    Posts
    810

    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!

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464
    Quote 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.

    Please clarify.

  3. #3
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    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)

  4. #4
    Forum Contributor
    Join Date
    02-08-2005
    MS-Off Ver
    Microsoft 365
    Posts
    810
    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!

  5. #5
    Forum Contributor
    Join Date
    02-08-2005
    MS-Off Ver
    Microsoft 365
    Posts
    810
    Hi,

    Anyone have any ideas?

    Thanks

  6. #6
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464
    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

  7. #7
    Forum Contributor
    Join Date
    02-08-2005
    MS-Off Ver
    Microsoft 365
    Posts
    810
    Thanks Richard

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1