Excel Help: Average In Range

[bqmassey]

I have Excel data that looks something like the block below. I want to create a table that looks something like the block below.

I want to create a table that averages the B values for ranges in the A column.

A      B
7    0.82
0    0.41
2    0.93
7    0.24
4    0.7
1    0.18
9    0.99
10   0.65
1    0.63
7    0.36
1    0.21
8    1
9    0.27
6    0.75
6    0.41
9    0.76
7    0.6
9    0.49
4    0.49
0    0.82

I want to end up with this:

0-1   .500
2-3   .500
4-5   .500
6-7   .500
8-9   .500
9-10  .500

Thoughts?

 

[Cap'n Jack]

Without writing a macro, consider the following:

1. sort the two columns based on column A
2. use the AVERAGE() command over each desired range in column A.

Sorting them first puts them in order so you can average() adjacent rows in a column.

I don't see any other way of doing it outside of a macro program- not too difficult to do, but more than I feel like doing at the moment...

 

[bqmassey]

Without writing a macro, consider the following:

1. sort the two columns based on column A
2. use the AVERAGE() command over each desired range in column A.

Sorting them first puts them in order so you can average() adjacent rows in a column.

I don't see any other way of doing it outside of a macro program- not too difficult to do, but more than I feel like doing at the moment...

Thanks for the reply.

I'd do what you're suggesting, but the real data is a few hundred lines deep and there are 80+ ranges

I've seen it done before, without a macro, but I can't recall how.

 

[Cap'n Jack]

Thanks for the reply.

I'd do what you're suggesting, but the real data is a few hundred lines deep and there are 80+ ranges

I've seen it done before, without a macro, but I can't recall how.

Did you install the Excel analysis toolpak? Look up the histogram functions and see if they do what you need...

 

[zalo9]

Here's a solution

 

[nyoung]

There's gotta be an easier way (perhaps using the histogram functions), but for sure, you can use the SUMIF function to create your own summations.

As a backdrop, let's say you have 400 entries of data arranged in two columns.

#1. Insert a new column A that has the numbers 1 to 400 in the rows 1:400.

#2. Insert a new column B. Add this function in row B1...
=SUMIF($C$1:$C$400,A1,$D$1:$D$400). Then copy B1 to all other rows in B. The entries in this row will be the sum of data matching the entries in column A.

#3. If you need averages, you can add two new columns (E and F). Put entries of "1" into the E column. Then use the SUMIF function in the F column. SUMIF($C$1:$C$400,A1,$E$1:$E$400).

#4. To get the averages, divide column B by column F.

I have Excel data that looks something like the block below. I want to create a table that looks something like the block below.

I want to create a table that averages the B values for ranges in the A column.

A      B
7    0.82
0    0.41
2    0.93
7    0.24
4    0.7
1    0.18
9    0.99
10   0.65
1    0.63
7    0.36
1    0.21
8    1
9    0.27
6    0.75
6    0.41
9    0.76
7    0.6
9    0.49
4    0.49
0    0.82

I want to end up with this:

0-1   .500
2-3   .500
4-5   .500
6-7   .500
8-9   .500
9-10  .500

Thoughts?

 

[roadwarrior]

Just use a pivot table.

(get rid of the ".txt" on the attached file to open as an excel workbook.)