+ Reply to Thread
Results 1 to 4 of 4

Get the max sum of 2 or more consecutive values

  1. #1
    Registered User
    Join Date
    08-11-2020
    Location
    ESA
    MS-Off Ver
    2016
    Posts
    2

    Get the max sum of 2 or more consecutive values

    Hi All

    I'm either looking for a formula solution or vba code to calculate the data on cells L2 and M2 ( I've tried multiple formulas and I cant accomplish what I need)

    Explanation

    I've a table from column range G:I, the table is registering the days from 1 to 1000, the value is the amount sold ( it can be a credit or debit) , when is a Credit, column "I" will display "Good Day", otherwise "Bad day" for debits. All of this calculation is being processed correctly, the problem comes when I want to get the max sum value for either the consecutive "Good Days" or "Bad Days"

    Day 1 and Day 2 are consecutive "Bad Days" and the sum of both values is 200 ( so far this is the maximum sum value ), the problem comes when I get again consecutive "Bad Days" and the sum of those values is greater than my initial consecutive max sum values. Day 500 and 501 are consecutive "Bad Days" and the sum of both is 600 ( that is what I want in cell L2). The same process for "Good Day"

    Consecutive days mean more than 1 day either being "Bad Day" or "Good Day" , example, 2 days,4 days, 100 days,etc. Let's say I've got 100 consecutive "Bad Days" with a sum of 1000 , then I got 1 "God Day" and then I got 2 consecutive "Bad Days" with a sum of 5000 which is greater than the one I've got in the 100 consecutive "Bad days" before ( I will need the 5000 value on cell L2)

    On the picture below, the first 2 days are consecutive "Bad days" and the total sum is 200 ( so far this is the max value). From day 4 to day 10 there 7 consecutive "Bad Days" and the total sum is 35 ( which is less than the total of previous consecutive values which was 200). Now Days 500 and 501 are 2 consecutive "Bad Days" and the total sum is 600 which is greater than the previous 2 consecutive days on day 1 and 2. 600 will be now my max value that I need on cell L2 ( if someone change the results or values, L2 must be updated too

    I've attached an excel file with 3 different examples about how data can be distributed and what the expected results are
    Attached Files Attached Files
    Last edited by sama9000; 08-11-2020 at 11:57 PM. Reason: Attaching a file

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Get the max sum of 2 or more consecutive values

    Welcome to the forum

    Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Quang PT

  3. #3
    Registered User
    Join Date
    08-11-2020
    Location
    ESA
    MS-Off Ver
    2016
    Posts
    2

    Re: Get the max sum of 2 or more consecutive values

    Hi, I've attached an excel file with 3 examples about how data can be distributed on 3 different ways and what will be the expected result one each

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Get the max sum of 2 or more consecutive values

    This proposal employs four helper columns.
    The first helper displays the number of consecutive good days using: =IF($D2="Bad Day",0,SUM(E1,1))
    The second helper displays the sum of consecutive good days using: =IF($D2="Bad Day",0,SUM(F1,$C2))
    The third helper displays the number of consecutive bad days using: =IF($D2="Good Day",0,SUM(G1,1))
    The fourth helper displays the sum of consecutive bad days using: =IF($D2="Good Day",0,SUM(H1,$C2))
    The max value of bad days is displayed using: =MAXIFS(H2:H15,G2:G15,">1")
    The max value of good days is displayed using: =MAXIFS(F2:F15,E2:E15,">1")
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Asigning consecutive textbox values to multiple consecutive cells...
    By Hovoruha Octavian in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-02-2019, 06:31 PM
  2. Replies: 2
    Last Post: 07-30-2018, 02:21 PM
  3. Count Consecutive Values within Range of Values
    By MrRhodes2004 in forum Excel General
    Replies: 1
    Last Post: 04-06-2016, 02:23 PM
  4. Count of Values (Rows) of Most Recent Consecutive Streak (2+ Distinct Values)
    By ExcelForum88 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-13-2016, 12:26 PM
  5. Code selects first and last rows with consecutive values and with unique values
    By Ochimus in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-24-2015, 08:38 PM
  6. [SOLVED] Consecutive values
    By SgtGil in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-25-2013, 09:35 AM
  7. Summing Values after Finding Max Consecutive Values
    By pipsturbo in forum Excel General
    Replies: 6
    Last Post: 05-12-2009, 08:06 PM

Tags for this Thread

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