Reverse Cumulative Row Sums

The cumulative sum across rows is given in problem table. Work out the individual numbers from the Problem table.

📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 194
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Reverse Cumulative Row Sums with Power Query

Power Query solution 1 for Reverse Cumulative Row Sums, proposed by Zoran Milokanović:

let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  R = Table.ToRows(Source), 
  S = Table.FromRows(
    List.Transform(
      R, 
      each List.Transform(
        List.Positions(_), 
        (p) =>
          let
            r = List.PositionOf(R, _)
          in
            {_{p} - _{p - 1}, _{p} - List.Last(R{r - 1}), _{p}}{
              List.PositionOf({p > 1, p = 1 and r > 0, true}, true)
            }
      )
    ), 
    Table.ColumnNames(Source)
  )
in
  S

Power Query solution 2 for Reverse Cumulative Row Sums, proposed by Zoran Milokanović:

let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  S = Table.ReplaceValue(
    Source, 
    0, 
    0, 
    (x, y, z) =>
      x
        - List.Max(
          List.Select(
            List.TransformMany(Table.ToRows(Source), each List.Skip(_), (i, _) => _), 
            each _ < x
          ), 
          0
        ), 
    List.Skip(Table.ColumnNames(Source))
  )
in
  S

Power Query solution 3 for Reverse Cumulative Row Sums, proposed by Kris Jaganah:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Unpivot = Table.UnpivotOtherColumns(Source, {"Date"}, "Attribute", "Value"), 
  Each = Table.TransformColumns(
    Unpivot, 
    {
      "Value", 
      each 
        let
          a = Unpivot[Value]
        in
          List.Transform({0 .. List.Count(a) - 1}, each try a{_} - a{_ - 1} otherwise List.First(a)){
            List.PositionOf(a, _)
          }
    }
  ), 
  Pivot = Table.Pivot(Each, List.Distinct(Each[Attribute]), "Attribute", "Value", List.Sum)
in
  Pivot

Power Query solution 4 for Reverse Cumulative Row Sums, proposed by Aditya Kumar Darak 🇮🇳:

Alejandro Simón, Awesome use of List.Transform function.

Power Query solution 5 for Reverse Cumulative Row Sums, proposed by Aditya Kumar Darak 🇮🇳:

let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Generate = List.Generate(
    () => [a = - 1, b = 0], 
    each [a] < Table.RowCount(Source), 
    each [
      a  = [a] + 1, 
      R  = Source{a}, 
      L  = Record.ToList(R), 
      S  = List.Skip(L), 
      C  = List.Count(S), 
      NL = {0} & S, 
      T  = List.Transform({1 .. C}, (f) => NL{f} - NL{f - 1} - (if f = 1 then [b] else 0)), 
      b  = List.Last(S)
    ], 
    each {[L]{0}} & [T]
  ), 
  Return = Table.FromRows(List.Skip(Generate), Table.ColumnNames(Source))
in
  Return

Power Query solution 6 for Reverse Cumulative Row Sums, proposed by Alejandro Simón 🇵🇦 🇪🇸:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Unpivot = Table.UnpivotOtherColumns(Source, {"Date"}, "A", "V"), 
  Lista = List.Transform(
    {1 .. Table.RowCount(Unpivot)}, 
    each ({0} & Unpivot[V]){_} - ({0} & Unpivot[V]){_ - 1}
  ), 
  Tbl = Table.FromColumns(
    List.RemoveLastN(Table.ToColumns(Unpivot)) & {Lista}, 
    Table.ColumnNames(Unpivot)
  ), 
  Sol = Table.Pivot(Tbl, List.Distinct(Tbl[A]), "A", "V")
in
  Sol

Power Query solution 7 for Reverse Cumulative Row Sums, proposed by Alejandro Simón 🇵🇦 🇪🇸:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Lista = List.Combine(List.Transform(Table.ToRows(Source), each List.Skip(_))), 
  OldList = List.Split(
    List.Transform({0 .. List.Count(Lista) - 1}, each try Lista{_} - Lista{_ - 1} otherwise Lista{_}), 
    3
  ), 
  Sol = Table.FromRows(
    List.Transform({0 .. List.Count(OldList) - 1}, each {Source[Date]{_}} & OldList{_}), 
    Table.ColumnNames(Source)
  )
in
  Sol

Power Query solution 8 for Reverse Cumulative Row Sums, proposed by Luan Rodrigues:

let
  Fonte = Tabela1, 
  ind = Table.AddIndexColumn(Fonte, "ind", 0, 1, Int64.Type), 
  add = Table.AddColumn(
    ind, 
    "tab", 
    each [
      a    = List.RemoveFirstN(Record.FieldValues(_)), 
      Amt1 = try Number.Abs(ind{[ind] - 1}[Amt3] - [Amt1]) otherwise [Amt1], 
      Amt2 = Number.Abs(a{0} - a{1}), 
      Amt3 = Number.Abs(a{1} - a{2})
    ]
  )[[Date], [tab]], 
  res = Table.ExpandRecordColumn(add, "tab", List.RemoveFirstN(Record.FieldNames(add[tab]{0})))
in
  res

Power Query solution 9 for Reverse Cumulative Row Sums, proposed by Luan Rodrigues:

let
  Fonte = Tabela1, 
  ind = Table.AddIndexColumn(Fonte, "ind", 0, 1, Int64.Type), 
  add = Table.AddColumn(
    ind, 
    "tab", 
    each [
      a = List.RemoveFirstN(Record.FieldValues(_)), 
      b = try
        Number.Abs(List.First(List.LastN(Record.ToList(ind{[ind] - 1}), 2)) - [Amt1])
      otherwise
        [Amt1], 
      c = List.RemoveLastN(
        List.Transform(
          List.Zip({List.RemoveLastN(a, 1), List.RemoveFirstN(a, 1)}), 
          (x) => Number.Abs(x{0} - x{1})
        )
      ), 
      d = Table.FromRows({{b} & c}, List.RemoveFirstN(Table.ColumnNames(Fonte), 1))
    ][d]
  )[[Date], [tab]], 
  res = Table.ExpandTableColumn(add, "tab", Table.ColumnNames(add[tab]{0}))
in
  res

Power Query solution 10 for Reverse Cumulative Row Sums, proposed by Alexis Olson:

let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 Unpivot = Table.UnpivotOtherColumns(Source, {"Date"}, "Amt", "Value"),
 AddIndices = Table.Buffer(Table.AddIndexColumn(Table.AddIndexColumn(Unpivot, "0", 0, 1, Int64.Type), "1", 1, 1, Int64.Type)),
 SelfJoin = Table.NestedJoin(AddIndices, {"0"}, AddIndices, {"1"}, "Unpivot", JoinKind.LeftOuter),
 Expand = Table.ExpandTableColumn(SelfJoin, "Unpivot", {"Value"}, {"Prev"}),
 Delta = Table.AddColumn(Expand, "Delta", each [Value] - ([Prev] ?? 0)),
 Cleanup = Table.SelectColumns(Delta,{"Date", "Amt", "Delta"}),
 Pivot = Table.Pivot(Cleanup, {"Amt1", "Amt2", "Amt3"}, "Amt", "Delta")
in
 Pivot

See also: https://community.fabric.microsoft.com/t5/Desktop/Calculating-Daily-values-from-Cumulative-Total/td-p/2198969

Power Query solution 11 for Reverse Cumulative Row Sums, proposed by Abdallah Ally:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  columns = Table.ColumnNames(Source), 
  a = List.Transform(
    {0 .. Table.RowCount(Source) - 1}, 
    (x) => if x = 0 then Source[Amt1]{x} else Source[Amt1]{x} - Source[Amt3]{x - 1}
  ), 
  b = List.Transform(List.Zip({Source[Amt1], Source[Amt2]}), (x) => x{1} - x{0}), 
  c = List.Transform(List.Zip({Source[Amt2], Source[Amt3]}), (x) => x{1} - x{0}), 
  d = List.Transform(List.Zip({Source[Date], a, b, c}), (x) => Record.FromList(x, columns)), 
  Result = Table.FromRecords(d)
in
  Result

Power Query solution 12 for Reverse Cumulative Row Sums, proposed by Ramiro Ayala Chávez:

let
  S   = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  a   = {0} & List.Combine(List.Transform(Table.ToRows(S), each List.Skip(_))), 
  b   = List.RemoveNulls(List.Transform(List.Positions(a), each try a{_} - a{_ - 1} otherwise null)), 
  c   = {S[Date]} & Table.ToColumns(Table.FromRows(List.Split(b, 3))), 
  Sol = Table.FromColumns(c, Table.ColumnNames(S))
in
  Sol

Power Query solution 13 for Reverse Cumulative Row Sums, proposed by Eric Laforce:

let
  Source   = Excel.CurrentWorkbook(){[Name = "tData194"]}[Content], 
  CNames   = Table.ColumnNames(Source), 
  RTValues = List.Combine(Table.ToRows(Table.RemoveColumns(Source, {"Date"}))), 
  Previous = {0} & List.RemoveLastN(RTValues, 1), 
  Values   = List.Transform(List.Zip({RTValues, Previous}), each _{0} - _{1}), 
  Split    = List.Split(Values, List.Count(CNames) - 1), 
  Rows     = List.Transform(List.Zip({Source[Date], Split}), each {_{0}} & _{1}), 
  ToTable  = Table.FromRows(Rows, CNames)
in
  ToTable

Power Query solution 14 for Reverse Cumulative Row Sums, proposed by Eric Laforce:

let
 Source = Excel.CurrentWorkbook(){[Name="tData194"]}[Content],
 Rows = Table.ToRows(Source), NRows=List.Count(Rows),
 Transform = List.Generate(()=>[r=1, p=0, s=Rows{0}], each [r]<=NRows, 
 each let 
 _RV = Rows{[r]},
 _V = List.Skip(_RV), 
 _P = {[p]} & List.RemoveLastN(_V,1), 
 _T = List.Transform(List.Zip({_V,_P}), each _{0}-_{1})
 in [r=[r]+1, p=List.Last(_RV), s={_RV{0}} & _T],
 each [s]),
 Result = Table.FromRows(Transform, Table.ColumnNames(Source))
in
 Result
Of course once more , cf also Zoran Milokanović solution 
- It's a far more compact proposal 🙌🙌 
- But still too High M-Level for me to get at 1st sight 🤯❓:😉 
=> I'll definetly retry later to decompose & understant its logic

Power Query solution 15 for Reverse Cumulative Row Sums, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:

let
  Source            = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  A                 = Table.TransformColumnTypes(Source, {{"Date", type date}}), 
  B                 = Table.UnpivotOtherColumns(A, {"Date"}, "A", "V"), 
  C                 = Table.AddIndexColumn(B, "I", 0, 1, Int64.Type), 
  D                 = Table.AddColumn(C, "V2", each try [V] - C[V]{[I] - 1} otherwise [V]), 
  E                 = Table.SelectColumns(D, {"Date", "A", "V2"}), 
  #"Pivoted Column" = Table.Pivot(E, List.Distinct(E[A]), "A", "V2", List.Sum)
in
  #"Pivoted Column"

Power Query solution 16 for Reverse Cumulative Row Sums, proposed by Rafael González B.:

let
 Source = Excel.CurrentWorkbook(){0}[Content], TTC = Table.ToColumns, LR = List.Reverse,
 Col = LR(List.Combine(List.Zip(List.RemoveFirstN(TTC(Source))))),
 LA = List.RemoveLastN(List.Accumulate(
 {1..List.Count(Col)}, {},
 (s,c) => s & {Col{c-1} - Col{c}} 
 )) & {List.Last(Col)},
 LS = Table.ReverseRows(Table.FromRows(List.Transform(List.Split(LA,3), each LR(_)))),
 Result = Table.FromColumns({Source[Date]} & TTC(LS), Table.ColumnNames(Source))
in
 Result

🧙‍♂️🧙‍♂️🧙‍♂️

Power Query solution 17 for Reverse Cumulative Row Sums, proposed by Peter Tholstrup:

let
  Source   = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  current  = List.Combine(Table.ToRows(Table.RemoveColumns(Source, {"Date"}))), 
  subtract = {0} & List.Transform(List.RemoveLastN(current), each - _), 
  values   = List.Transform(List.Zip({current, subtract}), List.Sum), 
  extract  = List.Transform({0 .. 2}, each List.Alternate(List.Skip(values, _), 2, 1, 1)), 
  result   = Table.FromColumns({Source[Date]} & extract, Table.ColumnNames(Source))
in
  result

Power Query solution 18 for Reverse Cumulative Row Sums, proposed by Peter Tholstrup:

let
  Source   = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  current  = List.Combine(Table.ToRows(Table.RemoveColumns(Source, {"Date"}))), 
  subtract = {0} & List.Transform(List.RemoveLastN(current), each - _), 
  values   = List.Transform(List.Zip({current, subtract}), List.Sum), 
  columns  = {Source[Date]} & List.Zip(List.Split(values, 3)), 
  result   = Table.FromColumns(columns, Table.ColumnNames(Source))
in
  result

Power Query solution 19 for Reverse Cumulative Row Sums, proposed by Yaroslav Drohomyretskyi:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Unpivot = Table.UnpivotOtherColumns(Source, {"Date"}, "Attribute", "Value"), 
  Values = Table.AddColumn(
    Table.AddIndexColumn(Unpivot, "Index", 0, 1, Int64.Type), 
    "Values", 
    each try [Value] - Unpivot{[Index] - 1}[Value] otherwise [Value]
  ), 
  Pivot = Table.Pivot(
    Table.RemoveColumns(Values, {"Value", "Index"}), 
    List.Distinct(Values[Attribute]), 
    "Attribute", 
    "Values", 
    List.Sum
  )
in
  Pivot

Power Query solution 20 for Reverse Cumulative Row Sums, proposed by Ahmed Ariem:

let
  source = Excel.CurrentWorkbook(){[Name = "tbl"]}[Content], 
  PrHeaders = Table.PromoteHeaders(source, [PromoteAllScalars = true]), 
  Types = Table.TransformColumnTypes(
    PrHeaders, 
    {{"Date", type date}, {"Amt1", Int64.Type}, {"Amt2", Int64.Type}, {"Amt3", Int64.Type}}
  ), 
  from = Table.AddIndexColumn(Types, "Idx", 0, 1, Int64.Type), 
  step1 = Table.ReplaceValue(
    from, 
    each [Amt3], 
    each [Amt3] - [Amt2], 
    Replacer.ReplaceValue, 
    {"Amt2", "Amt3"}
  ), 
  step2 = Table.ReplaceValue(
    step1, 
    each [Amt2], 
    each [Amt2] - [Amt1], 
    Replacer.ReplaceValue, 
    {"Amt1", "Amt2"}
  ), 
  step3 = Table.ReplaceValue(
    step2, 
    each [Amt1], 
    each [Amt1] - ({0} & List.RemoveLastN(from[Amt3], 1)){[Idx]}, 
    Replacer.ReplaceValue, 
    {"Amt1", "Amt3", "Idx"}
  )
in
  step3

Power Query solution 21 for Reverse Cumulative Row Sums, proposed by Arnaud Duvernois:

let
  Source = Excel.CurrentWorkbook(){[Name = "Tableau1"]}[Content], 
  Number = List.Max(
    List.Transform(
      List.Skip(Table.ColumnNames(Source)), 
      each Number.From(Text.Select(_, {"0" .. "9"}))
    )
  ), 
  Accumulate = List.Accumulate(
    List.Reverse({2 .. Number}), 
    Source, 
    (s, c) =>
      Table.ReplaceValue(
        s, 
        each Record.Field(_, "Amt" & Text.From(c)), 
        each Record.Field(_, "Amt" & Text.From(c)) - Record.Field(_, "Amt" & Text.From(c - 1)), 
        Replacer.ReplaceValue, 
        {"Amt" & Text.From(c)}
      )
  )
in
  Accumulate

Solving the challenge of Reverse Cumulative Row Sums with Excel

Excel solution 1 for Reverse Cumulative Row Sums, proposed by Bo Rydobon 🇹🇭:

=LET(
    x,
    TOCOL(
        B2:D10
    ),
    HSTACK(
        A2:A10,
        WRAPROWS(
            x-DROP(
                VSTACK(
                    0,
                    x
                ),
                -1
            ),
            3
        )
    )
)

Excel solution 2 for Reverse Cumulative Row Sums, p&roposed by Rick Rothstein:

=LET(
    v,
    VSTACK(
        0,
        TOCOL(
            B2:D10
        )
    ),
    HSTACK(
        A2:A10,
        WRAPROWS(
            DROP(
                DROP(
                    v,
                    1
                )-v,
                -1
            ),
            3
        )
    )
)

Excel solution 3 for Reverse Cumulative Row Sums, proposed by محمد حلمي:

=HSTACK(A2:A10,B2:B10-N(+D1:D9),C2:D10-B2:C10)

Excel solution 4 for Reverse Cumulative Row Sums, proposed by Julian Poeltl:

Excel solution 5 for Reverse Cumulative Row Sums, proposed by Oscar Mendez Roca Farell:

Excel solution 6 for Reverse Cumulative Row Sums, proposed by Sunny Baggu:

Excel solution 7 for Reverse Cumulative Row Sums, proposed by Sunny Baggu:

=HSTACK(A2:A10, VSTACK(B2, B3:B10 - D2:D9), C2:D10 - B2:C10)

Excel solution 8 for Reverse Cumulative Row Sums, proposed by Abdallah Ally:

Excel solution 9 for Reverse Cumulative Row Sums, proposed by Md. Zohurul Islam:

=LET(
    u,
    A2:A10,
    v,
    B2:D10,
    hdr,
    A1:D1,
    
    a,
    TOCOL(
        v
    ),
    
    b,
    VSTACK(
        TAKE(
            a,
            1
        ),
        DROP(
            a,
            1
        )-DROP(
            a,
            -1
        )
    ),
    
    c,
    VSTACK(
        hdr,
        HSTACK(
            u,
            WRAPROWS(
                b,
                COUNTA(
                    hdr
                )-1
            )
        )
    ),
    
    c
)

Excel solution 10 for Reverse Cumulative Row Sums, proposed by Md. Zohurul Islam:

=LET(
    z,
    A2:D10,
    hdr,
    A1:D1,
    cc,
    CHOOSECOLS,
    
    a,
    cc(
        z,
        1
    ),
    b,
    cc(
        z,
        2
    ),
    c,
    cc(
        z,
        3
    ),
    d,
    VSTACK(
        0,
        DROP(
            cc(
                z,
                4
            ),
            -1
        )
    ),
    
    e,
    HSTACK(
        a,
        b-d,
        c-b,
        cc(
                z,
                4
            )-c
    ),
    
    VSTACK(
        hdr,
        e
    )
)

Excel solution 11 for Reverse Cumulative Row Sums, proposed by Hamidi Hamid:

Excel solution 12 for Reverse Cumulative Row Sums, proposed by ferhat CK:

=LET(
    a,
    MAP(
        TOCOL(
            B2:D10
        ),
        DROP(
            TOCOL(
            B2:D10
        ),
            1
        ),
        LAMBDA(
            x,
            y,
            y-x
        )
    ),
    b,
    VSTACK(
        B2,
        TAKE(
            a,
            COUNTA(
                a
            )-1
        )
    ),
    VSTACK(
        {"Date",
        "Amt1",
        "Amt2",
        "Amt3"},
        HSTACK(
            A2:A10,
            WRAPCOLS(
                b,
                3
            )
        )
    )
)

Excel solution 13 for Reverse Cumulative Row Sums, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:

=VSTACK(
    HSTACK(
        "Date",
        "Amt1",
        "Amt2",
        "Amt3"
    ),
    HSTACK(
        TEXT(
            A2:A10,
            "mm/dd/yyyy"
        ),
        IF(
            ISNUMBER(
                B2:B10
            ),
            B2:B10,
            0
        )-IF(
            ISNUMBER(
                D1:D9
            ),
            D1:D9,
            0
        ),
        MAP(
            C2:D10,
            B2:C10,
            LAMBDA(
                x,
                y,
                x-y
            )
        )
    )
)

Excel solution 14 for Reverse Cumulative Row Sums, proposed by Peter Bartholomew:

=MAP(
    
     SCAN(
         
          LAMBDA(
              {0; 0}
          ),
         
          amounts,
         
          LAMBDA(
              acc,
               value,
              
               LET(
                   
                    diff,
                    @acc(),
                   
                    prev,
                    @TAKE(
                        acc(),
                         -1
                    ),
                   
                    LAMBDA(
                        VSTACK(
                            value - prev,
                             value
                        )
                    )
                    
               )
               
          )
          
     ),
    
     LAMBDA(
         v,
          @v()
     )
    
)
The problem is that I need both the difference and the previous value (i.e.,
     an array of arrays)

Excel solution 15 for Reverse Cumulative Row Sums, proposed by Erik Oehm:

=LET(
    
     _Inputs,
     A2:D10,
    
     _Dates,
     TAKE(
         _Inputs,
          ,
          1
     ),
    
     _Amounts,
     DROP(
         _Inputs,
          ,
          1
     ),
    
     _Change,
     _Amounts - Lag(
         _Amounts,
          1,
          0
     ),
    
     _Return,
     HSTACK(
         _Dates,
          _Change
     ),
    
     _Return
    
)

Excel solution 16 for Reverse Cumulative Row Sums, proposed by Erik Oehm:

=HSTACK(A2:A10,B2:D10-Lag(B2:D10,,0))

Solving the challenge of Reverse Cumulative Row Sums with Python

Python solution 1 for Reverse Cumulative Row Sums, proposed by Konrad Gryczan, PhD:

import pandas as pd
path = 'PQ Challenge_194.xlsx'
input = pd.read_excel(path, usecols = "A:D")
test  = pd.read_excel(path, usecols = "F:I") 
test.columns = input.columns
result = input.melt(id_vars=['Date'], var_name='Amt', value_name='Value') 
 .sort_values(['Date', 'Amt']).reset_index(drop = True) 
 .assign(val=lambda x: x['Value'].shift(fill_value=0),
 diff=lambda x: x['Value'] - x['val']) 
 .drop(columns=['Value', 'val']) 
 .pivot(index='Date', columns='Amt', values='diff').reset_index(drop=False) 
print(result.equals(test)) # True

Solving the challenge of Reverse Cumulative Row Sums with Python in Excel

Python in Excel solution 1 for Reverse Cumulative Row Sums, proposed by Alejandro Campos:

df = xl("A1:D10", headers=True)
df = df.assign(Amt4=df['Amt1'].where(pd.isna(df['Amt1'] -
 df['Amt3'].shift(1)), df['Amt1'] - df['Amt3'].shift(1)),
 Amt5=df['Amt2'] - df['Amt1'],
 Amt6=df['Amt3'] - df['Amt2']).iloc[:, [0, 4, 5, 6]]
df.columns = df.columns[:4]
df

Python in Excel solution 2 for Reverse Cumulative Row Sums, proposed by Abdallah Ally:

import pandas as pd
file_path = 'PQ Challenge_194.xlsx'
df = pd.read_excel(file_path, usecols='A:D')
# Perform data wrangling
columns = df.columns
# Add three new columns
cond = df['Amt1'] - df['Amt3'].shift(1)
df['Amt4'] = df['Amt1'].where(pd.isna(cond), cond)
df['Amt5'] = df['Amt2'] - df['Amt1']
df['Amt6'] = df['Amt3'] - df['Amt2']
df = df.iloc[:, [0, 4, 5, 6]]
df.columns = columns
 
df

Solving the challenge of Reverse Cumulative Row Sums with R

R solution 1 for Reverse Cumulative Row Sums, proposed by Konrad Gryczan, PhD:

library(tidyverse)
library(readxl)
path = "Power Query/PQ Challenge_194.xlsx"
input = read_xlsx(path, range = "A1:D10")
test = read_xlsx(path, range = "F1:I10")
result = input %>%
 pivot_longer(cols = -c(1), names_to = "Amt", values_to = "Value") %>%
 mutate(val = lag(Value, default = 0),
 diff = Value - val) %>%
 select(-c(Value, val)) %>%
 pivot_wider(names_from = Amt, values_from = diff)
identical(result, test) 
# [1] TRUE

Solving the challenge of Reverse Cumulative Row Sums with Excel VBA

Excel VBA solution 1 for Reverse Cumulative Row Sums, proposed by Rushikesh K.:

Sub xyz()
 Dim ws As Worksheet
 Dim lastRow As Long
 Dim i As Long
 Set ws = ThisWorkbook.Sheets("Sheet1") 
 lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
 ws.Columns("A").Copy Destination:=ws.Columns("F")
 
 ws.Range("B2").Copy Destination:=ws.Range("G2") 
 For i = 2 To lastRow
 ws.Cells(i, "H").Value = ws.Cells(i, "C").Value - ws.Cells(i, "B").Value
 Next i
 
 For i = 2 To lastRow
 ws.Cells(i, "I").Value = ws.Cells(i, "D").Value - ws.Cells(i, "C").Value
 Next i
 
 For i = 3 To lastRow
 ws.Cells(i, "G").Value = ws.Cells(i, "B").Value - ws.Cells(i - 1, "D").Value
 Next i
End Sub

Excel VBA solution 2 for Reverse Cumulative Row Sums, proposed by Kosiso Anyaegbunam:

Sub ExcelChallenge()
 Dim resizeArg1 As Integer
 Dim resizeArg2 As Integer
 Dim Cell As Range
 Dim dataRange As Range
 last cell that was just looped through
 resizeArg1 = Range("A1").CurrentRegion.Rows.Count - 1
 resizeArg2 = Range("A1").CurrentRegion.Columns.Count - 1
 
 Set dataRange = Range("A1").CurrentRegion.Resize(resizeArg1, resizeArg2).Offset(1, 1)
 
 For Each Cell In dataRange
 If dataRange(1, 1) <> Cell Then
 Cell.Offset(0, 5).Value = Cell.Value - lastCell
 Else
 Cell.Offset(0, 5).Value = Cell.Value
 End If
 
 lastCell = Cell.Value
 Next Cell
End Sub