Forum Discussion

Excelhelp1225's avatar
Excelhelp1225
Copper Contributor
Feb 27, 2022
Solved

Need help with complex Excel formula

I need a formula to sum columns B and C for a given row and then subtract the above values in column D only for the rows that have the same value in column A. See column D for desired outcome of formula. Thanks in advance for your help.

excel
Formulas and Functions

5 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Feb 27, 2022
    Spoiler
     

    Excelhelp1225 

    I can't help feeling that the original formula is expressed in a manner that is unnecessarily complex.  Any given number (being the sum of the two amount columns) appears in column D within the same row, appears negated in the next row, and from there on cancels out.

    Using Charles Williams's FastExcel DIFF formula gives

    = IF(DIFF(Nbr), AmtS, DIFF(AmtS,0))
where
AmtS = Amt₁+Amt₂

    Without the Add-in formula, one can 'roll one's own' Lambda function 'Diffλ' defined by

    = LAMBDA(x, LET(
  k, SEQUENCE(COUNT(x)),
  x - IF(k>1,INDEX(x,k-1))))

    giving the worksheet formula

    = IF(Diffλ(Nbr), AmtS, Diffλ(AmtS))

Resources