Summing abolsute differences between losts of sources

[Yonez]

Not sure if that title makes any sense, but let me try and explain.

Basically, I've done a questionnaire, and each of the candidate's responses to each question are in numerical form, which the first table shows (as per attached file). What I want to do, is judge each person's responses against each other persons in an absolute sense, and have the sum of those figures displayed in the second table.

For example, I would first want to compare candidate 1 and candidate 2's response, and the sum of all the absolutes for this (2-7, 5-8, 3-1, 12-1, 6-9) would be 24, which I would need entered into C16.

Then I would compare candidate 1 and candidate 3, and the answer I want of 18 would be displayed in C17

Then compare all the other candidates against all the other candidates, and populate the rest of the second table.

If it were just that table attached, then it'd be fine, but it's for 45 questions and around 100 candidates, so It'd be great if there is an automatic way to do this.

If that doesn't make any sense I'll be happy to clarify.

Cheers.

*edit* The attachment didn't seem to work, here's an imageshack link to a picture of what I'm talking about:

http://img67.imageshack.us/img67/6618/exceloz8.png

[duane]

an array function such as this will sum the absolute of the difference of ranges - when you enter the formaul push control+shift+enter at once and {} will appear around the formula

=SUM(ABS(B9:F9-B10:F10))

the two ranges must be of the same dimension (#of colums/rows)

[Yonez]

Ah, yes, that seems to be exactly what I want to do.

One thing, though; Is there an easy way to copy that into the rest of the table? I found when trying to enter the $ sign to use absolute referencing, it screwed up for some reason. Is there a quick way to copy the formula downwards, yet still retain the relevant cell references?

(In the sense that, instead of having the results of [cand.1 vs. cand.3] below [cand.1 vs. cand.2], I was actually getting [cand.2 vs. cand.3] when not using the $ sign.)

[duane]

it sound slike you want

SUM(ABS(B$9:F$9-B10:F10))

where row 9 has cand1 responses

[Yonez]

I tried what you just said first, but it didn't work, and it took me ages to realise I needed to press CTRL+SHIFT after editing the cells again!

Hah, one more thing, though.

To save me a bit more time, is there a way to make it so that when I copy a formula from left to right, instead of the column reference increasing, the row reference increases?

ie.

In D17 I need:

{=SUM(ABS($C$6:$H$6-$C6:$H6))}

Then in E17 I need:

{=SUM(ABS($C$7:$H$7-$C6:$H6))}

And so on and so forth. Is there a way to do this?

[duane]

the easy way to do it if possible is to copy the formula from D17 DOWN for as many rows as you need and then copy D18 and below and past special (check transpose) into E17.