summing averages of paired data

**sesquiup** · 10-23-2007, 02:27 AM

I have two sets of data in the same row, say A1:J1 and then K1:U1. These are paired, so that A1 goes with K1, B1 with L1, etc.

I want to take the average of each pair, and then sum the averages, say like this:

=average(A1,K1) + average (B1,L1) + average(C1,M1) + ...

Naturally, this can get tedious.

Now, a simple solution would be to put the data into two separate rows, but let's just say that I can't do that -- the data in the spreadsheet is going to be imported into another application, and these data MUST be in the same row. I feel like there's a better way.

sesquiup

**starguy** · 10-23-2007, 03:14 AM

Here is a single cell formula

=SUM(A1:J1,K1:T1)/2

**lecxe** · 10-23-2007, 05:14 AM

Hi

I agree with the formula but I don't think it's an array formula, SUM() accepts ranges. I think you just have to confirm with Enter.

Kind regards
lecxe

**starguy** · 10-23-2007, 06:13 AM

Originally Posted by lecxe

Hi

I agree with the formula but I don't think it's an array formula, SUM() accepts ranges. I think you just have to confirm with Enter.

Kind regards
lecxe

Thank you for pointing out.

**sesquiup** · 10-23-2007, 12:57 PM

Oops, I goofed... it's not this simple. The second set of data sometimes has blanks. So, if the first set of data is

10 10 2 8

and the second set of data is

blank 4 6 blank

Then the averages I want are

10 7 4 8

Just summing everything and dividing by 2 won't work.

sesquiup

**starguy** · 10-24-2007, 02:12 AM

Originally Posted by sesquiup

Oops, I goofed... it's not this simple. The second set of data sometimes has blanks. So, if the first set of data is

10 10 2 8

and the second set of data is

blank 4 6 blank

Then the averages I want are

10 7 4 8

Just summing everything and dividing by 2 won't work.

sesquiup

If you want the averages in seperate cells as you mentioned (i-e 10 7 4 8) then simple AVERAGE function will work.

=AVERAGE(A1,K1) and copy it to right side. Then you can SUM all averages.