Results 1 to 5 of 5

Thread: Cumulative sum, based on previous data

  1. #1

    Cumulative sum, based on previous data



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Ok, I'm sure this is pretty easy to figure out but this has been bugging me for a while.

    If you look at the attached file, I have twelve months of data of various amounts. What I would like to do is to report ZERO for Jan as the amount if less than 0.5; report ZERO for Feb as the total of Jan and Feb is still less than 0.5. However, for Mar I'd like to report 0.7 as that is the total of Jan, Feb, and Mar and is over 0.5. For Apr I would like to report 1.2 as that amount is already higher than 0.5; May ZERO and Jun 1.3 and so on and so fourth

    I have to do this for over 10k lines of data and would love an easier way of formulating it so I know what needs to be reported on any given month

    Thank you so much for your help
    Attached Files Attached Files

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,468
    Articles
    0
    Excel Version
    Excel 2016
    Do you mean?

    =IF(SUM($C3:C3)<0.5,0,SUM($C3:C3))

    copied across


  3. #3
    HI NBVC, that is kind of what of want, but i need the formula to sum up those months where the data is less than 0.5 - for example Jan 0.4 (report as zero) Feb 0.2 (add this with Jan and report as 0.6) April 0.2 (report as zero) May 0.2 (report as zero) Jun 1.4 (add Apr and May and report as 1.8)

    Hope this makes sense

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,468
    Articles
    0
    Excel Version
    Excel 2016
    I am having a bit of a brain freeze this morning... I will see what I can come up with eventually... unless others have any suggestions...


  5. #5
    Hello,
    Quote Originally Posted by downhillsheep View Post
    HI NBVC, that is kind of what of want, but i need the formula to sum up those months where the data is less than 0.5 - for example Jan 0.4 (report as zero) Feb 0.2 (add this with Jan and report as 0.6) April 0.2 (report as zero) May 0.2 (report as zero) Jun 1.4 (add Apr and May and report as 1.8)

    Hope this makes sense
    In your posted data, shouldn't the value for September be 0.6?

    If so, try the following formula in C5, and copy to the right. (This assumes that your data range starts in col B3.)
    =IF(SUM(OFFSET($B$3,0,INDEX(MAX(MAX(IF($B5:B5>0, COLUMN($B5:B5))),COLUMN($B$3))-COLUMN($B$3)+1, 1),1,INDEX(COLUMN()-MAX(MAX(IF($B5:B5>0, COLUMN($B5:B5))),COLUMN($B$3)), 1)))>=0.5, SUM(OFFSET($B$3,0,INDEX(MAX(MAX(IF($B5:B5>0, COLUMN($B5:B5))),COLUMN($B$3))-COLUMN($B$3)+1, 1),1,INDEX(COLUMN()-MAX(MAX(IF($B5:B5>0, COLUMN($B5:B5))),COLUMN($B$3)), 1))), 0)

    enter with Ctrl + Shift + Enter (instead of Enter)


    I will give some thought to simplifying the formula... but in the meantime, let me know if you are getting the results you need.

    Cheers,
    Sajan.

Posting Permissions

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