+ Reply to Thread
Results 1 to 16 of 16

Apply cumulative total until reach threshold which is two values

  1. 12-18-2019, 04:31 AM #1
    flstephenh
    flstephenh is offline
    Registered User
    Join Date
    12-18-2019
    Location
    South Africa
    MS-Off Ver
    2010
    Posts
    8

    Apply cumulative total until reach threshold which is two values

    Hello,
    Column A is monthly movement (ie. -32.04, 39.00, 21.00, -2.00, -10.80, 30.00).
    Column B is cumulative sum of column A (-32.04, 6.96, 27.96, 25.96, 15.16, 45.16)
    Column C is where formula is to apply value of Column B if value of Column B if greater than -20.00 and greater +20.00.
    Appreciate the assistance.
    Attached Files Attached Files
    Last edited by flstephenh; 12-18-2019 at 04:52 AM.
    Reply With QuoteRegister To Reply
  2. 12-18-2019, 04:33 AM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2412 (Windows 11 24H2 64-bit)
    Posts
    88,728

    Re: Apply cumulative total until reach threshold which is two values

    Welcome to the forum.

    Not sure what you mean, since a value greater than +20 will automatically be greater than -20. Do you mean between -20 and +20?
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    NB:     as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 12-18-2019, 04:34 AM #3
    flstephenh
    flstephenh is offline
    Registered User
    Join Date
    12-18-2019
    Location
    South Africa
    MS-Off Ver
    2010
    Posts
    8

    Re: Apply cumulative total until reach threshold which is two values

    Correct, so 21 or -21 would meet the criteria
    Reply With QuoteRegister To Reply
  4. 12-18-2019, 04:37 AM #4
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2412 (Windows 11 24H2 64-bit)
    Posts
    88,728

    Re: Apply cumulative total until reach threshold which is two values

    So not between -20 and +20, then? You mean LESS THAN -20 OR greater than 20?
    Reply With QuoteRegister To Reply
  5. 12-18-2019, 04:37 AM #5
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2412 (Windows 11 24H2 64-bit)
    Posts
    88,728

    Re: Apply cumulative total until reach threshold which is two values

    Try this:

    =IF(OR(B1>20,B1<-20),B1,A1)
    Reply With QuoteRegister To Reply
  6. 12-18-2019, 04:53 AM #6
    flstephenh
    flstephenh is offline
    Registered User
    Join Date
    12-18-2019
    Location
    South Africa
    MS-Off Ver
    2010
    Posts
    8

    Re: Apply cumulative total until reach threshold which is two values

    Hi,
    Me bad.
    Have attached an example file to hopefully explain my request better.
    Reply With QuoteRegister To Reply
  7. 12-18-2019, 05:01 AM #7
    flstephenh
    flstephenh is offline
    Registered User
    Join Date
    12-18-2019
    Location
    South Africa
    MS-Off Ver
    2010
    Posts
    8

    Re: Apply cumulative total until reach threshold which is two values

    Hi AliGW,

    Thanks for the suggestion.

    Inserted your suggestion onto the attachment, and unfortunately does not provide the results as hard keyed on the attachment.

    Regards
    S
    Reply With QuoteRegister To Reply
  8. 12-18-2019, 05:03 AM #8
    samba_ravi
    samba_ravi is offline
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,920

    Re: Apply cumulative total until reach threshold which is two values

    =IF(ABS(B2)>=20,"Meets Criteria","Cumulative Calc Req")
    Try above in D2, Copy and paste towards down
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.
    Reply With QuoteRegister To Reply
  9. 12-18-2019, 05:16 AM #9
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2412 (Windows 11 24H2 64-bit)
    Posts
    88,728

    Re: Apply cumulative total until reach threshold which is two values

    Quote Originally Posted by flstephenh View Post
    Inserted your suggestion onto the attachment, and unfortunately does not provide the results as hard keyed on the attachment.
    There was no attachment when I offered my solution - it was added after I did so.
    Reply With QuoteRegister To Reply
  10. 12-18-2019, 05:37 AM #10
    flstephenh
    flstephenh is offline
    Registered User
    Join Date
    12-18-2019
    Location
    South Africa
    MS-Off Ver
    2010
    Posts
    8

    Re: Apply cumulative total until reach threshold which is two values

    Quote Originally Posted by samba_ravi View Post
    =IF(ABS(B2)>=20,"Meets Criteria","Cumulative Calc Req")
    Try above in D2, Copy and paste towards down
    Hi,

    Thanks for the above formula, it works, however I am looking for a formula for column B.
    Reply With QuoteRegister To Reply
  11. 12-18-2019, 05:38 AM #11
    flstephenh
    flstephenh is offline
    Registered User
    Join Date
    12-18-2019
    Location
    South Africa
    MS-Off Ver
    2010
    Posts
    8

    Re: Apply cumulative total until reach threshold which is two values

    Hi AliGW,

    Took a chance it would work.

    Rgds
    S
    Reply With QuoteRegister To Reply
  12. 12-18-2019, 05:47 AM #12
    samba_ravi
    samba_ravi is offline
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,920

    Re: Apply cumulative total until reach threshold which is two values

    =IF(IFERROR(ABS(B1),0)>=20,A2,SUM(D1,A2))
    Try the above in B2, copy and paste towards down
    Reply With QuoteRegister To Reply
  13. 12-18-2019, 07:57 AM #13
    flstephenh
    flstephenh is offline
    Registered User
    Join Date
    12-18-2019
    Location
    South Africa
    MS-Off Ver
    2010
    Posts
    8

    Re: Apply cumulative total until reach threshold which is two values

    Hi Samba,

    Copy pasted your formula into the example I provided and the results were the same as that in column A.

    So sorry the solution you provided did not work as I expected.

    Looking for a formula that will sum cumulatively until it reaches the criteria of either greater than 20 or less than -20 (such as -21).
    When it does to provide the calculated sum and then on the next row start again with the cumulative sum until is again reaches the criteria.

    Rgds,
    S
    Reply With QuoteRegister To Reply
  14. 12-18-2019, 08:14 AM #14
    samba_ravi
    samba_ravi is offline
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,920

    Re: Apply cumulative total until reach threshold which is two values

    =IF(IFERROR(ABS(B1),0)>=20,A2,SUM(D1,A2))
    change D1 to A1
    see the attached file
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  15. 12-19-2019, 02:37 AM #15
    flstephenh
    flstephenh is offline
    Registered User
    Join Date
    12-18-2019
    Location
    South Africa
    MS-Off Ver
    2010
    Posts
    8

    Re: Apply cumulative total until reach threshold which is two values

    Hi,

    Thank you AliGW & Samba, after some adjustments on my side, I combined the two formulas and they work nicely.

    Have a great day.

    Rgds,
    S
    Reply With QuoteRegister To Reply
  16. 12-19-2019, 02:41 AM #16
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2412 (Windows 11 24H2 64-bit)
    Posts
    88,728

    Re: Apply cumulative total until reach threshold which is two values

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Happy Christmas!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Sum a column of values to show total minutes over a certain threshold
    By VH19845150 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 07-16-2018, 06:56 AM
  2. tiered commision cumulative threshold
    By danns281 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-10-2013, 03:51 AM
  3. [SOLVED] VBA to split data and apply a percentage sum to total values
    By kenadams378 in forum Excel Programming / VBA / Macros
    Replies: 72
    Last Post: 08-01-2012, 08:45 AM
  4. Subtracting values in cells from a total and counting the number of cells to reach <0
    By TonyClark in forum Excel General
    Replies: 0
    Last Post: 05-08-2012, 12:42 PM
  5. Using a loop to add '0's in between two other numbers to reach 12 digits total
    By giallofever in forum Excel General
    Replies: 4
    Last Post: 03-25-2009, 10:12 AM
  6. How do I automatically total and flag charges when they reach a s.
    By slw66 in forum Excel General
    Replies: 4
    Last Post: 02-22-2005, 05:06 PM
  7. [SOLVED] Count number to reach a cumulative value
    By Bruce in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-25-2005, 02:06 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1