Is there a way to have a cumulative formula?

Tvl_97
Tvl_97
edited 05/12/22 in Formulas and Functions

Hey everyone,

I would like to know if there's a formula that calculates the cumulative time because I have to drag it down everytime.

I have 3 columns:

1) Task

2) Task Duration

3)Cumulative time

I know that for row 2, I can just do [Task Duration]@row + [Task Duration]1, but I can't do that for row 1.

Also, by using this method, if I remove a row in the middle (i.e. row 10), everything else after it will be #BLOCKED because the reference got deleted.

Is there another method of doing it?

Tags:

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Insert an auto-number column. Then insert a text/number column (called "Row" in this example) and insert this column formula:

    =MATCH([Auto-Number]@row, Auto-Number]:[Auto-Number], 0)


    Then in the cumulative column you would enter

    =SUMIFS([Task Duration]:[Task Duration], Row:Row, @cell <= [email protected])

    thinkspi.com

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Insert an auto-number column. Then insert a text/number column (called "Row" in this example) and insert this column formula:

    =MATCH([Auto-Number]@row, Auto-Number]:[Auto-Number], 0)


    Then in the cumulative column you would enter

    =SUMIFS([Task Duration]:[Task Duration], Row:Row, @cell <= [email protected])

    thinkspi.com

  • I'm trying to understand this method and I have a couple of questions:

    Is it normal that the Auto-number column is empty?


    What is the purpose of these new columns?


    Where do I put this formula?

    =MATCH([Auto-Number]@row, Auto-Number]:[Auto-Number], 0)

    Is it in the new "Row" column?

    When I put that formula in the "Row" column, all I get is 1 for every row (Maybe because the auto-number is empty?)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Tvl_97 Once you save the sheet, the auto-number column should populate with a unique number on each row base on the order the rows were added.


    Yes. The MATCH formula goes in the Row column and will output the row number once you save and the Auto-number column populates.

    thinkspi.com

  • @Paul Newcome

    Thank you very much for your help!

  • Tvl_97
    Tvl_97
    edited 05/19/22

    Hey @Paul Newcome,

    I have another situation with the cumulative function

    Instead of 3 columns, I have 5 which are:

    1) Group

    2) Task

    3) Duration (per group)

    4) Duration (per task)

    5) Cumulative time

    The group duration is the sum of all the task duration in that specific group:

    =SUMIF(Group:Group, [email protected], [Task Duration]:[Task Duration])

    I would like the cumulative to calculate the maximum value of the group duration.

    So from rows 1 to 7, it would be 113

    Then from rows 8 to 12, it would be 113+80 = 193

    Then from rows 13 to 17, it would be 193 + 46 = 239

    Do you think it is possible to modify the cumulative formula or is there a better way to do this?

    Thank you

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Why not just run cumulative on the [Task Duration] column?

    thinkspi.com

  • Tvl_97
    Tvl_97
    edited 05/19/22

    With the values in cumulative column, I will convert them into number of days by dividing the values by 480 (8 hours) and rounding up the result.

    If I run the cumulative on [Task Duration], the groups that have a lot of tasks will be split into 2 days.

    So for a group that has multiple tasks (None of the groups' duration will go over 480), I would like to fit them into 1 day.