count the sum of difference between two cells-CodePudding

hello i worte a funnction that sums ahe abslute diffrence betwen two cells each time and then i subtact 70000 from the sum i would like to to this in vba more aesthetic

70000-(IF(D2>0,ABS(D2-C2)) IF(E2>0,ABS(E2-D2)) IF(F2>0,ABS(F2-E2)) IF(G2>0,ABS(G2-F2)) IF(H2>0,ABS(H2-G2)) IF(I2>0,ABS(I2-H2)) IF(J2>0,ABS(J2-I2)) IF(K2>0,ABS(K2-J2)) IF(L2>0,ABS(L2-K2)) IF(M2>0,ABS(M2-L2)) IF(N2>0,ABS(N2-M2)) IF(O2>0,ABS(O2-N2)) )

CodePudding user response：

Maybe a bit off-topic since you specifically ask for a VBA solution, but this formula-solution would also bring aesthetic and improves calculation:

=70000-
 REDUCE(0, COLUMN(C:N),
 LAMBDA(a, b,
        LET(offset,INDEX(2:2,,b 1),
IF(offset>0,
   a ABS(offset-INDEX(2:2,,b)),
   a))))

It loops through column C:N (b) in the row mentioned (row 2:2 in this case) and checks if the value offset 1 to the right (I used INDEX to not make it volatile, but named it offset). If the value in that row/column 1 is greater than 0 than value a becomes a ABS(the value in the row/column 1 - value in that row/column), otherwise a stays the same.

CodePudding user response：

You can achieve this in VBA using a for loop:

Sub AbsoluteDifference()
    ' Store variables as double to account for large numbers and decimals
    Dim n As Double
    Dim sum As Double

    n = 70000
    sum = 0

    For Each i In Range("D2:O2").Cells
        If i.Value > 0 Then
            sum = sum   Abs(i.Value - i.Offset(0, -1).Value)
        End If
    Next i
    ' Save the value to C3
    Range("C3").Value = n - sum
End Sub

This code produces an identical result to your function.

CodePudding user response：

First, based on your formula, you rather only add the absolute difference if the value subtracted from is >0. If this is what you want, then you would have something like this:

Sub SumAbsDiff()
    Dim i As Integer
    Dim sum As Double
    
    'This loops from col D to col O
    For i = 4 To 15 Step 1
        If Cells(2, i).Value > 0 Then sum = sum   Abs(Cells(2, i).Value - Cells(2, i - 1).Value)
    Next i
    
    'Change this to the cell you would like to display the value
    Cells(1, 1).Value = 70000 - sum
End Sub

Explanation:

Here, we are taking row 2, and then looping over from D until O. Using the loop, we absolute subtract each of them (D-C, E-D, etc) if the >0 condition satisfy. The result is then add to the sum variable (which initialize as 0 by default).

After the loop is done, we just simply use it to subtract from 70,000 and then write it to the cell that we wanted.

As a side note, if your original formula was wrong, and you actually want the sum between each of the absolute differences without the >0 condition, then removing the If ... Then would do the trick.