macros for calculating data after inserting row

[debbz82]

hi~~
I am working on a spreadsheet where I search through row by row, and
if the data in the Column B of that particular row is equivalent to the
data in the Column B of another row is the same, I add the data in the
Column W of the rows together. Then, I divide the number by the count
of how many rows are the same. I found a way to do something similar
to this by using =SUM(w3:w9)/rows(w3:w9), but that only works when I
insert rows within the row ranges, assuming that the data in Column B
of rows 3 through 9 are the same. If I insert a row after row 9 that
has the same value as the others(column B), the value in Column W of
row 10 does not add to the other numbers.

For example: (original spreadsheet)

Column B Column W
101 3
101 3
101 4
103 2

Since the first three rows have the same value in column B, I need to
add the Column W values of the first three rows, then divide the sum by
3 (which is the count of how many rows have the same Column B value).
Here's my problem:

say i inserted a row after the third row

column B column W
101 3
101 3
101 4
101 2
103 2

the formula that I found no longer works because the fourth row's
column W does not get added..

Any help would be really appreciated thank you so much!!

Debbie

 

[Earl Kiosterud]

Debb,

Make a pivot table. This will give you an average of column W values for
every unique value in column B (all the 101's, all the 102's, etc). Do the
grouping on column B (drag its header into either row or column of the pivot
table). They don't have to be together (your table doesn't have to be
sorted on column B). Do the data calc on column W (drag its header into the
data area, then double-click it, and change it's calculation function to
AVERAGE.

Or use Data, Subtotal, specifying average for the calculation.