+ Reply to Thread
Results 1 to 6 of 6

Unique Running Total Criteria Conditonal

  1. #1
    Registered User
    Join Date
    09-04-2011
    Location
    USA
    MS-Off Ver
    97/2000/XP
    Posts
    3

    Unique Running Total Criteria Conditonal

    Hello,

    I have a unique issue dealing with a running total with criteria and conditionals to make it calculate successfully . I've spent the past two days reading over forum posts and help documents and haven't yet been able to make headway. I have experience in BASIC, but I only want to turn to VBA as a last resort, as I plan on having the spreadsheet run on smartphones by friends and fellow co-workers and would like to minimize any security risks that come with enabling macros with inexperienced end users.

    What I'm trying to do is have a column of daily totals summarized in the adjacent columns: a 7 day total and a 8 day total. It will calculate, including the current day, a running count of both the last 7 days and 8 days.

    My problem lies with having a "reset" day in which the running totals are reset and begin counting over. These "reset" days could come at any time during the month, and happen more than once a week.

    I've tried my best at attempts to create a formula string consisting of SUM, ADDRESS, MIN, MATCH and a few other functions that have resulted in failures. I can't wrap my head around this at all.

    Attached is an example of what I'm trying to do. Note that some days the totals could be zero (0) but do not denote a "reset" day. Only the daily totals column will be manipulated by the user. I would like both the 7 and 8 day totals to dynamically calculate per the user's entry.

    Any help or direction would be extremely appreciated. I'm at my wits end.

    Much Thanks,
    Jones
    Attached Files Attached Files
    Last edited by joness; 09-07-2011 at 06:44 PM. Reason: Solution Found

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Unique Running Total Criteria Conditonal

    Put this nasty formula in C2:

    =SUM(OFFSET($B$1, INDEX($A:$A, MATCH(IF(ISERROR(LOOKUP(2, 1/($B$1:$B2="RESET"))), MAX($A2-6, 1), MAX($A2-6, 1, LOOKUP(2,1/($B$1:$B2="RESET"), $A$1:$A2))), $A:$A, 0)),,,):$B2)

    In D2:

    =SUM(OFFSET($B$1, INDEX($A:$A, MATCH(IF(ISERROR(LOOKUP(2, 1/($B$1:$B2="RESET"))), MAX($A2-7, 1), MAX($A2-7, 1, LOOKUP(2,1/($B$1:$B2="RESET"), $A$1:$A2))), $A:$A, 0)),,,):$B2)

    ...then copy those two cells downward.
    Last edited by JBeaucaire; 09-05-2011 at 11:32 AM. Reason: Removed unneeded SUM()
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Unique Running Total Criteria Conditonal

    I think this one will be slightly less nasty for C2 copied down

    =SUM(INDEX(B$1:B2,MAX(ROWS(B$1:B2)+1-7,LOOKUP(9^9,IF({1,0},1,MATCH(2,INDEX(1/(B$1:B2="reset"),0)))))):B2)

    change the 7 to an 8 for D2
    Audere est facere

  4. #4
    Registered User
    Join Date
    09-04-2011
    Location
    USA
    MS-Off Ver
    97/2000/XP
    Posts
    3

    Re: Unique Running Total Criteria Conditonal

    I'd like to say first, thank you both for helping me out with this.

    I tried both of your solutions and I'm afraid I may be making mistakes. I should note that right now I'm on a linux virtual machine as I don't have access to my desktop and do not have MS Excel on hand. I'm running the latest version of OpenOffice.

    OO's syntax is a bit strange as it requires you to have semi-colons ";" instead of commas "," to separate parameters with each function in the formula. I replaced all commas with semi-colons and inserted both of your respective formulas into C2.

    I received an error of 511 for JBeaucaire's solution, and #VALUE! for daddylonglegs's. I'm quite certain it is an error with OpenOffice and I'll work to debug it as soon as I possibly can or find access to Excel. Unfortunately my options are a bit limited as I'm away from home.

    After I made my initial post, I did make some headway. It seems I was finally able to string a successful formula together that seems to be working. I've tested it and it seems to be successful. However I used INDIRECT and strings (ie: "$B$2", "$B$8", etc) that must be changed by hand per each new cell in the column.

    Below is my solution; however I have a feeling that it isn't the most clean or optimized way of getting to my destination. Note that I'm having to use semi-colons to seperate parameters per OpenOffice's formula syntax requirements.

    In C8:
    =SUM(INDIRECT(IF(SUMPRODUCT(MAX((B2:B8="RESET")*(ROW(B2:B8))))=0;"$B$2";"$B$"&SUMPRODUCT(MAX((B2:B8="RESET")*(ROW(B2:B8))))+1)&":"&"$B$8"))

    It's a dirty hack for sure. I have to change by hand each string reference to cells. As well, for the first 7 entries I have to manually manipulate the range so that my range is limited to each respective count of the total days to count.

    I'll analyze both of your solutions to see if I can successfully recreate them in OpenOffice without errors.

    Thank you both again,
    Jones

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Unique Running Total Criteria Conditonal

    The LOOKUP() function nested inside my formula is a bit of an Excel trick, I wouldn't think OpenOffice would behave the same out of the gate. Good luck on the conversion process.

    If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

  6. #6
    Registered User
    Join Date
    09-04-2011
    Location
    USA
    MS-Off Ver
    97/2000/XP
    Posts
    3

    Re: Unique Running Total Criteria Conditonal

    Sorry for the late edit; I have been traveling all this week.

    Between my fix and the excel solution, I believe this has been resolved.

    Thanks again,
    Jones

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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