Feb 27 2022 03:05 AM
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.
Feb 27 2022 04:38 AM
SolutionFeb 27 2022 05:27 AM
Feb 27 2022 07:01 AM
@Hans Vogelaar That worked beautifully! Thank you so much!!
Feb 27 2022 07:02 AM
Feb 27 2022 02:31 PM
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))
Feb 27 2022 04:38 AM
Solution