+ Reply to Thread
Results 1 to 6 of 6

calc value totals of streak

  1. #1
    Registered User
    Join Date
    08-24-2015
    Location
    SA
    MS-Off Ver
    2010
    Posts
    11

    calc value totals of streak

    need a hand to calc total (sum of) values related to streak
    added attachment to assist
    column A -> value being influenced by win or loss
    column B -> win or loss itself
    column C -> streak of wins or losses
    in column D or other; i need to calc the total (sum of) values related to the streak (done manually at moment)

    any advise welcome
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,065

    Re: calc value totals of streak

    9 views not one solution.
    Think you need to explain more.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Valued Forum Contributor
    Join Date
    12-28-2014
    Location
    NSW, Australia
    MS-Off Ver
    MS365
    Posts
    604

    Re: calc value totals of streak

    A better explanation of how the streaks worked out would have been good. Took me ages to see the pattern

    I've added a helper column and 3 helper rows, but it now calculates the streaks for you as well as the sum.

    Edit: Just for fun, I added a column showing how many games were in the streak.
    Attached Files Attached Files
    Last edited by Beamernsw; 01-18-2016 at 01:21 PM.

  4. #4
    Registered User
    Join Date
    08-24-2015
    Location
    SA
    MS-Off Ver
    2010
    Posts
    11

    Re: calc value totals of streak

    noted & apologies
    updated sheet attached

    this is a single player score sheet with columns as follows;
    A - date (number format as only used in calcs) = manual data
    B - actual value of game (win, loss or draw) = manual data
    D - accumulated value
    F - win as 1, draw as 0 & loss as -1
    G - helper for draw as part of win streak or loss streak
    H - calculated streak

    trying to do is to sum the total value per streak

    Special-K:- hope this helps, else let me know what other details are required

    Beamernsw:- thank you. helper column formula returns #REF in original sheet for some reason, will check again
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor
    Join Date
    12-28-2014
    Location
    NSW, Australia
    MS-Off Ver
    MS365
    Posts
    604

    Re: calc value totals of streak

    In your 2nd sample file, you could enter this next formula in Cell H5 and copy down and then delete column G if you wish, but it really makes no difference.

    =IF(F5=0,H4,IF(F5=F4,H4+1,IF(AND(F4=0,INDEX($F$1:F4,MAX(LOOKUP(2,1/($F$2:F4>0),ROW($F$2:F4)),LOOKUP(2,1/($F$2:F4<0),ROW($F$2:F4))))=F5),H4+1,1)))

    As for REF# did you add the 3 helper rows above the 1st data with 1,-1,0?

    Are my calculations working for you or do I have something not working?

  6. #6
    Registered User
    Join Date
    08-24-2015
    Location
    SA
    MS-Off Ver
    2010
    Posts
    11

    Re: calc value totals of streak

    thank you.. all working
    " , " instead of " ; "

+ 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] Calc Numbers with decimals and having the Decimal be moved to new column to be Calc
    By TwistedFaith in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-24-2015, 12:01 AM
  2. [SOLVED] current streak
    By rumihasj in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-07-2014, 01:45 PM
  3. Last X games, Max Win and Loss streak, current streak with Excel formulas
    By Biogeek1 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-12-2013, 11:00 AM
  4. How do I set some wksht formulas to calc. manually and others to calc. automatically?
    By hoboking87 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-26-2013, 08:16 PM
  5. streak
    By jimblimm in forum Excel General
    Replies: 1
    Last Post: 06-13-2012, 07:54 PM
  6. How to calc #'s giving totals 1-15=1, 16-25=2, 26-35=3, etc
    By Yang-Um in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-21-2006, 01:25 AM
  7. auto calc on, but have to edit (f2) cells to force re-calc..help!
    By Curt in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-13-2006, 02:10 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1