Subtract one row from another and add up absolute value of differences

Oh I understand. Then maybe we could utillize square and square root to get rid of the negatives. A quick test worked with this (translated by me so I don't know whether this is the correct expression)

= ROOT( ABS( SUMX2MY2( 1:1 , 2:2 ) ) )

Not sure if I'm entirely understanding your question, but you can do something like this:

Numbers does not have array functions so I don't think is possible to derive the 40 in the example with just one formula.

SG

Like SG, I don't see a way to accomplish this in a single formula. My approach is similar, but allows for negative values in the individual differences, and corrects for them in the final sum.

B4: =B2-B3

C4: =C2-C3

D4: =SUMIF(B4:C4,">=0")-SUMIF(B4:C4,"<0")

Regards,

Barry

Hi Preston,

You are moving the goalposts. Your original question was:

What I want to do is subtract the value of each cell in a row from the cell in the same column of another row, and add up the absolute values of all the differences

In my humble opinion, SGIII and Barry have given helpful solutions to your original question. Now your question has moved to:

calculating genetic distance for Y-chromosome STR haplotypes, which probably means nothing to you unless you're into genealogy.

Perhaps you should ask your new question in a genealogy forum. This is the Numbers for Mac forum. And you are correct; very few users here are qualified to calculate genetic distance for haplotypes.

Regards,

Ian

Why not just substract the sum of row 1 from the sum of row 2?

That would be something like = SUM(2:2) - SUM(1:1)

And I'm not entirely sure but I think there is a function to sum cells that are in a range of cells, meaning they don't have to be in a single column or row.

@papalapapp

"Why not just substract the sum of row 1 from the sum of row 2?"

Subtracting the sum of one row from the sum of the other gives a different result than called for. Using the examples above, the result would be 30-50 = -20 or ABS(30-50) = 20.

Using the calculations in the two examples the result is 40.

Regards,

Barry

@ papalapapp,

According to your result, I don't think that's going to do the job either.

You've calculated the differences of the absolute values, which it not the same as the absolute value of the differences of the actual values.

The three differences in your example are either 10, 30 and 10 OR -10, -30 and -10, depending which row was subtracted from the other. In eihter case, the absolute values of the differences are 10, 30 and 10, and the SUM of the absolute values of the differences is 50, not 30.

Preston's method will give the same results as Badunit's or mine.

Regards,

Barry

PS: Thanks for the German version of your formula—it gave me an Aha! moment regarding the English name of the root vegetable Mangelwurzel!

Try this formula against your coulmn of ABS formulas. Replace the B4's with whatever your last column is

=SUMIF(B2:B4,">="&B1)−SUMIF(B2:B4,"<"&B1)+B1×(COUNTIF(B2:B4,"<"&B1)−COUNTIF(B2:B 4,">="&B1))

Note that the forum seems to be adding a space in the formula in the middle of the B and 4 of the last "B4". It shouldn't be there

EDIT: Oops, replied to the wrong person.

I think there is a formula now that I know what you are subtracting from what. Give me a few minutes.

---

: )

Sorry if I added some confusion here.

HI papalapapp,

No problem.

Glückliches neues Jahr!

REgards,

Barry